Migration using Aurora Read Replica

Aurora uses MySQL DB engines binary log replication functionality to create a special type of DB cluster called an Aurora read replica for a source MySQL DB instance. Updates made to the source instance are asynchronously replicated to Aurora Read Replica.

To use Aurora Read Replica to migrate from RDS MySQL, your MySQL database must be running on Amazon RDS MySQL 5.6 or 5.7. This migration method does not work with on-premises databases or databases running on Amazon Elastic Compute Cloud (Amazon EC2). Also, if you are running your Amazon RDS MySQL database on a version earlier than 5.6, you would need to upgrade it to 5.6 as a prerequisite.

You can migrate your existing Amazon RDS MySQL databases to Amazon Aurora using Aurora Read Replica. This solution is beneficial since it's completely managed and does not involve manually configuring replication functionality to reduce downtime during migration.

These are the high-level steps to be performed:

1. Begin writes on the source Amazon RDS database (to simulate traffic in the real world).

2. Create an Amazon Aurora read replica from the existing Amazon RDS MySQL instance.

3. Stop writes to the Amazon RDS MySQL instance.

4. Wait for the replication lag between Amazon RDS MySQL and Amazon Aurora read replica to be zero.

5. Promote Amazon Aurora read replica to be a standalone database cluster.

6. Begin writes on Amazon Aurora read replica, immediately after starting promotion.

Although the promotion process is fairly quick, it can still add valuable seconds in the downtime window of your application, especially if the recovery time objective (RTO) for cutover is only for a few seconds instead of approximately 30 seconds. The key point in the above process which reduces the downtime window is the ability for the applications to read and write to the Amazon Aurora read replica immediately after the promotion process is started instead of waiting for it to complete.

The only time writes on the Aurora read replica should be on hold is when writing has stopped on source Amazon RDS and the replica lag reaches zero.

To get started, you must have an existing Amazon RDS MySQL instance.

Create Amazon Aurora read replica from an existing Amazon RDS instance

1. Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/.

2. In the navigation pane, choose Databases.

3. Choose the MySQL DB instance that you want to use as the source for your Aurora read replica.

4. For Actions, choose Create Aurora read replica.

   

5. Choose the DB cluster specifications as mentioned in Migrating data from a MySQL DB instance to an Amazon Aurora MySQL DB cluster by using an Aurora read replica and click Create read replica.

6. After Aurora replica is successfully completed, you should see a replica cluster along with RDS MySQL instance.

   

Stop writes to the Amazon RDS MySQL instance

Stop writing to the source RDS database and wait for the replica lag between Amazon RDS MySQL and Amazon Aurora read replica to come down to zero. You can check replica lag by running the SHOW SLAVE STATUS command on Aurora read replica and checking the Seconds behind Master value.

SHOW SLAVE STATUS \G 

Promote Amazon Aurora read replica to be a standalone database cluster

1. Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/.

2. In the navigation pane, choose Databases.

3. Choose the DB cluster for the Aurora read replica.

4. For Actions, choose Promote.

5. Choose Promote read replica.

   

As an alternative, you can issue the following AWS CLI command instead of using the AWS Management Console:

aws rds promote-read-replica-db-cluster \
    --db-cluster-identifier myreadreplicacluster

Begin writes on Amazon Aurora read replica immediately after starting promotion

Immediately after starting the promotion process, issue writes to the Amazon Aurora read replica. To validate if your writes are anyway affected during the promotion process, we will start inserting records into Aurora read replica as soon as we start the promotion.

After promotion is complete, you can confirm that the promotion has completed by using the following procedure.

1. Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/.

2. In the navigation pane, choose Events.

3. On the Events page, verify that there is a Promoted Read Replica cluster to a stand-alone database cluster event for the cluster that you promoted.

It took around 15 to 20 seconds for the promotion process (replica cluster to change status to regional cluster).

After promotion is complete, the primary MySQL DB instance and the Aurora read replica are unlinked, and you can safely delete the DB instance if you want.

Note

PostgreSQL follows the same process as the one described previously for MySQL for migration.

Important points to consider

