Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
MySQL DB Migration from localhost to AWS RDS
http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/MySQL.Procedural.Importing.External.Repl.html
http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/mysql_rds_stop_replication.html
http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/mysql_rds_reset_external_master.html
http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_BestPractices.html
https://www.elitmus.com/blog/technology/setting-up-amazon-rds-as-a-slave-to-a-self-managed-mysql-server/
https://plusbryan.com/mysql-replication-without-downtime
https://www.borfast.com/blog/2014/02/15/how-to-add-a-slave-to-a-mysql-replication-setup-with-no-downtime/
http://stackoverflow.com/a/32716003
https://www.bonusbits.com/wiki/HowTo:Configure_iptables_to_Allow_Access_to_Common_Services_on_Linux
# Minimize DB Read-Only period, but involves MySQL Replication Setup
# PREPARATION:
# On the master, create user for replication:
CREATE USER 'repl_user'@'%' IDENTIFIED BY 'mypassword';
GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'repl_user'@'%' IDENTIFIED BY 'mypassword';
# On the master, expose the TCP connection (TCP port 3306)
iptables -L
iptables -A INPUT -p tcp -m tcp --dport 3306 -m state --state NEW,ESTABLISHED -j ACCEPT
# On the master, you must enable binary logging and configure a unique server ID
# configure /etc/mysql/my.cnf by adding these lines in the [mysqld] section:
server-id=1
binlog-format=mixed
log-bin=mysql-bin
datadir=/var/lib/mysql
innodb_flush_log_at_trx_commit=1
sync_binlog=1
# remove the bound IP by commenting out line:
# bind-address = 127.0.0.1
# after editing /etc/mysql/my.cnf restart the master mysql server
service mysql stop; service mysql start
# MAINTENANCE MODE (master is read-only)
=======================================================================
mysql> FLUSH TABLES WITH READ LOCK; SET GLOBAL read_only = ON;
mysqldump --databases production_database --skip-lock-tables --single-transaction --order-by-primary --flush-logs --hex-blob --master-data=2 > ~/dump.sql
# make the source instance writeable again:
mysql> SET GLOBAL read_only = OFF; UNLOCK TABLES;
=======================================================================
# head dump.sql -n80 | grep "MASTER_LOG_POS"
# File Position
# ------------------------------------
# mysql-bin.000001 107
# ------------------------------------
# mysql --host=db.us-east-1.rds.amazonaws.com --port=3306 -u master -pass < ~/dump.sql
# On each slave that you want to connect to the master, you must configure a unique server ID (amazon done that for us?)
# >>>>> on Amazon RDS, provide the master log file name and master log position:
CALL mysql.rds_set_external_master ('184.173.147.99', 3306, 'repl_user', 'mypassword', 'mysql-bin.000001', 107, 0);
CALL mysql.rds_start_replication;
SHOW SLAVE STATUS;
# make sure that the binlog for the master is actually the correct one, and then
# FINAL step: promote a slave to a master
# on Master:
FLUSH LOGS;
# on Rails App, switch ENV variables for config/database.yml
# and restart Puma
# on Slave:
CALL mysql.rds_stop_replication;
CALL mysql.rds_reset_external_master;
SHOW SLAVE STATUS;
# dump and upload in a single step
# MAINTENANCE MODE (DB is read-only)
=======================================================================
mysql> FLUSH TABLES WITH READ LOCK; SET GLOBAL read_only = ON;
mysqldump --databases mydatabase --single-transaction --compress --order-by-primary -u root -passwd |
mysql --host=db.us-east-1.rds.amazonaws.com --port=3306 -u master -pass
# on Rails App, switch ENV variables for config/database.yml
# and restart Puma
=======================================================================
# DB is writeable again
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.