AWS Database Blog

Migrating to Amazon Aurora MySQL with fallback option using GTID-based replication

When migrating production applications, it is often important to have a fallback option. This blog post demonstrates how to use global transaction identifier (GTID)-based replication to migrate Amazon RDS MySQL workloads to Amazon Aurora MySQL. We also discuss how to use a fallback mechanism in case you encounter any issues.

For more information about GTID-based replication, see Amazon Aurora for MySQL compatibility now supports global transaction identifiers (GTIDs) replication.

In this post, the replication topology has a master RDS MySQL instance with two read replicas (RDS MySQL read replica and Aurora MySQL replica). You use the RDS MySQL read replica as a fallback instance in case of any issues during migration and leave the original RDS MySQL master unaffected. However, you can also use the original RDS MySQL master instance as a fallback option. After a successful migration, Aurora MySQL replica becomes the master instance to the RDS MySQL replica instance.

The main advantage of using GTID-based replication is that it assigns a unique identifier for every transaction, and each MySQL server in the replication topology keeps track of which transaction it has already executed. Because GTID-based replication is transaction-based, it is easy to determine data consistency between the master and the replica. If all the transactions committed on a master are also committed to the replica, you have consistency between the two. This permits auto-positioning, the ability for a replica to point at a master instance without needing to specify a binlog file name or position.

Prerequisites

To complete this walkthrough, you must meet the following prerequisites:

  • In the replication topology, the master instance and fallback replica are on RDS MySQL. For RDS MySQL instances, the GTID-based replication is supported for RDS MySQL version 5.7.23 and later MySQL 5.7 versions.
  • Because the target migration server is Aurora MySQL, the GTID-based replication is supported for MySQL 5.7-compatible clusters in Aurora MySQL version 2.04 and later.

GTID-based replication is supported for Aurora MySQL version 2.04 and later, RDS MySQL version 5.7.23 and later MySQL 5.7 versions. All RDS MySQL DB instances in a replication configuration must meet this requirement. GTID-based replication isn’t supported for RDS MySQL 5.5, 5.6, or 8.0. for more information, see Using GTID-Based Replication for Aurora MySQL and Using GTID-Based Replication for Amazon RDS MySQL.

Solution overview

This walkthrough contains the following steps:

  1. Configure GTID-based replication
  2. Configure Aurora MySQL as a new master instance and set up a fallback option to an RDS MySQL replica
  3. Switch the applications to the Aurora DB cluster
  4. Configure the fallback back to the RDS MySQL replica

Configuring GTID-based replication

The following diagram shows two possible replication scenarios. Depending on your master and read replicas, choose to either configure GTID-based replication between master RDS MySQL and your new read replicas or between existing replicas.

Configuring GTID-based replication for new read replicas

All the instances in this replication topology have a custom parameter group and are configured with parameters that enable GTID-based replication. For more information, see Parameters for GTID-Based Replication.

Configuring GTID-based replication between the master (RDS MySQL) and new read replica (RDS MySQL read replica)

