At GoSquared, we recently migrated our MySQL databases over to Amazon RDS, here’s how and why we’ve done it.
Why migrate to RDS?
They’re easy to maintain and scale whilst being pretty good value. The Multi-AZ deployment also means there should be minimal downtime should anything go wrong. We also didn’t like having to maintain multiple MySQL servers and handle replication between them. It was therefore a good time to change MySQL to a different server, and RDS is a great option.
Configuration and Booting
This is pretty easy, select whatever basic preferences you’d like, a DB Instance Identifier (mysql-1 is probably descriptive enough) and a master username and password (which annoyingly can only be a max of 16 chars).
I’d recommend enabling Multi-AZ Deployment to help with uptime and redundancy, but it does cost more so it’s all about your needs.
In management options, enable automatic backups (losing data if things go monumentally wrong is not cool) and keep them for as long as you’d like. Select a backup window and maintenance window where your servers are at lowest load as the backup can use a fair amount of CPU and the maintenance window can have a restart within it (another reason to use Multi-AZ Deployment – you shouldn’t have any interruption).
After you’ve booted your server it should show as a DB Instance. You’ll need to add whatever IP’s/Security Groups you want to be able to access the server to DB Security Groups, or you won’t be able to connect.
By clicking on the arrow for the instance and looking for the Endpoint, you’ll find what host you should be connecting to.
Read Replicas
If you want a server to handle only read queries, making it basically a slave, you can easily set one up by right clicking on the instance. Very useful if you need to deal with high read load on MySQL.
Migration Preparation
Now the important part, migrating your entire production database without going down for hours (hopefully less than a minute). You’ll need MySQL binlogging enabled.
First, take a full MySQL dump of your database…
mysqldump --single-transaction --master-data=2 -C -q dbname -u username -p > backup.sql
This will export your whole database into one huge sql file.
Next, import it to your RDS instance…
mysql -u username -p -h RDS_endpoint DB_name < backup.sql
This may take a while depending on your DB size. The –master-data=2 flag when you took the dump stores the start position and filename of the binlog, to view it run something like more backup.sql and find something that looks like CHANGE MASTER TO MASTER_LOG_FILE=’mysql-bin.000003′, MASTER_LOG_POS=350789121;. mysql-bin.x is the filename and the number is the log position. As there’s data always being modified/added in your production database, you’ll need to keep the new RDS instance up to date without spending the time importing a new dump, which would be too much downtime for many companies.
mysqlbinlog /var/log/mysql/mysql-bin.000003 --start-position=350789121 --base64-output=NEVER > output.sql
This command will convert the binlog to valid SQL which can be run on the RDS to update it to the latest data.
To update it on the server, run:
cat output.sql | mysql -h RDS_endpoint -u username -p DB_name
See Errors if you have any issues.
To now find what line of the binlog the RDS instance is up to date to, tail output.sql and find end_log_pos. The longer you leave between updating the database with the binlogs, the longer it’ll take to update, so keep running it so when you come to the actual migration it won’t take long.
Migration
When you’re ready to actually migrate, prepare by getting the latest log position ready in the mysqlbinlog command.
Then, on your current production database, run FLUSH TABLES WITH READ LOCK; on MySQL to stop all writes from going through.
After this is done, run the prepared mysqlbinlog command. Then, update RDS to the very latest database.
As soon as that has run, deploy all of your applications with the new MySQL configs. Migration complete.
Errors
While updating the RDS servers with the latest binlog, you may experience an error or two, we had this and spent ages debugging it. Here are a couple of common errors and fixes.
You do not have the SUPER privilege and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable)
With RDS, you can’t have the SUPER privilege, so go into your DB Security Groups, create a new group and change log_bin_trust_function_creaters to 1.
ERROR 1227 (42000) at line N: Access denied; you need (at least one of) the SUPER privilege(s) for this operation
We need to remove all lines with SET @@session.pseudo_thread_id commands, a simple grep can do this and then we can update our RDS instance straight away…
grep -v "SET @@session.pseudo_thread_id" output.sql | mysql -h RDS_endpoint -u username -p DB_name
