Amazon RDS for MySQL – Promote Read Replica

As I described in my original blog post, you can easily create and manage read replicas using Amazon RDS for MySQL. From the AWS Management Console, you simply right-click on the database instance of interest and choose the Create Read Replica option:

Adding one or more Read Replicas gives you the ability to handle an increasing amount of read-heavy traffic to your database.

Today we are enhancing the Read Replica function with support for promotion. You can now convert a MySQL Read Replica into a “standalone” RDS database instance using the Promote Read Replica function. Two things happen when you do this:

1. Replication from the former master ceases.
2. The Read Replica becomes a “standalone” database instance.

Promotion in Motion
There are a number of different uses for this new feature. Here are some suggestions to get you started:

Perform DDL Operations – Table-level DDL operations such as adding columns or indices can take a long time and can impose a performance penalty on your master database instance. Here’s another way to do it:

1. Execute the operations on a designated Read Replica and wait for them to complete.
2. Wait for the Read Replica to catch up with the master database instance.
3. Promote the Read Replica to a master.
4. Direct all database traffic to the newly promoted master.
5. Create additional Read Replicas for performance purposes as needed.
6. Terminate the original master and any remaining Read Replicas associated with it.

Shard a Table – Sharding involves splitting a table into smaller tables, often using a hashing algorithm on the table’s primary key to partition the key space across tables. You can move from a single table model to a sharded model using Read Replicas and Promotion as follows:

1. Create a Read Replica for each shard.
2. Wait for each of the new Read Replicas to become available.
3. Promote the Read Replicas to masters.
4. Direct database traffic to the new sharded masters.
5. On each shard, delete the rows that belong to the other shards.
6. Terminate the original master.

Implement Failure Recovery – Amazon RDS provides multiple options for data recovery during failures including Multi-AZ deployments and Point in Time Recovery. With the ability to promote, Read Replica can be considered as an additional data recovery scheme against failures. However, you will want to make sure that you understand the ramifications of the asynchronous replication model and its limitations before electing to use this option as a recovery mechanism. If your use case requires synchronous replication, automatic failure detection and failover, we recommend you run your DB Instance as a Multi-AZ deployment.  If you do want to use Read Replica as a data recovery mechanism, you would start by creating a Read Replica, and then monitoring the master for failures. In the event of a failure you would proceed as follows:

1. Promote the Read Replica.
2. Direct database traffic to the new master.
3. Create a replacement Read Replica.

Because you can perform all of these operations from code using the Amazon RDS APIs, consider automating the procedures above using the Amazon Simple Workflow Service. Let me know what you come up with!

— Jeff;