To configure GTID-based replication between the master (RDS MSQL) and new replica (RDS MySQL read replica), complete the following steps:

  1. Create a custom parameter group for the master RDS MySQL instance with parameters gtid-mode = ON and enforce_gtid_consistency = ON.
  2. Associate the parameter group by modifying the DB instance.
  3. Manually reboot the RDS MySQL instance so the DB instance uses the new DB parameter group.
    If you already have a custom parameter group associated with RDS MySQL instance, to enable GTID-based replication, configure parameters gtid-mode = ON and enforce_gtid_consistency = ON. Because these parameters are static in RDS, you must reboot the RDS MySQL instance. For more information, see Working with DB Parameter Groups and Rebooting a DB Instance.
  4. Enable binary logs on the master RDS MySQL instance.
    If you already have backups enabled, binary logs are automatically enabled. Otherwise, enable binary logs on RDS MySQL instance by setting the backup retention period to a value other than 0 (this requires a reboot if you change from 0 to non-zero).
    Because you must reboot to enable binary logs in RDS MySQL, you may want to incorporate this step while configuring the custom parameter group.
  5. Configure the binary logs’ retention hours on the master RDS MySQL instance.
    By default, the RDS MySQL instance purges the binary logs as soon as possible. To set the binlog retention time frame, use the procedure mysql_rds_set_configuration and specify a configuration parameter of binlog retention hours and the number of hours to retain binlog files on the master RDS MySQL instance. For MySQL DB instances, the maximum binlog retention hours is 168 (7 days). See the following command:

    mysql> call mysql.rds_set_configuration('binlog retention hours', 24);
  6. Create an RDS MySQL read replica. For more information, see Creating a Read Replica and Working with MySQL Read Replicas.
    By default, the read replica inherits the master instance’s parameter group. As a best practice, you should remove the dependency of the master’s custom parameter group. If any changes occur to the parameters within a master DB parameter group that is specific to the master DB instance, those changes also apply to all the DB instances that are associated with that parameter group.
  7. Create a new custom parameter group for the read replica and configure parameters gtid-mode = ON and enforce_gtid_consistency = ON.
  8. Associate the newly created custom parameter group by modifying the instance and manually reboot the instance.
    Now the RDS MySQL read replica instance uses the new custom DB parameter group.
  9. Verify if GTID-based replication is enabled and replication is working as expected between the master RDS MySQL and RDS MySQL read replica.
    a.) On the master, enter the command SHOW MASTER STATUS\G and verify the Executed_Gtid_Set column, which shows the GTID for transactions that have run on the master. See the following command output: 

    mysql> SHOW MASTER STATUS\G;
*************************** 1. row ***************************
File: mysql-bin-changelog.000038
Position: 194
Binlog_Do_DB: 
Binlog_Ignore_DB: 
Executed_Gtid_Set: 7d90b125-77f6-11e9-b3b1-02bb8ffa5a04:1-19

    b.) On the replica, enter the command SHOW SLAVE STATUS\G and verify the following columns:

    Slave_IO_Running: Yes
Slave_SQL_Running: Yes

    These values mean that replication is working as expected. You can examine the Retrieved_Gtid_Set and Executed_Gtid_Set from the output. The following example output shows that the replica has retrieved transactions and executed the same transaction (1–19) that was executed on the master instance:

    mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Master_Log_File: mysql-bin-changelog.000038
Read_Master_Log_Pos: 194
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Last_Errno: 0
Last_Error: 
Master_Server_Id: 2052411434
Master_UUID: 7d90b125-77f6-11e9-b3b1-02bb8ffa5a04
Retrieved_Gtid_Set: 7d90b125-77f6-11e9-b3b1-02bb8ffa5a04:11-19
Executed_Gtid_Set: 7d90b125-77f6-11e9-b3b1-02bb8ffa5a04:1-19
Auto_Position: 1

    The preceding step confirms that the replication between the master RDS MySQL instance and RDS MySQL read replica is configured with GTID-based replication.

Configuring GTID-based replication between the master (RDS MySQL) and new replica (Aurora MySQL)

To configure GTID-based replication between the master (RDS MySQL) and the new replica (Aurora MySQL), complete the following steps:

  1. Create an Aurora read replica from the master RDS MySQL DB instance.
    For more information, see Migrating Data from a MySQL DB Instance to an Amazon Aurora MySQL DB Cluster by Using an Aurora Read Replica and Create an Amazon Aurora Read Replica from an RDS MySQL DB Instance.
    By default, the Aurora cluster uses the default parameter groups with the default configuration gtid-mode = OFF_PERMISSIVE. As a read replica, the configuration accepts both transactions with and without GTID. However, as per your replication topology, this is your target master server for the fallback option.
  2. To guarantee that every transaction has a GTID, create a custom cluster parameter group and set parameters gtid-mode = ON and enforce_gtid_consistency = ON.
  3. Associate the cluster parameter group to the newly created Aurora cluster by modifying the cluster.
  4. Manually reboot the cluster.
  5. Verify if GTID-based replication is enabled and replication is working as expected between master RDS MySQL and the Aurora read replica.
    a.) On the master, enter SHOW MASTER STATUS\G; and verify the Executed_Gtid_Set column, which shows the GTID for transactions that have run on the master. See the following command output:

    mysql> SHOW MASTER STATUS\G;
