Perform cross-account major version upgrades from Amazon RDS for MySQL 5.7 to Amazon Aurora 3.04.x with minimum downtime

As businesses continue to scale, the need for highly performant, scalable, and reliable databases becomes paramount. Amazon Aurora, with its advanced features and cloud-native architecture, offers an attractive solution for organizations seeking to upgrade their databases. For mission-critical, large, and complex databases, it is challenging to upgrade databases with near-zero downtime for cutover.

In this post, we guide you through the process of upgrading your RDS MySQL 5.7 database to Aurora 3.04.x (compatible with MySQL 8.0.x) from one account to another with minimal downtime. Migrating to Amazon Aurora MySQL-Compatible Edition database from one AWS account to another is a common use case in scenarios when you acquire, sell, or merge operations, or if you’re reorganizing your AWS accounts, backup and disaster recovery strategies, or organizational structure. Amazon Relational Database Service (Amazon RDS) for MySQL 5.7 shared documentation end of standard support. AWS has announced Amazon RDS Extended Support so that you can upgrade to a new major version at a pace that meets your business requirements. Upgrading to major version with end of standard support EOL as part of the migration allows your business to take advantage of newer features and a longer timeline before you’re expected to do further major version upgrades.

Solution overview

This post overview provides a high-level outline of the steps and considerations involved in migrating a MySQL 5.7 database to Aurora 3.04.x across two AWS accounts. As of this writing, customers have no direct path to upgrade to Aurora across accounts. By using a snapshot and Aurora read replicas, you can migrate the database instance to Aurora 2.07.8.x, followed by an in-place upgrade to 3.04.x. Then you can use AWS Database Migration Service (AWS DMS) with change data capture (CDC) replication tasks from Amazon RDS for MySQL to Amazon Aurora MySQL-Compatible Edition to meet strict RTO requirements and cater to your use case.

The following diagram illustrates the solution architecture.

The high-level steps for implementing this solution are as follows:

Create a snapshot from the RDS for MySQL instance in account A.
Share the snapshot of the RDS for MySQL instance with account B.
When the snapshot is available in the account B, restore the snapshot.
Create an Aurora read replica from the restored RDS for MySQL instance.
Promote the Aurora read replica to a standalone DB cluster using the AWS Management Console or AWS Command Line Interface (AWS CLI).
Perform an in-place upgrade of the Aurora standalone DB cluster from 2.07.8 to 3.04.x.
Create an AWS DMS CDC-only replication task option selected for the migration type.
Specify a custom start point before the snapshot was created, allowing AWS DMS to start replication task on the Amazon RDS for MySQL source in account A.
Monitor CDC replication lag and redirect the traffic to the target Aurora cluster when the lag reaches zero.

Benefits of upgrading to Aurora

Migrations between different AWS services, particularly cross-account migrations, come with inherent complexities. Understanding the reasons for such migrations can help businesses and developers make informed decisions:

Service upgrades – Aurora offers a variety of performance improvements and features that are not available in Amazon RDS for MySQL 5.7. By migrating to Aurora, you can harness improved performance, scalability, and reliability.
Cross-account management – Large enterprises often have multiple AWS accounts for different departments, projects, or stages in their development lifecycle. Migrating data across accounts helps in better segregation and management of resources.
Enhanced security – Cross-account migrations can be a strategic move to place critical data in a more secure environment, separated from other resources.
Near-zero downtime – You can use AWS DMS to achieve minimal disruption during cutover.
Cloud Architecture– Aurora offers enhanced performance over traditional Amazon RDS for MySQL by utilizing a distributed architecture and optimizing read and write operations.
Cost benefits – Although there might be upfront costs associated with migration, the long-term cost benefits of Aurora, given its performance enhancements and efficient data storage mechanisms, can result in overall savings.

Prerequisites

The steps and prerequisites in this section provide a general guideline for upgrading and setting up replication between MySQL and Aurora MySQL-Compatible across AWS accounts. Always refer to the latest AWS documentation and best practices for the most accurate and up-to-date information, because AWS services and features may evolve over time.