• If your application uses a DNS solution like Amazon Route53, consider the DNS TTL (Time-To-Live) of approximately five seconds when switching the Amazon RDS endpoint with Amazon Aurora endpoint.

• Perform this operation during non-peak hours or at another time when writes to the primary DB cluster are minimal.

• You cannot delete the primary MySQL DB instance or unlink the DB Instance and the Aurora read replica during promotion time.

• If you write before replica lag reaches zero, then the replication is at the risk of breaking and you’ll have to delete and recreate the Aurora read replica.

• Be prepared for the Amazon Aurora Read Replica to catch up with Amazon RDS when you initially create it. It could take several hours per tebibyte (TiB) of data. The source RDS instance is available throughout the initial load.

• Version compatibility - The RDS for PostgreSQL version must be lower than or equal to a supported Aurora PostgreSQL version in the same major version. For example, you can replicate data between an RDS for PostgreSQL version 11.7 DB instance and an Aurora PostgreSQL version 11.7 or higher 11 version DB cluster, but not an Aurora PostgreSQL version 11.6 DB cluster. You cannot downgrade your version, so if you running on MySQL 8 in RDS, you can’t create an Aurora read replica with MySQL 5.7.

• Rollback option: If you wish to conduct sanity checks on the new primary on Amazon Aurora, you can let the Amazon RDS MySQL instance run after promotion of Amazon Aurora and rollback to Amazon RDS in case of any issues Identified by pointing the application back to the Amazon RDS endpoint. The time required for sanity checks will add to application downtime as once the switchover is done, Amazon Aurora does not replicate back to Amazon RDS in a managed fashion, although this can be done natively.

Aurora read replica vs other methods:

Using Amazon Aurora read replica is great when you are looking to make use of a managed solution while migrating from Amazon RDS (MySQL or PostgreSQL) to Amazon Aurora. This method also applies only in case of homogenous migration of MySQL and PostgreSQL engines. With this method, you can only move an Amazon RDS MySQL to an Amazon Aurora MySQL instance and an Amazon RDS PostgreSQL to an Amazon Aurora PostgreSQL instance, not MySQL or PostgreSQL instances running on EC2. Heterogenous migration across engines is not supported when using an Aurora read-replica method such as Amazon RDS Oracle, SQL Server, MariaDB.

If the above doesn’t fit your requirement, you could adopt other options for near-zero downtime migration from your source MySQL to Aurora MySQL platform:

1. Native tools + binlog replication: This method is a manual one and applies to homogenous migrations and can be very effective in that scenario. Using native tools such as mysqldump and binary logging for MySQL near-zero downtime migration can be achieved. This method can be used for small-scale migrations when the MySQL database is running on-premises or on MySQL EC2 prior to 5.6 or 5.7 versions.

2. RDS snapshot + binlog replication: Instead of using native tools, another method for homogenous migration is to use RDS snapshot of the RDS read replica and create an Aurora database cluster from it. Along with binary logging, this method will help is a near-zero downtime migration as well. This method however is only supported for RDS MySQL 5.6 or 5.7 and migrating only to the same version is supported, not 5.6 to 5.7. This method again requires some amount of manual work by configuring the binary logging between source and target.

3. Database Migration Service (DMS): You can use AWS Database Migration Service (AWS DMS) to migrate your data among homogeneous migrations such as MySQL to MySQL. DMS supports both heterogeneous migrations between different platform such as Oracle to MySQL and homogeneous migrations such as MySQL to MySQL. AWS DMS can do a one-time data migration, or it can do a continuous replication of the data for near-zero downtime migration using its Change Data Capture (CDC) feature Please refer to migrating from MySQL to Amazon Aurora. AWS DMS might also be advantageous if your migration project requires advanced data transformations such as remapping schema or table names, advanced data filtering, migrating and replicating multiple database servers into a single Aurora DB cluster.

Review the limitations for AWS DMS before using this method.

Note

Since AWS DMS CDC uses plain SQL statements from binlog to apply the changes in target database, it might be slower and more resource-intensive than native primary/replica binary log replication in MySQL. Hence, it is recommended to use the native self-managed or managed features for homogenous migrations and DMS for heterogenous migrations.