Amazon mysql to rds migration

Simply dump the production database using mysqldump and the following options:

mysqldump --single-transaction --master-data=2 -C -q -u root -p > backup.sql

If you are using InnoDB tables, this will dump your database to a file without locking the tables (--single-transaction) AND save the bin log position in that file as a comment (--master-data=2). This is important because the dump itself takes a while to complete and loading it into RDS using the command below takes another 30 minutes to an hour in our case, and we didn't want the site down for that long as I mentioned before.

mysql -u root -p -h < backup.sql The cool thing is that I could now open up the backup.sql file to find the last bin log position in the comments at the top. With this knowledge, I could use the following command to have RDS catch up to the production database that was still running:

mysqlbinlog mysql-bin.002971 --start-position=73319797 | mysql -u root -p -h This example assumes we left off at bin log file 002971 and position 73319797 On top of that, I could keep doing this throughout the day or just right before our migration as long as I remembered what position I was at to make sure our new RDS instance was only a few moments behind the old production database right before we made the switch.

Then, when everything was ready.. all I had to do was put up our maintenance page, sever all connections to our production database, run mysqlbinlog one last time to make sure RDS is fully caught up (which only takes a second since we made sure it was very close already), switch the IPs, and re-deploy our app. After that, the maintenance page is removed and all traffic starts flowing to RDS. Thinking back, it was a pretty simple procedure, but it definitely took us a while to figure out what to do... hopefuilly this will be useful to anyone else thinking about moving to RDS.