Ensure that proper network connectivity is established between the two AWS accounts. This might involve setting up VPC peering or VPN connections, depending on your network architecture.
Create AWS Identity and Access Management (IAM) roles in each account with the necessary permissions for cross-account access. The source account (account A) should allow the target account (account B) to assume its IAM role, and vice versa.
Configure security groups for both the source MySQL instance and the target Aurora MySQL instance to allow inbound and outbound traffic between them.
Verify that the MySQL version on the source instance is compatible with the Aurora MySQL version on the target instance. Note that when upgrading from Aurora 5.7 to 8.0.x, you should consider compatibility issues and features that may have changed between the two versions. It is recommended to review the MySQL community release notes to understand any potential impact on your applications and databases.
Configure SSL certificates for secure communication between the source and target databases, enhancing data security during replication.
Before migration, make sure the instance and cluster custom parameter group is created for the target Aurora MySQL newer major version 3.04.x.

Perform pre-migration tasks

It is highly recommended to reference detailed information on MySQL perquisites listed in documentation MySQL as a source when using DMS.

Complete the following pre-migration tasks:

To use logical replication and AWS DMS CDC, make sure automated backups are enabled and to also enable binary logging on the source Amazon RDS for MySQL. To enable binary logging, modify the binlog_format parameter to ROW when creating a custom parameter group (if you’re using the default parameter group).

Ensure that the binary logs are available to AWS DMS. Because AWS-managed MySQL-compatible databases purge the binary logs as soon as possible, you should increase the length of time that the logs remain available. For example, to increase log retention to 24 hours, run the following command:

mysql> call mysql.rds_set_configuration('binlog retention hours', 24);
Query OK, 0 rows affected (0.00 sec)

mysql> CALL mysql.rds_show_configuration;
+------------------------+-------+-----------------------------------------------------------------------------------------------------------+
| name                   | value | description                                                                                               |
+------------------------+-------+-----------------------------------------------------------------------------------------------------------+
| binlog retention hours | 24    | binlog retention hours specifies the duration in hours before binary logs are automatically deleted.      |
| source delay           | 0     | source delay specifies replication delay in seconds between current instance and its master.              |
| target delay           | 0     | target delay specifies replication delay in seconds between current instance and its future read-replica. |
+------------------------+-------+-----------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

For this post, we use the example database employees and create dummy data under the large_data table in it. The following SQL creates data using a simple insert for tests showing both snapshot restore and ongoing replication:
```
USE employees;

CREATE TABLE large_data (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
value DOUBLE NOT NULL,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
```

Insert sample data with the following code:

INSERT INTO large_data (name, value) VALUES ('Data1', 10.5);

When the sample data is present, take the binlog position using the following command:

mysql> show master status;
+----------------------------+----------+--------------+------------------+-------------------+
| File                       | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------------+----------+--------------+------------------+-------------------+
| mysql-bin-changelog.000228 | 456      |              |                  |                   |
+----------------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

Share a snapshot to other account

To share an RDS snapshot between AWS accounts, you can follow these steps:

Take a snapshot of the RDS for MySQL DB instance.
Share the snapshot with the target AWS account.

Sharing an unencrypted manual DB snapshot enables authorized AWS accounts to directly restore a DB instance from the snapshot instead of taking a copy of it and restoring from that. However, when sharing an encrypted DB snapshot, you can make a copy of the DB snapshot and restore the DB instance from the copy. As shown in the following screenshot, after the snapshot copy is complete, you can see the snapshot from the target AWS account.

Encrypt an RDS snapshot for the RDS for MySQL DB instance

You can restore an unencrypted RDS for MySQL snapshot to an encrypted RDS for MySQL instance. However, you must first make a copy of unencrypted snapshot, and specify a KMS encryption key when copying backup.

Restore an RDS for MySQL instance from the designated snapshot on the target account

For instructions on restoring the RDS for MySQL instance, refer to Tutorial: Restore an Amazon RDS DB instance from a DB snapshot.

