Comprehensive Guide to Migrating AWS RDS MySQL Community 5.x to AWS RDS Aurora MySQL 8.x Serverless V2

This blog post aims to offer a comprehensive guide for migrating your RDS MySQL Community 5.x databases to the more scalable and efficient Aurora MySQL 8.x Serverless V2. Transitioning from MySQL Community to Aurora MySQL 8.x Serverless V2 provides several benefits, including improved performance, automatic scaling, and seamless integration with AWS services. Within this guide, you'll discover step-by-step instructions, best practices, and insights designed to facilitate a smooth and successful migration process.

Prerequisites

Make sure you have binlogs is enabled on your source RDS cluster and binlog_format is set to ROW.

# Check whether binlog is enabled
SHOW VARIABLES LIKE "log_bin";

# Check whether binlog_format is ROW
SHOW VARIABLES LIKE "binlog_format";

Steps

In this example, please be aware that the source RDS cluster is named pf-portal-staging.

Creating a blue/green deployment (The green deployment is named pf-portal-staging-green)

To set up a green environment for RDS MySQL using RDS Blue/Green deployment on the latest Aurora-compatible version 8.0.34, ensure that your source database does not use custom option groups, as this will cause an error. If your database uses a custom option group, switch it to the engine’s default option group by navigating to Database Options, selecting the default option group default:mysql-5-7, and choosing Apply Immediately followed by Continue. Review the details and select Modify DB Instance. This change does not cause any downtime as it does not require an instance reboot.

When there are multiple read replicas in the source database, the green deployment will also create the same number of replicas.

Create Aurora read replica from RDS MySQL in green deployment. This Aurora cluster will be created with provisioned capacity writer. Name should be something like pf-portal-staging. Then it will automatically create the cluster with the name pf-portal-staging-cluster.

Create one more reader in same Aurora cluster in serverless mode. This will become the writer instance. The replica name must be pf-portal-staging-cluster-0.

Create one more reader in same Aurora cluster in serverless mode. This will become the reader instance. The replica name must be pf-portal-staging-cluster-1.

Set priority tier of all other readers and writers in aurora cluster to tier-15 (except serverless writer instance which should be on tier-1)

Failover your aurora cluster (based on the priority set, it will failover to serverless and make it new writer)

After testing, point your applications to serverless (Change the Route53 DNS records to the new Aurora Replica Cluster read/write endpoint). This time your writes will be failed as serverless is still a reader. You will be only be able to test read queries. So, once the promotion step is done in the staging cluster, make sure to thoroughly test the application.

Promote Aurora Replica Cluster as a standalone instance. (You cannot switchover directly your green deployment as the primary instance of green is RDS MySQL)

Before promoting the cluster, kindly verify the replica lag and the values for seconds_behind_master.

The replica lag values must be in milliseconds.
The seconds_behind_master value should be 0.

# Find replica lag
SELECT Replica_lag_in_msec FROM mysql.ro_replica_status WHERE Session_id != "MASTER_SESSION_ID";
SELECT REPLICA_LAG_IN_MILLISECONDS FROM information_schema.REPLICA_HOST_STATUS WHERE SESSION_ID != "MASTER_SESSION_ID";

# Find seconds_behind_master
SHOW SLAVE STATUS;