Replicating Local MySQL database to AWS RDS Slave

Replication is often the easiest way to migrate an on-premise production database to Amazon RDS. We recently had to do one such migration for a startup for an exciting Indian startup. Even though this is pretty standard task I thought posting the detailed steps here would help some of you. So here we go:

Prepare the local server

  • Open /etc/my.cnf file and update mysql configuration with below replication options. You might want to Ignore mysql system database.
[mysqld]
bind-address = 0.0.0.0  
server-id = 1  
log-bin = mysql-bin  
binlog-ignore-db = “mysql”  
  • After making changes to the config files, you need to restart services - service mysqld start

Backup the local database

  • Take a full backup of mysql database using below command. We are using mysqldump. If you have a huge database and are using a tool like mysqldumper your command may vary.
nohup mysqldump -S ~/webapps/mysql/var/mysql.sock -u root -p'xxxxxxx' --databases mydb01 mydb02 mydb03  --master-data=2  --single-transaction --order-by-primary -r backup_01032016.sql &  
  • We will use an EC2 instance as an intermediary before we restore this backup file. Move the above backup file to an EC2 instance which has mysql client tools installed.

Launch RDS and restore backup

  • Logon to AWS console and launch a MySQL RDS instance of relevant version.
  • Logon to the EC2 instance and start restoring the backup you uploaded earlier to RDS instance
mysql -h thenakedsun.crc9taun2mmr.ap-southeast-1.rds.amazonaws.com -u stageuser -pxxxxxxxx  < backup_01032016.sql  
  • Wait for the restore to complete and validate that the import succeeded.

Configure Replication

  • Get the RDS IP by executing the below command - host thenakedsun.crc9taun2mmr.ap-southeast-1.rds.amazonaws.com
  • Login to the Local DB server and create the replication user. Grant the required privilages. Replica with the actual IP address you got from the step above.
CREATE USER 'repl_user'@'<IP Address>' IDENTIFIED BY 'XXXXXXX';  
GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'repl_user'@'<IP Address>' IDENTIFIED BY 'XXXXXXX';  
  • From the backup dump, get the MASTER_LOG_FILE and MASTER_LOG_POS
SMS=/tmp/show_master_status.txt  
mysql -ANe "SHOW MASTER STATUS" > ${SMS}  
CURRENT_LOG=`cat ${SMS} | awk '{print $1}'`  
CURRENT_POS=`cat ${SMS} | awk '{print $2}'`  
echo ${CURRENT_LOG} ${CURRENT_POS}  
  • Connect to RDS instance and invoke a procedure, mysql.rds_set_external_master. It allows configuring the RDS instance as a slave to an external, non-AWS master. Execute the below command to set the external master for the RDS with proper parameters.
CALL mysql.rds_set_external_master ('master-ip', hostport,  
    'repl_user', 'xxxxxxxxx', ${CURRENT_LOG}, ${CURRENT_POS}, 0);
  • Start the replication CALL mysql.rds_start_replication;
  • Check the slave status SHOW SLAVE STATUS \G

Hope this was helpful. Happy replicating!

Manoj Kumar

Manoj is our Principal Cloud Architect. An AWS and Linux geek who takes extreme pleasure in lording servers, tightening security screws and learning new things.

comments powered by Disqus