Navigate to the Logs & Events tab of the restored RDS for MySQL instance and copy the binlog position from the event called “Binlog position from crash recovery.” This will be used in a later step as the start position for the AWS DMS task.

Create an Aurora read replica from the RDS for MySQL instance

To create an Aurora MySQL DB cluster from a MySQL DB instance, you can create an Aurora read replica of your source MySQL DB instance. The updates made to the source MySQL DB instance are then asynchronously replicated to the Aurora read replica cluster. This process of creating an aurora read replica may take several hours for each TB of data.

Promote the Aurora read replica to standalone

When Aurora replica cluster is online and available, you can promote to the Aurora read replica to make it a standalone Aurora MySQL DB cluster.

Upgrade Aurora MySQL 5.7 to 8.0.x

Upgrading Aurora from version 5.7 to 8.0.x involves careful planning and implementation to ensure a smooth transition. Using the console or AWS CLI, you can do an in-place upgrade of the promoted Aurora cluster.

Configure an AWS DMS CDC task

When cluster is online and available, we can create an AWS DMS replication instance and configure an AWS DMS CDC task. An AWS DMS replication instance can be created in either account. For the purpose of this blog, DMS replication instance is created in source account. Creating a CDC task allows you to replicate changes from a source database to a target database in near-real time.

AWS DMS endpoints are used to define the source and target databases in a migration task. Endpoints contain the necessary information to connect to the databases, such as server names, user credentials, ports, and database names. To create an endpoint for the source and target, you can use the console or AWS CLI to make API calls. The following screenshot shows the task creation for a CDC-only task using the AWS DMS console. For System change number, enter the binlog position taken from the Logs & Events tab of the restored RDS instance in earlier steps. Take special note of the format required (for example, mysql-bin-changelog.000228:456).

Next, we need to specify table mapping for the AWS DMS task. Make sure you use explicit rules when capturing schemas and tables because system tables can’t be migrated (they are already there from the backup and restore). You can include multiple schemas because you’re only migrating one database in this example (employees).

After you create the AWS DMS task, confirm all the tables are replicated. Monitor the CDCLatencySource and CDCLatencyTarget metrics: when the value reaches 0, it’s safe to cut over to the target Aurora MySQL cluster residing in the target AWS account. For any errors, check the Amazon CloudWatch logs.

Clean up

To clean up after cutover, delete the RDS for MySQL instance restored using the snapshot. For instructions, see Deleting a DB instance. If AWS DMS is no longer required, you should delete the AWS DMS replication instance and replication tasks.

Conclusion

In this post, we showed how to seamlessly migrate from Amazon RDS for MySQL 5.7 .x in one account to Aurora 3.04.x in another account, with an emphasis on enabling encryption. By following the steps outlined in this post, you can achieve a seamless migration with minimal downtime, ensuring that your applications benefit from the advanced features and scalability of Aurora while maintaining data integrity and application performance. The process involves a combination of backup and restore, RDS binlog replication, and AWS DMS for the migration. Ensure that each step is carefully monitored to ensure data integrity and consistency throughout the migration process.

Feel free to reach out with any queries or feedback. Happy migrating!

About the authors

Robert Daly is a Senior Database Specialist Solutions Architect at AWS, focusing on Amazon RDS, Amazon Aurora, and AWS DMS. He has helped multiple enterprise customers move their databases to AWS, providing assistance on performance and best practices. Robert enjoys helping customers build technical solutions to solve business problems.

Sudhakar Darse is a Database Specialist Solutions Architect at Amazon Web Services. He works with AWS customers to provide guidance and technical assistance on database services, helping them with database migrations to the AWS Cloud and improving the value of their solutions when using AWS.

Guy Baulch is a Database Specialist Solutions Architect at AWS, focusing on Amazon RDS, Amazon Aurora, and AWS DMS. His specialty is database architecture and migrations, where he assists customers in designing resilient and performant solutions to meet their current and future business needs.