What may become a place for random musings about law as well as code by @DominikTo.

AWS RDS MySQL migration with replication

18 Jun 2015

Amazon Web Services makes it quite easy to replicate MySQL instances running on their RDS service - but sometimes, you’ll still have to do things manually.

Migrating an unencrypted RDS instance into one that uses encryption (or vice versa) is such a case, because you (currently) cannot use an unencrypted snapshot to create a new encrypted instance and you can’t create an encrypted read replica of an unencrypted database.

Migrating an instance into the Frankfurt (eu-central-1) region is another example, where RDS currently won’t help you as you (currently) can neither copy snapshots into the Frankfurt region, nor create a cross-region read replica there (As of 2015-09-21 cross region read-replicas are supported in eu-central-1).

This post explains how to migrate using MySQL replication.

Disclaimer: This post is still a work in progress, I published it regardless to force myself to actually finish and polish it. So until you see this warning here, you should take everything with a grain of salt… well, you should do that anyway with everything you hear or read. ;-)

Overview

We want to migrate from a source RDS instance to our target instance. You should definitely practice this process if you are trying to migrate a production database without downtime.

To do this, we’ll have to go through the following steps:

  1. Create a read replica of the source instance (in the same region as the source instance).
  2. Use the read replica to extract a full dump of the source database (without putting heavy load on the source instance).
  3. Spin up a new RDS instance as the target instance.
  4. Import the dump from the source database.
  5. Set up manual replication between the new target instance and the source instance.
  6. Wait for replication to catch up.

Once replication finally catched up (and you’ve reconfigured your application to use the new instance), we’ll do some cleanup and get rid of the read replica we’ve created in step 1. as well as the source instance.

1. Create a read replica of your source database

You can do that via the aws cli or the web console.

2. Extract a database dump from the read replica

First, you’ll have to stop replication on the read replica by running the following SQL statement:

CALL mysql.rds_stop_replication;

Now we’ll have to figure out at which position we’ll later have to continue replication, once we’ve imported the dump again with the following SQL statement:

SHOW SLAVE STATUS;

Write the following values down, you will need them later:

Next, prepare the database dump:

echo "SET autocommit=0;
SET unique_checks=0;
SET foreign_key_checks=0;" > source_dump.sql

Then create the actual database dump with mysqldump (it’s highly recommended to run your dump in a screen session; depending on your data, you may have to set --max_allowed_packet accordingly):

mysqldump -h read-replica-of-source-db.region.rds.amazonaws.com -u username -p password -P 3306 --quick --databases db1 db2 db3 --routines --triggers >> source_dump.sql; echo "COMMIT;" >> source_dump.sql

Creating the dump will take quite a while. Good night and see you tomorrow. ;-)

3. Start a new RDS instance as your target instance

You can do that via the aws cli or the web console.

4. Import your dump into the new target instance

To speed up the import a bit, you should set innodb_flush_log_at_trx_commit = 2 for the target instance.

Once that is done, it is time for importing the dump into the new instance (it’s highly recommended to run your import in a screen session; depending on your data, you may have to set --max_allowed_packet accordingly):

mysql -h target-db.region.rds.amazonaws.com -u username -p password < source_dump.sql

This will again take quite a while.

5. Set up manual replication between the new target instance and the source instance.

To set up replication, you’ll first have to create a replication user on the source instance:

GRANT REPLICATION SLAVE on *.* to 'username'@'%' identified by 'password';

Then head over to the target instance and configure replication (you’ll now need the values for Master_Log_File and Read_Master_Log_Pos that you wrote down earlier):

CALL mysql.rds_set_external_master (
    'source-db.region.rds.amazonaws.com',
    3306,
    'username',
    'password',
    'Master_Log_File',
    Read_Master_Log_Pos,
    1
);

Now, we’ll start replication by running the following SQL statement on the target instance:

CALL mysql.rds_start_replication;

You can check if replication is actually running with SHOW SLAVE STATUS; on the target instance (slave_io_running and slave_sql_running should both be YES).

6. Wait for replication to catch up.

Once replication catched up, you should set innodb_flush_log_at_trx_commit back to 1 again.

You’ll probably also want to migrate your db users from the source instance. You can get a listing of them on the source instance with:

SELECT user, host FROM user;

Next run the following statement for every user you want to migrate on the source instance:

SHOW GRANTS FOR 'username'@'%';

Then run the output of those queries on the target instance to create the users there and run FLUSH PRIVILEGES; after that.

Final steps and clean up

After you’ve verified that everything is as it should be:

Disclaimer: This post is still a work in progress, I published it regardless to force myself to actually finish and polish it. So until you see this warning here, you should take everything with a grain of salt… well, you should do that anyway with everything you hear or read. ;-)

Any comments? Ping me on Twitter. 👉🏻 Get my newsletter for occasional updates. ✌🏻

Related Posts