*************************** 1. row *************************
File: mysql-bin-changelog.000059
Position: 868
Binlog_Do_DB: 
Binlog_Ignore_DB: 
Executed_Gtid_Set: 7d90b125-77f6-11e9-b3b1-02bb8ffa5a04:1-46

    b.) On the replica, enter SHOW SLAVE STATUS\G; and verify the following columns:

    Slave_IO_Running: Yes
Slave_SQL_Running: Yes

    These values mean that replication is working as expected. You can examine the Retrieved_Gtid_Set and Executed_Gtid_Set from the output. The following example output shows that the replica has retrieved transactions and executed the same transaction (1–46) that was executed on the master instance:

    mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Master_Log_File: mysql-bin-changelog.000059
Read_Master_Log_Pos: 868
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Last_Errno: 0
Last_Error:
Seconds_Behind_Master: 0
Master_Server_Id: 2052411434
Master_UUID: 7d90b125-77f6-11e9-b3b1-02bb8ffa5a04
Retrieved_Gtid_Set: 7d90b125-77f6-11e9-b3b1-02bb8ffa5a04:11-46
Executed_Gtid_Set: 7d90b125-77f6-11e9-b3b1-02bb8ffa5a04:1-46
Auto_Position: 1

    The preceding step confirms that the replication between the master RDS MySQL instance and Aurora read replica is configured with GTID-based replication. 

Configuring GTID-based replication on existing read replicas

All the instances in this replication topology have a custom parameter group and are configured with parameters that enable GTID-based replication.

By default, when you create a read replica for an RDS MySQL, it uses the binary log file and positions in them to be synchronized between master and replica. So, for an existing RDS MySQL DB instance with read replicas (RDS MySQL and Aurora MySQL) that do not use GTID-based replication, you have to manually configure GTID-based replication.

Enabling GTID replication between the master (RDS MySQL) and an existing replicas (RDS MySQL & Aurora MySQL)

To enable GTID replication between the master (RDS MySQL) and an existing replicas (RDS MySQL replica and Aurora MySQL replica), complete the following steps:

  1. Verify the version of the RDS MySQL instances.
    If they are using RDS MySQL version 5.7.22 or lower, upgrade the RDS MySQL version to 5.7.23 or later MySQL 5.7 version. For more information, see Upgrading the MySQL DB Engine.
  2. Verify the version of the Aurora DB cluster
    If the Aurora MySQL version is lower than 2.04, upgrade the Aurora DB cluster version to 2.04 or later.
  3. Configure binary logs on the master instance until no longer needed.
    By default, the RDS MySQL instance purges the binary logs as soon as possible. Set the binlog retention time frame by using the mysql_rds_set_configuration and specify a configuration parameter of binlog retention hours and the number of hours to retain binlog files on the master RDS MySQL instance. For MySQL DB instances, the maximum binlog retention hours is 168 (7 days). See the following command:

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

    As a best practice, you should remove the dependency of the master’s custom parameter group.

  4. Create a different custom parameter group for the master RDS MySQL instance and RDS MySQL replica instance.
  5. For the Aurora MySQL replica, create a custom cluster parameter group.
  6. Configure the custom parameter groups of the master RDS MySQL instance, RDS MySQL read replica, and Aurora MySQL read replica with gtid-mode = OFF_PERMISSIVE and enforce_gtid_consistency = WARN.
  7. Associate the parameter groups to the RDS instances respectively by modifying the RDS DB instances.
  8. After you modify the default parameter group of the DB instances (master RDS MySQL, RDS MySQL read replica and Aurora MySQL replica), manually reboot the instances before the DB instances use the new custom parameter group.
  9. Make sure that no warnings are being generated in the error logs before going to the next step.
    If there are any warnings, adjust your applications so that it only uses GTID-compatible features and does not generate any warnings. For more information, see Restrictions on Replication with GTIDs on the MySQL website.
  10. After you verify that there are no warnings are generated in the error logs, configure the parameter groups of the master RDS MySQL and read replicas (RDS MySQL read replica, and Aurora MySQL replica) with gtid-mode = ON_PERMISSIVE and enforce_gtid_consistency = WARN.
  11. Manually reboot the instances so the DB instances can use the parameters.
  12. Make sure that there are no transactions in the relay log that do not have a GTID assigned.
    To verify these anonymous transactions, on both the read replicas (RDS MySQL and Aurora MySQL), run the command: SHOW STATUS LIKE 'ONGOING_ANONYMOUS_TRANSACTION_COUNT';. See the following command output:

    mysql> SHOW STATUS LIKE 'ONGOING_ANONYMOUS_TRANSACTION_COUNT';
