MySQL DB Migration from localhost to AWS RDS

migrating_mysql_to_rds.txt

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

min_read_only_mode.txt

# 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;

min_setup.txt

# 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