Migrate On-Premises MySQL Data to Amazon RDS (and back)

I love to demo Amazon RDS. It is really cool to be able to launch a relational database instance in minutes, and to show my audiences how it manages scaling, backups, restores, patches, and availability so that they can focus on their application.

After my demo, I invariably get questions about data migration. The audiences see the power and value of RDS and are interested in moving their existing data (and applications) to the cloud. However, because most databases are processing changes all the time, this is a non-trivial exercise.

Today we are introducing a pair of new features to simplify the process of migrating data to Amazon RDS running MySQL, and back out again. Both of these features rely on MySQL’s replication capabilities.

As you may know, replication works by copying the data modification operations (INSERT, DELETE, and so forth) performed on the master to the slave, and then running them on the slave. As long as the master and the slave start with identical copies of the replicated database tables, the two will stay in sync.

Migrating Data From an External MySQL Instance to RDS
In order to migrate data from an existing MySQL instance running on-premises or on an EC2 instance, you’ll need to configure it as a replication source. You’ll need to poke a hole in your corporate firewall if your database is on-premises, or add an entry to your EC2 instance’s security group if your database is already hosted on AWS.

Next, you will need to launch and prepare an Amazon RDS running MySQL. Then you will use mysqldump to dump the databases or database tables that you want to migrate, and take note of the current log file name. Next, you’ll use the database dump to initialize the RDS database instance.

Once the RDS instance is initialized, you will need to run the replication stored procedures that we supply as part of this release to configure the RDS instance as a read replica of the instance running on premises or on EC2, and start the replication process. Once your RDS instance catches up with any changes that have taken place on the master, you can instruct your application to use it in preference to the existing version.

You also have the option to use RDS Provisioned IOPS, and you can also set up RDS in Multi-AZ mode if you’d like. However, we recommend that you do not configure Multi-AZ until after the import has completed.

The full process is a bit more involved and we have plenty of documentation to help you along the way. This feature was designed to be used in conjunction with MySQL 5.5 (version 5.5.33 or later) and 5.6 (version 5.6.13 or later).

Migrating Data to an External MySQL Instance from RDS
You can also invert the above process in order to move data from an RDS database instance to a MySQL server that is running on-premises or on EC2. This process uses an RDS Read Replica to convince the master to hold on to the log files for longer than usual.

Again, we have plenty of documentation to help you navigate the process. This feature was designed to be used in conjunction with MySQL 5.6 (version 5.6.13 or later).

The Road to Replication
If you have read this far and you have some experience with MySQL replication, you may be thinking “Cool — I can set up replication from my existing database to the cloud, creating a hot spare for easy failover.”

It is best to think of replication as a component of a fail-to-cloud model, rather than as a complete solution in and of itself. Because there’s a network connection in between the master and the slave, you would need to monitor and maintains the connection, track replication delays, and so forth in order to create a robust solution.

Migrate That Data!
You can start migrating your data today. Here are the documents and stored procedures you’ll need to have in order to get started:

• Importing data from an external MySQL database instance to a RDS MySQL database instance.
• Exporting data from a RDS MySQL database instance to an external MySQL instance.

— Jeff;