+-------------------------------------+-------+
| Variable_name                       | Value |
+-------------------------------------+-------+
| Ongoing_anonymous_transaction_count | 0     |
+-------------------------------------+-------+
  13. After you verify there are no anonymous transactions, set the GTID on the master and read replica’s custom parameter group with gtid_mode = ON and enforce_gtid_consistency = ON.
  14. Manually reboot the instances.
  15. Make sure that there are no anonymous transactions on both the read replicas.
  16. Execute the rds_set_master_auto_position on both the read replicas.
    This sets the replication mode to use the GTID-based replication method. See the following command:

    mysql> CALL mysql.rds_set_master_auto_position (1);
  17. Connect to the master RDS MySQL and read replicas (RDS MySQL replica and Aurora MySQL replica) and verify that GTID is enabled and replication is working as expected.
    a.) On the master, enter SHOW MASTER STATUS\G; and verify the Executed_Gtid_Set column, which shows the GTID for transactions that have run on the master. See the following command output:

    mysql> SHOW MASTER STATUS\G;
*************************** 1. row ***************************
File: mysql-bin-changelog.119716
Position: 194
Binlog_Do_DB: 
Binlog_Ignore_DB: 
Executed_Gtid_Set: d8e0b642-62b2-11e9-a674-06f30def75ee:1-5091

    b.) On the replicas, enter SHOW SLAVE STATUS\G; and verify the following columns:

    Slave_IO_Running: Yes
Slave_SQL_Running: Yes

    These values mean that replication is working as expected. You can examine the Retrieved_Gtid_Set and Executed_Gtid_Set from the output. The following example output shows that the replica server has retrieved the transaction and executed the same transaction (1–5091) that was executed on the master instance:

    mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Log_File: mysql-bin-changelog.119716
Read_Master_Log_Pos: 194
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Last_Errno: 0
Last_Error: 
Seconds_Behind_Master: 0
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Retrieved_Gtid_Set: d8e0b642-62b2-11e9-a674-06f30def75ee:5090-5091
Executed_Gtid_Set: d8e0b642-62b2-11e9-a674-06f30def75ee:1-5091
Auto_Position: 1

    The preceding step confirms that the replication between the master RDS MySQL and read replicas (RDS MySQL read replica and Aurora read replica) are configured with GTID-based replication. The following diagram illustrates this configuration.

Configuring the Aurora MySQL replica as a new master instance and a fallback option

Before you begin, verify that the replications are in sync, because replication is asynchronous by default. The replication lag can vary predominantly based on the volume of writes on the master that must be applied to the replicas in a single-threaded process. So, the best recommendation is to stop the application and verify if replications are in sync.

To configure the Aurora MySQL replica as the new master instance and a fallback option, complete the following steps:

  1. Make sure that there are no writes on the master RDS MySQL DB instance by configuring the instance as read-only.
    To set the instance in read-only mode, modify the custom parameter group that is assigned to the instance. The parameter read_only defaults to a value of {TrueIfReplica}. In this use case, set this value to 1, which indicates that the instance is in read-only mode. This parameter has a dynamic apply type, which means that the change takes effect immediately and doesn’t require a reboot.
  2. Wait for any in-flight transactions to replicate fully to both replica instances (Aurora MySQL replica and RDS MySQL replica).
  3. To verify, connect to the replica instances and enter SHOW SLAVE STATUS\G; several times in a relatively short period.
  4. Check the columns Slave_IO_State, Slave_IO_Running, Slave_SQL_Running, Slave_SQL_Running_State, Seconds_Behind_Master, and Executed_Gtid_Set.
    You can examine the Executed_Gtid_Set from the output, which should be the same as the master’s Executed_Gtid_Set if replicas are in sync. When the replicas are in sync with the RDS MySQL master, Seconds_Behind_Master naturally be 0. SQL_IO_State and SQL_SQL_Running_State should look similar to as shown below:

    Slave_IO_Running: Yes
