Overview

Amazon Aurora offers several significant advantages over RDS MySQL, including superior performance, stronger I/O capabilities, and automatic disk capacity expansion without manual management. Since Aurora is almost fully compatible with MySQL, replacing MySQL with Aurora can bring numerous benefits.

Due to historical reasons, we operate two RDS MySQL instances, one of which is in the AWS Classic environment. We aim to migrate these database instances to Aurora for better performance, but determining the best migration method was a challenge.

Optional Migration Schemes

We have explored various migration approaches. The simplest method involves exporting a database backup and creating a new Aurora instance based on that backup. Specifically, there are two ways to do this:

1. Take the application offline, stop MySQL, perform a full backup using mysqldump, create an Aurora instance, and import the backup into Aurora. Once complete, reconnect the application to Aurora and bring it back online.
2. Take the application offline, stop MySQL, take a manual snapshot of the MySQL RDS instance, and then migrate that snapshot to Aurora after completion. Once done, reconnect the application to Aurora and bring it back online.

Both methods allow migrating MySQL to Aurora, but both require downtime maintenance, potentially taking several hours or even days. This is unacceptable for our operations.

The migration solution we need must minimize impact on online services and ensure a smooth transition to Aurora.

AWS provides the Database Migration Service (DMS). However, after testing, DMS frequently encountered SQL errors after starting data migration, and debugging/troubleshooting was very cumbersome, forcing us to abandon it.

Until we discovered in the documentation that AWS provides a migration scheme based on MySQL BinLog. This scheme only causes a short-term impact on online services when switching the database connection configuration in the final stage. This finally made our database migration feasible.

Migration Process 1

First, we needed to migrate the MySQL RDS instance within the VPC. The documentation outlines certain requirements for the migration process, such as recommending MySQL version 5.5 or higher and requiring all tables to be converted to InnoDB.

The specific migration steps are as follows:

1. Create a read replica on the RDS MySQL instance. If no read replica exists previously, this operation will enable MySQL binlog. Additionally, if the database is large, it is recommended to adjust the binlog retention time. The following example sets the retention period for binary log files to 6 days:
   CALL mysql.rds_set_configuration('binlog retention hours', 144);
2. After the read replica is created, wait for it to catch up with the primary instance (i.e., when Replica lag is nearly 0), then stop replication on the read replica:
   CALL mysql.rds_stop_replication;
3. After stopping replication on the read replica, it is advisable to wait for a period, such as 10 minutes (a lesson learned from retrying 10 times).
   Execute in the read replica:
   SHOW SLAVE STATUS
   Record the values of Master_Log_File and Read_Master_Log_Pos from the results.
   Take a manual snapshot of this read replica.
4. For the read replica’s snapshot, execute Migrate Snapshot to convert the snapshot into an Aurora instance. Note: If the creation process hangs without response for a long time (over 3 hours), contact AWS immediately to confirm if there are any anomalies. I encountered this multiple times, getting stuck at the “Preparing Data” stage.
5. After the Aurora instance is created, check the security group settings of the cluster to ensure internet connectivity. Execute:
   CALL mysql.rds_set_external_master ('mysql master server host', 3306, 'repl_user', '<password>', 'mysql-bin-changelog.000031', 107, 0);
CALL mysql.rds_start_replication;
   Where mysql-bin-changelog.000031 and 107 come from the Master_Log_File and Read_Master_Log_Pos outputs of the aforementioned SHOW SLAVE STATUS.
6. Execute SHOW SLAVE STATUS on Aurora to confirm that replication is executing normally. If you encounter errors similar to the following:
   Error 'Table 'mysql.rds_monitor' doesn't exist' on query. Default database: 'mysql'. Query: 'DELETE from mysql.rds_monitor'
Error 'Table 'mysql.rds_monitor' doesn't exist' on query. Default database: 'mysql'. Query: 'INSERT INTO mysql.rds_monitor(pid, table) values (5455,'abc/#sql-ib345-3892659999')'
   Contact AWS, or directly execute CALL mysql.rds_skip_repl_error to skip the replication error. However, once this issue arises, it may reappear after a period even if skipped.
7. The Seconds_Behind_Master value in the output of SHOW SLAVE STATUS on Aurora indicates how much time the replication is behind the primary instance. Once replication catches up, use CALL mysql.rds_stop_replication; to stop replication, and then switch the write node in the application.

Migration Process 2

Through the above Migration Process 1, we successfully completed the migration of the MySQL RDS instance within the VPC to Aurora. Our other RDS instance was in the EC2 Classic environment. AWS also has a document describing how to perform database migration in this scenario.

The specific migration steps are identical to the previous process, with one additional step: create a new EC2 instance within the VPC, enable Classic Link, and configure it to access both the newly created Aurora in the VPC and the RDS MySQL instance in the external Classic environment. Refer to the last part of the document to configure an nginx proxy server, and then fill in the IP address of that EC2 instance as the primary database in mysql.rds_set_external_master. All other steps remain exactly the same.

Lessons Learned

The following points should be noted after migrating to Aurora:

1. Aurora’s Multi-AZ is completely different from RDS MySQL’s Multi-AZ. Under MySQL, Multi-AZ allows only one standby, which remains idle. Under Aurora, you can create up to 15 replicas, which can perform Failover sequentially according to their configured priorities.
2. Read replicas under MySQL RDS can be promoted to writable. However, read replicas under Aurora cannot be set to writable.
3. Permissions under Aurora have changed slightly, causing oak-online-alter-table to become unusable. However, Aurora has stronger performance, and after upgrading the instance type, table structures can often be modified directly without impacting online services.