Customers are responsible for making their own independent assessment of the

information in this document. This document: (a) is for informational purposes only, (b)

represents current AWS product offerings and practices, which are subject to change

© 2020 Amazon Web Services, Inc. or its affiliates. All rights reserved.

Introduction .......................................................................................................................... 5

Database Migration Considerations .................................................................................... 6

Homogeneous Migrations ................................................................................................. 11

Example Migration Scenarios ........................................................................................... 28

Testing and Cutover .......................................................................................................... 46

Troubleshooting ................................................................................................................. 49

This paper outlines the best practices for planning, executing, and troubleshooting

database migrations from MySQL-compatible and non- MySQL-compatible database

products to Amazon Aurora. It also teaches Amazon Aurora database administrators

For decades, traditional relational databases have been the primary choice for data

storage and persistence. These database systems continue to rely on monolithic

architectures and were not designed to take advantage of cloud infrastructure. These

monolithic architectures present many challenges, particularly in areas such as cost,

flexibility, and availability. In order to address these challenges, AWS redesigned

relational database for the cloud infrastructure and introduced Amazon Aurora.

Amazon Aurora is a MySQL-compatible relational database engine that combines the

speed, availability, and security of high-end commercial databases with the simplicity

and cost-effectiveness of open-source databases. Aurora provides up to five times

are completely automated.

Amazon Aurora is built for mission-critical workloads and is highly available by default.

An Aurora database cluster spans multiple Availability Zones (AZs) in a region,

providing out-of-the-box durability and fault tolerance to your data across physical data

centers. An Availability Zone is composed of one or more highly available data centers

operated by Amazon. AZs are isolated from each other and are connected through low-

latency links. Each segment of your database volume is replicated six times across

these AZs.

Aurora cluster volumes automatically grow as the amount of data in your database

increases with no performance or availability impact, so there is no need for estimating

and provisioning large amount of database storage ahead of time. An Aurora cluster

backups are automatic, incremental, and continuous and have no impact on database

performance.

For applications that need read-only replicas, you can create up to 15 Aurora Replicas

underlying storage as the source instance, lowering costs and avoiding the need to

perform writes at the replica nodes.

Amazon Aurora is highly secure and allows you to encrypt your databases using keys

that you create and control through AWS Key Management Service (AWS KMS). On a

database instance running with Amazon Aurora encryption, data stored at rest in the

underlying storage is encrypted, as are the automated backups, snapshots, and replicas

in the same cluster. Amazon Aurora uses SSL (AES-256) to secure data in transit.

For a complete list of Aurora features, see Amazon Aurora. Given the rich feature set

and cost effectiveness of Amazon Aurora, it is increasingly viewed as the go-to

database for mission-critical applications.

Migrations are among the most time-consuming and critical tasks handled by database

administrators. Although the task has become easier with the advent of managed

migration services such as AWS Database Migration Service, large-scale database

migrations still require adequate planning and execution to meet strict compatibility and

performance requirements.

iterative approach.

This paper examines the following major contributors to the success of every database

migration project:

performance, cost, and high availability and durability

engines, you should make sure that your application works with Amazon Aurora.

Amazon Aurora is designed to be wire-compatible with MySQL 5.5,5.6,5.7 and 8.0

Therefore, most of the code, applications, drivers, and tools that are used today with

MySQL databases can be used with Aurora with little or no change.

However, certain MySQL features, like the MyISAM storage engine, are not available

with Amazon Aurora. Also, due to the managed nature of the Aurora service, SSH

access to database nodes is restricted, which may affect your ability to install third-party

tools or plugins on the database host.

For more details, see Aurora on Amazon RDS in the Amazon Relational Database

Service (Amazon RDS) User Guide.

Performance is often the key motivation behind database migrations. However,

or by replaying the production workload.

eliminates delays created by database process threads. Our tests with SysBench on

r3.8xlarge instances show that Amazon Aurora delivers over 585,000 reads per second

and 107,000 writes per second, five times higher than MySQL running the same

application may already have very strict recovery time objective (RTO) and recovery

If you are running multiple shards or functional partitions of your database to achieve

management, but it also significantly improves performance of cross-partition queries.

to different tasks. For example, in an e-commerce application, you might have

one database node serving product catalog data, and another database node

capturing and processing orders. As a result, these partitions usually have

distinct, nonoverlapping schemas.

schema to your target Aurora instance. If your source database is MySQL

replication. If your source database is non-MySQL complaint, use AWS

Schema Conversion Tool to migrate the schemas to Aurora and use AWS

DMS for one-time load or continuous replication.

shards while keeping the same table schema.

schema, you only need to create the target schema once. If you are using a

