Accelerating Amazon RDS for MySQL Read Replica Promotion

Overview

This article mainly describes how to speed up the upgrade from MySQL Read Replica as much as possible in a limited time in an RDS MySQL cluster.

This scheme is applicable to scenarios

• Large amount of database data and short downtime maintenance window
• Need to create Read Replica immediately after boost

Scheme description

Assume that the original database cluster is a master node and a read replica. We now want to split this RDS cluster into 2 separate clusters.

First, we need to create a read-only replica in the original cluster, and then read-only wait for the data synchronization to complete.

You can monitor replication lag in Amazon CloudWatch by viewing the Amazon RDS ReplicaLag metric. When the ReplicaLag indicator reaches 0, it means that the replica has caught up with the progress of the primary database instance. If ReplicaLag indicator returns -1 , replication is not currently activated. ReplicaLag = -1 is equivalent to Seconds_Behind_Master = NULL .

The act of creating a read replica restores a copy of the database from the latest snapshot of the current primary. Therefore, the time of the last snapshot will affect the time of data synchronization after the read replica is created.

It is recommended to check the time of the last snapshot before creating a read replica, if the snapshot time is longer than the current time. After creating a manual snapshot, you can reduce the time to create a Read Replica.

Create snapshot on read replica 'Read Replica B'.

Wait for the snapshot creation to complete, then "promote" the read replica "Read Replica B" and choose to enable automatic backups.

At this time, taking a snapshot on "Read Replica B" will be faster, because the system will automatically select the snapshot of the master node as an incremental snapshot instead of taking a snapshot of the full storage of the read-only node.

If you choose not to start automatic backup during promotion, the promoted instance cannot create a read-only node. After restarting automatic backup, you can create a read-only node. You only need to set the backup retention period to be greater than 0 days.

After the "promotion" is completed, "Read Replica B" will be detached from the cluster of "DB Cluster 1" and become a new independent database cluster "DB Cluster 2".

The act of "promoting" automatically triggers a snapshot of the promoted node. By default, a newly created read replica (Read Replica B) has no snapshots. Therefore, a snapshot will be taken from the full storage. If the data volume of this node is very large, this time will last for a long time. Snapshots are expected to complete in about 1 hour/TB.

During a snapshot, you cannot "cancel" the snapshot behavior, increasing unexpected window maintenance time.

If manual snapshots have been made according to the previous steps, the automatic snapshot time of this step will be greatly reduced.

Create a read replica "Read Reaplic B" on "DB Cluster 2" and wait for the data to complete synchronization.

If the automatic backup is not turned on during the promotion, the menu item "Create Read-Only" in the console is grayed out and disabled.

At this point, the upgrade of RDS MySQL and the addition of read replicas are completed.

References

1. Best Practices for Promoting MySQL Read Replicas on Amazon RDS for MySQL Databases Amazon Cloud Tech Blog
2. Using Read Replicas – Amazon Relational Database Service
3. Click to get started and try Amazon cloud technology database products now

Author of this article

forestry

Amazon cloud technology solution architect, responsible for the consulting and architecture design of cloud computing solutions based on Amazon cloud technology. With more than 14 years of research and development experience, he has built tens of millions of user APPs, and contributed to many Github open source projects. He has rich practical experience in many fields such as games, IOT, smart cities, automobiles, and e-commerce.

Mao Jinhui

Senior Technical Account Manager, Amazon Cloud Technology

Yu Bing

Amazon cloud technology technical account manager, responsible for enterprise-level customer structure and cost optimization, technical support, and assisting customers to implement Amazon cloud technology best practices. He has worked in technology companies such as Cisco and Microsoft, and has long been engaged in security, network, public cloud and other technical fields.