Perform cross-account Amazon Aurora MySQL migration with minimal downtime

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.

Solution overview

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.

Prerequisites

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:

aws rds create-db-cluster-parameter-group \
    --db-cluster-parameter-group-name repl-clstr-param-group\
    --db-parameter-group-family aurora-mysql5.7 \
    --description "repl source cluster parameter group"

Modify binlog_format to ROW:

aws rds modify-db-cluster-parameter-group \
	--db-cluster-parameter-group-name repl-clstr-param-group \
	--parameters "ParameterName='binlog_format',ParameterValue='ROW',ApplyMethod= pending-reboot"

Apply the cluster parameter group to your cluster:

aws rds modify-db-cluster \
   	--db-cluster-identifier repl-source \
--db-cluster-parameter-group-name repl-clstr-param-group --apply-immediately

Restart the cluster:

aws rds reboot-db-instance \
     	--db-instance-identifier repl-source-instance-1

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:

CALL mysql.rds_set_configuration('binlog retention hours', 48);

For this example, we retain the binary logs for 48 hours.

Take a snapshot of the Aurora cluster:

aws rds create-db-cluster-snapshot \
  --db-cluster-identifier repl-source \
   		 --db-cluster-snapshot-identifier src-snapshot

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:

aws rds restore-db-cluster-from-snapshot \
 --db-cluster-identifier repl-target \
--snapshot-identifier arn:aws:rds:us-east-1:xxxxxxxxx:cluster-snapshot:src-snapshot \
--engine aurora-mysql \
--engine-version 5.7 \
--vpc-security-group-ids sg-xxxxxxxx \
--db-subnet-group-name aurora-subnet-grp \
--no-publicly-accessible

aws rds create-db-instance \
--db-instance-identifier repl-target-instance \
--db-instance-class db.r6g.2xlarge \
--engine aurora-mysql \
--db-subnet-group-name aurora-subnet-grp  \
--db-cluster-identifier repl-target

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:

"Message": "Binlog position from crash recovery is mysql-bin-changelog.000002 154”

Lastly, on the source Aurora MySQL cluster, create a replication user in the database to be used for replication:
```
mysql> CREATE USER 'repl_user'@'%' IDENTIFIED BY ‘<enter_your_password>’;
```
SQL

mysql> GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'repl_user'@'%';

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:

Use MySQL external master and start replication procedures on the target Aurora MySQL using the binlog position you noted earlier:

mysql> CALL mysql.rds_set_external_master ( 'repl-source.cluster-xxxx.us-east-1.rds.amazonaws.com', 3306, 'repl_user',<enter_your_password>,'mysql-bin-changelog.000002',154,0);

mysql> CALL mysql.rds_start_replication;

Check the replication status using the following command:

mysql> show slave status\G;

Monitor the Seconds_Behind_Master value from the previous command for replication lag between the source and target Aurora databases. When the Seconds_Behind_Master value is 0, the source and target Aurora MySQL databases are in sync.

Before switching over to the target Aurora cluster in the new AWS account, perform the following pre-switchover tasks:

Verify the configurations of the target cluster in the new account to confirm database infrastructure readiness.
Test the application-critical DB operations using the target Aurora cluster in the new account.
If the application is sensitive to DB performance, pre-warm the database cache by running the top SELECT queries on the target Aurora cluster.
Stop the application writes to the source Aurora cluster and redirect the connection to the target Aurora cluster.

For more information, refer to the pre-switchover tasks section of Performing major version upgrades for Amazon Aurora MySQL with minimum downtime.

Set up replication using AWS DMS

We can use the native CDC start point functionality of AWS DMS to capture the incremental changes past the snapshot restore for performing cross-account Aurora MySQL database migration. After you share and restore the Aurora MySQL snapshot and create the cluster in another account, note the crash recovery position from the logs and events of the Aurora writer instance via the the AWS Management Console or AWS CLI. Then complete the following steps:

Review the prerequisites for MySQL as a source and target to prepare the two Aurora MySQL databases.
Create an AWS DMS replication instance and configure it such that it can connect to the source and target databases.
Create source and target AWS DMS endpoints and check if the connection is successful.
Create an AWS DMS CDC-only replication task and with the Replicate data changes only option selected for the migration type.
Enable custom CDC start mode and select Specify a log sequence number.
Enter the crash recovery position captured earlier (for example, mysql-bin-changelog.000002:154).
Specify the appropriate mapping rules and settings to create the task.
After the task status changes to Created, start the task.
Monitor the AWS DMS task for any failures and validate the data using AWS DMS data validation for data consistency.
After you have verified data consistency, work with the application teams to declare a maintenance window.
Monitor CDCLatencySource and CDCLatencyTarget: when their value is 0, cut over to the target Aurora MySQL cluster residing in another AWS account (don’t forget any pre-switchover tasks as discussed earlier).

Clean up

After you cut over to the target Aurora MySQL cluster, you can stop the source Aurora cluster to avoid charges. If it’s not a one-way migration and you intend to keep the cluster, reset the binlog retention period and disable binlogs on the source by setting the binlog_format parameter in the DB cluster parameter group to OFF. After the migration is complete, and there is no need to retain the source cluster, you can delete it. For instructions, refer to Deleting an Aurora cluster and DB instances. If you used AWS DMS to replicate incremental changes to the target, you should delete the AWS DMS replication instance as well if it’s no longer required.

Conclusion

In this post, you learned how to migrate an Aurora MySQL cluster across different accounts with minimal downtime using either native binlog replication or AWS DMS. We recommend first using the native MySQL replication approach and you if identify any issues, explore the AWS DMS alternative.

Reach out with questions or requests in the comments. Happy migrating!

About the authors

Amay Chopra is a Database Consultant with AWS Professional Services based in Dallas, Texas. He works with AWS customers on a broad range of services to help build custom solutions and operate production workloads on AWS. Outside of work, he enjoys playing lawn tennis, traveling to new places, and spending time with family and friends.

Sushant Deshmukh is a Database Consultant with AWS Professional Services Team. He works with AWS customers and partners to build highly available, scalable and secured database architectures on AWS. He provides technical design and implementation expertise in running database workloads on AWS, also helping customers migrate and modernize their databases to AWS Cloud. Outside of work, he enjoys traveling and exploring new places, playing volleyball and spending time with his family and friends.

Devinder Singh is an SA Manager with AWS. He has over 25 years of experience working with various database and storage technologies. Devinder focuses on helping customers on their journey to AWS and helping them architect highly available and scalable database solutions based on various relational and NoSQL AWS Database services. When not working with customers, you can always find Devinder enjoying long hikes or biking.

Select your cookie preferences

AWS Database Blog