While managing databases at scale in AWS, there are certain scenarios where you may need to maintain an Amazon Aurora cluster in a single or multiple AWS accounts. These scenarios include but are not limited to mergers and acquisitions, consolidating all accounts to use only a single account, or compliance to maintain production and development environments in separate accounts. In today’s internet-based applications, downtime is always at a premium because companies have a requirement for minimal downtime for migration activities.
In this post, we show how you can migrate Amazon Aurora MySQL-Compatible Edition databases from one account to another with minimal downtime.
You can use a simple Aurora snapshot copy to migrate a database across accounts. However, this isn’t enough to achieve minimal downtime because changes can be made by applications while the backup is created and restored in the target account. A better approach is to use a combination of a snapshot and replication. You take a snapshot of the Aurora cluster in the source account and restore it to the target account, then any incremental changes are replicated to the target. For replication, we can use either native MySQL binlog replication or AWS Database Migration Service (AWS DMS). In this post, we demonstrate both methods.
The following diagram illustrates the solution architecture.
For our solution, we take a snapshot of the source Aurora cluster and then restore it in the other account. Then we set up replication for any incremental changes based on a MySQL binary log sequence number so that all changes after the snapshot are capture and applied.
The following configurational setup is required on the source Aurora MySQL database cluster for both the native MySQL binlog replication and AWS DMS approaches:
Set up networking between the two AWS accounts using either VPC peering or AWS Transit Gateway. To test the network setup across the two AWS accounts, you can provision Amazon Elastic Compute Cloud (Amazon EC2) instances in the same subnets as Aurora and test the connectivity. Additionally, make sure to update your security groups to reference the peer security groups.
By default, Aurora doesn’t use binary logs for replication to reader instances. However, to utilize binary log replication methodology, we have to enable binary logging. We do it via creating a custom cluster parameter group (if you’re using the default parameter group) and then updating the binlog_format parameter to ROW.
We recommend setting binlog_format to ROW during replication because in certain cases if binlog_format is set to STATEMENT or MIXED, it can cause inconsistencies when replicating data to the target. For more information on row-based logging, refer to Advantages and disadvantages of row-based replication.
If the source DB cluster is attached to the default parameter group, you can create a new custom DB cluster parameter group with the modification to the binlog_format parameter and attach it to the cluster as outlined in the following steps using the AWS Command Line Interface (AWS CLI):
Create a parameter group:
Modify binlog_format to ROW:
Apply the cluster parameter group to your cluster:
Restart the cluster:
If you already have a custom parameter group attached to the source DB cluster, you can simply change the binlog_format parameter and then restart the cluster. A restart is needed because binlog_format is a static parameter.
Increase the retention period to avoid removal for binary logs before replication:
For this example, we retain the binary logs for 48 hours.
Take a snapshot of the Aurora cluster:
Share this snapshot with the target AWS account.
If the Aurora cluster is encrypted, additional steps are required to share the AWS Key Management Service encryption key. For more information, refer to Allowing access to an AWS KMS key and How do I share manual Amazon RDS DB snapshots or Aurora DB cluster snapshots with another AWS account.
When the Aurora MySQL snapshot is available in the target account, restore this snapshot in the other account:
When the restore is complete, it’s important to note the binlog crash recovery position available via Aurora events. This binary log position is needed during the setup of incremental replication. The following is a sample crash recovery message:
Lastly, on the source Aurora MySQL cluster, create a replication user in the database to be used for replication:
In the following sections, we go through the steps to set up replication using either native binlog replication or AWS DMS.
Set up replication using native binlog replication
To use native binlog replication, complete the following steps:
Check the replication status using the following command:
Read MoreAWS Database Blog