Slave_IO_State: Waiting for master to send event
Slave_SQL_Running: Yes
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

    For more information: Replication Slave I/O Thread States and Replication Slave SQL Thread States on the MySQL website.

  1. When the replicas are in sync, configure Aurora MySQL as a new master instance and set up a fallback option to RDS MySQL read replica.
    The following diagram illustrates this architecture.

Configuring the Aurora MySQL DB cluster as master

To configure the Aurora MySQL DB cluster as the master, complete the following steps:

  1. Promote the Aurora read replica to a standalone Aurora DB cluster.
    For more information, see Promoting an Aurora Read Replica.
  2. Configure GTID-based replication from the Aurora DB cluster to the RDS MySQL read replica.
    To do so, you must enable binary logs on the Aurora DB cluster. For more information, see Enable Binary Logging on the Replication Master.
    Update the cluster parameter group with binlog_format = “MIXED” (You can also use other formats like ROW or STATEMENT, but it is recommended to set as MIXED). For more information, see Setting The Binary Log Format and Advantages and Disadvantages of Statement-Based and Row-Based Replication on the MySQL website.
  3. Reboot the Aurora DB cluster.
  4. To verify that the binary logs are being generated, connect to the Aurora cluster and enter SHOW BINARY LOGS;. See the following command output: 
    mysql> SHOW BINARY LOGS;
+----------------------------+-----------+
| Log_name                   | File_size |
+----------------------------+-----------+
| mysql-bin-changelog.000001 |       154 |
| mysql-bin-changelog.000002 |      3030 |
+----------------------------+-----------+
  5. While connected to the Aurora cluster, create a replication account. See the following command: 
    mysql> CREATE USER 'repl_user'@'<domain_name>' IDENTIFIED BY '<password>';

    For security reasons, use this account only for replication. For more information, see Creating a User for Replication on the MySQL website.

  6. Grant REPLICATION SLAVE privileges to your replication user. See the following command: 
    mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'<domain_name>' IDENTIFIED BY '<password>';

Configuring the RDS MySQL read replica instance as a replica of the Aurora MySQL DB cluster

To configure the RDS MySQL read replica instance as a replica of the Aurora MySQL DB cluster for a fallback option, complete the following steps:

  1. Promote the RDS MySQL read replica as a standalone DB instance.
  2. Reboot the instance.
    For more information, see Promoting a Read Replica to Be a Standalone DB Instance.
  3. Connect to RDS MySQL read replica using the master user and enter rds_set_external_master_with_auto_position.
    This stored procedure performs the necessary changes so the RDS MySQL read replica can connect to the Aurora MySQL DB cluster and request binary logs. It also configures replication based on global transaction identifiers. See the following command:

    mysql> CALL mysql.rds_set_external_master_with_auto_position (AuroraClusterEndpoint',3306,'repl_user','password',0,0);
  4. Run the reset slave to clear old references. See the following command: 
    mysql> reset slave;

    For more information, see RESET SLAVE Statement on the MySQL website.

  5. Start the replication on the target RDS MySQL read replica. See the following command: 
    mysql> call mysql.rds_start_replication();

The preceding steps configure the GTID-based replication between the new master instance (Aurora MySQL) and the replica instance (RDS MySQL read replica).

Switching the applications to use the Aurora DB cluster

You are now ready to switch your applications to use the new Aurora MySQL master. When you migrate from MySQL (RDS or on-premises) to Aurora MySQL, it is important to know that Aurora is wire-compatible with MySQL (5.6 and 5.7) using the InnoDB storage engine. You can use most of the drivers, connectors, and tools that you currently use for MySQL with Aurora MySQL with little or no change. Therefore, most of the applications work without modifications to the application code during the migration process.

To switch the applications to use the Aurora DB cluster, complete the following steps:

  1. Point your applications to the new Aurora cluster endpoint. There are two ways to do this depending on your approach that is being used in the applications:
  • Change the connection strings for all the applications with Aurora cluster endpoint.
  • If your applications are using the custom canonical name (CNAME), which points to the hostname of the database engine, update the CNAME record in the Domain Name System (DNS) with Aurora cluster endpoint.
  1. After the applications are pointed to the Aurora DB cluster, verify that the replication is running as expected.
    a.) On the master Aurora DB cluster, enter SHOW MASTER STATUS\G; and verify the Executed_Gtid_Set See the following command output:

    mysql> SHOW MASTER STATUS\G;
