Yao walks you through how to
migrate a MySQL database
to Amazon Aurora

migrate-mysql-aurora-yao

How do I migrate an existing RDS MySQL database to Amazon Aurora with minimal downtime?

Before you begin, note the following unsupported or partially supported database features that might cause issues with the Aurora migration process:

  • MyISAM
  • Temporal tables
  • Spatial indexes
  • Compressed tables
  • Encrypted tables

If your database uses any of these, consider changing or removing the features before beginning the import process. For additional considerations to take into account before starting the migration process, see Migrating Data to an Amazon Aurora DB Cluster and Migrating an RDS MySQL Snapshot to Aurora.

To migrate an existing MySQL RDS instance to Amazon Aurora with minimal downtime, perform the following steps:

1.    Make sure you have enabled backups for your RDS MySQL instance. In RDS, binary logging is controlled by the Backup Retention Period option of the DB instance; make sure to set Backup Retention Period to a non-zero number. It's a best practice to set the backup retention period to a long period of time (for example, 24 hours) as in the example at Accessing MySQL Binary Logs.

2.    After binary logging is enabled on your DB instance, create a replication user with REPLICATION CLIENT and REPLICATION SLAVE privileges on the primary DB instance, using commands similar to the following:

mysql> CREATE USER 'repl_user'@'%' IDENTIFIED BY '';
mysql> GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'repl_user'@'%'
IDENTIFIED BY '';
mysql> FLUSH PRIVILEGES;

3.    Extend the default binary log retention period on the RDS instance by using the mysql.rds_set_configuration command. By default, binary logs are rotated every few minutes; the following command sets the binary log retention period to 24 hours:

mysql> call mysql.rds_set_configuration('binlog retention hours', 24);

Note: Optionally, you can confirm that the binary log retention period has been updated by using the mysql.rds_show_configuration command.

4.    Create a Read Replica from your primary RDS instance. For best performance, use the same instance type as the master or higher.
Note: After the replica is created, it takes some time to “catch up” with the master. To check the status of this process, monitor the ReplicaLag CloudWatch metric. When the ReplicaLag metric is at or close to 0, the replica has “caught up” with the source DB instance.

5.    When the replica is caught up with your primary DB instance, stop the replication on the Read Replica and capture the binary log file and log position using commands similar to the following:

mysql> call mysql.rds_stop_replication;
mysql> SHOW SLAVE STATUS\G

Take note of the values of Relay_Master_Log_File and Exec_Master_Log_Pos from the output of the commands.

6.    Create a DB snapshot of your Read Replica.
Note: The DB snapshot must have been made from an unencrypted RDS DB instance running MySQL 5.6.

7.    When the snapshot is in the available state, migrate the snapshot and create an Aurora instance using the RDS console.

8.    After the Aurora instance is created, connect to the Aurora instance and verify that all your data is present using the following command:

mysql> mysql -h <aurora cluster end-point> -u <user-name> -p -P <port>

9.    Set up replication between your new Aurora cluster and your primary RDS MySQL instance by using the mysql.rds_set_external_master command. Make sure to use the replication user credentials created in step 2 and the values of Relay_Master_Log_File and Exec_Master_Log_Pos you noted previously:

CALL mysql.rds_set_external_master (
    'host_name_of_rds_end_point'
    , rds_master_host_port
    , 'replication_user_name'
    , 'replication_user_password'
    , 'Relay_Master_Log_File'
    , 'Exec_Master_Log_Pos'
    , 0
);

10.    Make sure you have authorized the subnet range and VPC of your Aurora instance in the master instance security group.

11.    Start the replication on your Aurora instance:

mysql> call mysql.rds_start_replication;

12.    After the replication is caught up with the master, migrate your application to use the new Aurora instance, reset the external master, and change the binary log retention period on your RDS MySQL instance:

mysql> call mysql.rds_reset_external_master;

Note: You can check the status of the replication process by running the SHOW SLAVE STATUS command on the Aurora slave database. If the value of seconds_behind_master is at or close to 0, the replication process is complete.


Did this page help you? Yes | No

Back to the AWS Support Knowledge Center

Need help? Visit the AWS Support Center

Published: 2016-12-09