MySQL-compliant database, use native tools to migrate the database

stopped for an extended period, you might still use AWS DMS with

If you are migrating from an RDS MySQL 5.6 database (DB) instance, the

checklist.

When you migrate a snapshot of a MySQL DB instance to an Aurora DB cluster, Aurora

compressed tables are uncompressed. Consequently, there must be adequate room for

the additional copies of any such tables.

The size of the migration volume is based on the allocated size of the source MySQL

database that the snapshot was made from. Therefore, if you have MyISAM or

there is available space in the original database, then migration should succeed without

(uncompressed) copy of compressed tables, then the migration volume will not be big

enough. In this situation, you would need to modify the source Amazon RDS MySQL

database to increase the database size allocation to make room for the additional

copies of these tables, take a new snapshot of the database, and then migrate the new

snapshot.

When migrating data into your DB cluster, observe the following guidelines and

Non MyISAM tables in the source database can be up to 6 TB in size. However, due to

additional space requirements during conversion, make sure that none of the MyISAM

and compressed tables being migrated from your MySQL DB instance exceed 3 TB in

size. For more information, see Migrating Data from an Amazon RDS MySQL DB

Instance to an Amazon Aurora MySQL DB Cluster.

MySQL database but rather on a database instance that was restored from your

production snapshot. For more details on doing this, see Reducing the Amount of

Space Required to Migrate Data into Amazon Aurora in the Amazon RDS User Guide.

The naming conventions used in this section are as follows:

are migrating from.

to migrate an RDS MySQL 5.6 DB instance to Amazon Aurora:

2. Take a snapshot of the source RDS DB instance.

3. Wait until the snapshot shows as Available in the AWS Management Console.

4. Use the AWS Management Console to migrate the snapshot to a new Aurora

5. Wait until the snapshot migration finishes and the target Aurora DB cluster

replication.

Perform the high-level procedure as follows:

1. On the source RDS DB instance, ensure that automated backups are enabled.

2. Create a Read Replica of the source RDS DB instance.

3. After you create the Read Replica, manually stop replication and obtain binary

4. Take a snapshot of the Read Replica.

5. Use the AWS Management Console to migrate the Read Replica snapshot to a

6. Wait until snapshot migration finishes and the target Aurora DB cluster enters

7. On the target Aurora DB cluster, configure binary log replication from the source

11. Complete cut-over by resuming write activity. Application downtime ends here.

12. Terminate replication between the source RDS DB instance and the target

For a detailed description of this procedure, see Replication Between Aurora and

Guide.

Replica from a source RDS MySQL 5.6 DB instance by using the RDS Management

Console.

The RDS automation does the following:

1. Creates a snapshot of the source RDS DB instance.

Direct snapshot migration is only supported for RDS MySQL 5.6 DB instance

snapshots. You can migrate RDS MySQL DB instances that are running other engine

versions by using the following procedures.

Follow these steps to migrate RDS MySQL 5.1 or 5.5 DB instances to Amazon Aurora:

1. Upgrade the RDS MySQL 5.1 or 5.5 DB instance to MySQL 5.6.

MySQL 5.6.

For more information about upgrading RDS MySQL engine versions, see Upgrading the

MySQL DB Engine in the Amazon RDS User Guide.

For migrations from RDS MySQL 5.7 DB instances, the snapshot migration approach is

not supported because the database engine version can’t be downgraded to MySQL

5.6.

In this case, we recommend a manual dump-and-import procedure for migrating

MySQL-compatible databases, described later in this whitepaper. Such a procedure

may be slower than snapshot migration, but you can still perform it with near-zero

downtime using binary log replication.

Moving to Amazon Aurora is still a relatively simple process if you are migrating from an

Amazon Aurora supports migration from Percona XtraBackup files that are stored in an

imports work by executing SQL commands to re-create the schema and data from your

source database, which involves considerable processing overhead. By comparison,

3. Restore backup files through the AWS Management Console.

You can use a variety of export/import tools to migrate your data and schema to

Examples of native migration tools include the following:

client.

AWS whitepaper Best Practices for Migrating MySQL Databases to Amazon Aurora.

You can execute a self-managed migration with downtime (without replication) or with

2. Perform a schema and data dump from the source database.

Aurora from a MySQL-compatible database:

are retained for at least the amount of time that is required to complete the

replication at a later time.

3. Import the dump into the target Aurora DB cluster.

database using the binary log coordinates that you obtained in step 2.

5. Wait for the replication to catch up, that is, for the replication lag to reach zero.

6. Stop all write activity against the source database instance. Application downtime

begins here.

7. Double-check that there is no outstanding replication lag. Then configure

the source database.