*************************** 1. row ***************************
File: mysql-bin-changelog.000002
Position: 4615
Binlog_Do_DB: 
Binlog_Ignore_DB: 
Executed_Gtid_Set: 5c619be5-381d-3436-9104-3d8fbb228334:1-10,
7d90b125-77f6-11e9-b3b1-02bb8ffa5a04:1-24:26-46

    b.) On the slave RDS read replica, enter SHOW SLAVE STATUS\G;. See the following command output:

    mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: AuroraClusterEndpoint.us-west-2.rds.amazonaws.com
Master_User: repl_user
Master_Port: 3306
Master_Log_File: mysql-bin-changelog.000002
Read_Master_Log_Pos: 4615
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Last_Errno: 0
Last_Error: 
Exec_Master_Log_Pos: 4615
Seconds_Behind_Master: 0
Master_UUID: 5c619be5-381d-3436-9104-3d8fbb228334
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Retrieved_Gtid_Set: 5c619be5-381d-3436-9104-3d8fbb228334:1-10
Executed_Gtid_Set: 5c619be5-381d-3436-9104-3d8fbb228334:1-10,
7d90b125-77f6-11e9-b3b1-02bb8ffa5a04:1-46
Auto_Position: 1

From the preceding results, the following parameters provide important information:

  • Master_Host confirms that the slave is connected to the newly configured Aurora DB cluster as a master
  • Retrieved_Gtid_Set and Executed_Gtid_Set confirm that the data is replicating as expected from master to slave
  • Exec_Master_Log_Pos shows that slave auto-positioned to the master’s binary log position

The verification confirms that the replication between the master instance (Aurora MySQL) and replica instance (RDS MySQL read replica) is running and configured with GTID-based replication.

Falling back to the RDS MySQL replica

To configure your fallback, complete the following steps:

  1. Stop the application.
  2. Configure the Aurora cluster as read-only.
    This makes sure that there are no writes on the Aurora DB cluster. To set the cluster to read-only, modify the custom cluster parameter group that is assigned to your cluster. The parameter read_only defaults to a value of {TrueIfReplica}. In this use case, set this value to 1, which indicates that the Aurora MySQL Cluster is in read-only mode. This parameter has a dynamic apply type, which means that the change takes effect immediately and doesn’t require a reboot.
  3. Wait for in-flight transactions to replicate fully to the replica (RDS MySQL read replica).
  4. Verify that the replication is in sync.
  5. Connect to the replica (RDS MySQL read replica) and enter SHOW SLAVE STATUS\G; several times in a relatively short period.
  6. Check the columns Slave_IO_State, Slave_IO_Running, Slave_SQL_Running, Slave_SQL_Running_State, Seconds_Behind_Master, and Executed_Gtid_Set.
    You can examine the Executed_Gtid_Set from the output, which should be the same as the master’s Executed_Gtid_Set if the replicas are in sync. When the replica is in sync with the Aurora MySQL master, Seconds_Behind_Master should naturally be 0. SQL_IO_State and SQL_SQL_Running_State should look like as shown below:

    Slave_IO_Running: Yes
Slave_IO_State: Waiting for master to send event
Slave_SQL_Running: Yes
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

    After you verified that the replica is up to date with the master instance, it’s safe to stop the replication on the replica (RDS MySQL read replica).

  7. Run the following commands - CALL mysql.rds_stop_replication; and reset slave;.
    This terminates the replication from the master Aurora MySQL instance.
    At this point, your RDS MySQL read replica is a standalone DB instance. You can now repoint your applications to a standalone DB instance endpoint as a fallback option.

Summary

This post showed the process of migrating RDS MySQL instance to an Aurora MySQL DB cluster with an RDS MySQL read replica as a rollback option. We used GTID-based replication in our scenario to leverage the benefits such as, enforce consistency and binary log auto-positioning in complex replication stream. You can also use GTID-based replication to migrate your on-premises MySQL or EC2 MySQL instances to Amazon Aurora MySQL.

 

About the Author

 

Vijay Karumajji is a Database Solutions Architect with Amazon Web Services. He works with our customers to provide guidance and technical assistance on database projects, helping them improving the value of their solutions when using AWS.