Best practices for migrating RDS for MySQL databases to Amazon Aurora

MySQL is the most popular open-source database in the world. However, many customers find that the undifferentiated heavy lifting of backups, high availability, and scaling of MySQL databases to be complex, time-consuming, or both.

This is one of the leading reasons why customers move their existing MySQL footprint to Amazon RDS for MySQL. Amazon RDS provides features like point-in-time recovery and high availability options right out of the box. RDS for MySQL even supports five read replicas for each source. With these, you can easily scale read workloads without having to manually configure and maintain binary log (binlog) replication.

If you want MySQL compatibility with the performance and availability of high-end commercial databases, you can migrate your MySQL databases to Amazon Aurora. With Amazon Aurora with MySQL compatibility, you can take advantage of features like fast database clones and autoscaling replicas. You also get native integration into other AWS services like AWS Lambda and Amazon CloudWatch Logs. In addition to those features, Amazon Aurora also offers enhanced durability by replicating data across three Availability Zones. It also can scale to 15 Aurora Replicas, all within latency often less than 20 milliseconds.

In this blog post, we will look at how to migrate from Amazon RDS for MySQL to Amazon Aurora MySQL with minimal downtime.

Migration considerations

As with any migration, you have a number of considerations to take into account. These include the source, the target, the client applications that depend on the migrating database, and business requirements concerning availability during migration. When migrating to the MySQL edition of Amazon Aurora, it is important to note that Aurora is wire-compatible with MySQL 5.6 and MySQL 5.7 (depending on your selection). What this means is that from an application perspective, there is no difference between MySQL 5.6 or 5.7 and Aurora 5.6 or 5.7. Thus, you shouldn’t need to make any changes to the existing application code during the migration process.

Even though the code in your applications doesn’t need to change, you need a way to point the applications to your new database. Of course, you can change all of the connection strings for all of the applications, but another common approach is to use DNS to do this for you. In this case, you don’t use the actual host name of your database instance in your connection string. Instead, consider creating a canonical name (CNAME) record that points to the host name of your database instance. Doing this allows you to change the endpoint to which your application points in a single location rather than tracking and modifying multiple connection string settings.

If you choose to use this pattern, be sure to pay close attention to the time to live (TTL) setting for your CNAME record. If this value is set too high, then the host name pointed to by this CNAME might be cached longer than desired. If this value is set too low, additional overhead might be placed on your client applications by having to resolve this CNAME repeatedly. Though use cases differ, a TTL of 5 seconds is usually a good place to start.

Migration steps

The first step in migrating your existing RDS for MySQL instance to an Amazon Aurora cluster is to select the instance in the Amazon RDS Management Console. Then for Instance actions, choose Create Aurora read replica.

When you have selected this option, you need to specify a number of parameters that define your Aurora cluster. The Amazon Aurora cluster has the same master user name and master password as the source instance.

When you have started the migration process, Amazon RDS creates a snapshot of your existing RDS for MySQL instance and restores the data from that snapshot to the newly created Amazon Aurora cluster. Depending on the size of the source database, this operation can take several hours.

When the Amazon Aurora cluster has been created and loaded with the initial set of data, Amazon RDS establishes binlog replication from the RDS for MySQL instance to the Amazon Aurora cluster.

When binlog replication has been established and replication begins, we recommend monitoring the CloudWatch metric Aurora Binlog Replica Lag on the Amazon Aurora cluster.

Although CloudWatch gives you a high-level view of the binlog replica lag, you can find a more precise measurement by logging in to the newly created Amazon Aurora cluster. To do so, use the MySQL client and run the command show slave status\G.  This command returns a lot of very useful information, but the specific metric that we want is Seconds_Behind_Master.  When this metric reaches 0, your newly created Amazon Aurora cluster is in sync with your original RDS for MySQL instance.

When your new Amazon Aurora cluster is in sync with the original RDS for MySQL instance, it is time to stop writes on your RDS for MySQL instance. You then begin writing to your new Amazon Aurora cluster. The way to do this using RDS for MySQL is to modify the parameter group assigned to your instance. The setting read_only defaults to a value of {TrueIfReplica} . In this case, we want to explicitly set this value to 1 , indicating that the instance is in a read-only mode. This parameter has a dynamic apply type, which means that its setting takes effect immediately and doesn’t require a reboot.

Now the source RDS for MySQL instance is in read-only mode. At this point, check the Seconds_Behind_Master metric one more time to ensure that all binlogs have been applied.

Then promote your Aurora read replica to a writer. To do this, simply select your target Aurora cluster and then choose Promote read replica for Instance actions. When it’s selected, it takes a few minutes for the process to complete. At this point, change the CNAME that your applications reference as discussed earlier in this post.

You know when the process is complete by checking Recent events for your new Amazon Aurora cluster as shown in the illustration following.

Summary

In this blog post, we discuss how to migrate a live RDS for MySQL instance to a new Amazon Aurora cluster with nearly zero downtime. In future posts, we plan to discuss how to move self-hosted MySQL databases either from Amazon EC2 or on-premises data centers to Amazon Aurora.

To get started with Amazon Aurora today, start work on the console.

For more detailed documentation on RDS for MySQL to Aurora migrations, see the migration documentation.

About the Author

Steve Abraham is a principal solutions architect for Amazon Web Services. He works with our customers to provide guidance and technical assistance on database projects, helping them improving the value of their solutions when using AWS.