Migrating from MySQL-compatible databases - Amazon Aurora MySQL Migration Handbook
Percona XtraBackupSelf-managed export/importAWS Database Migration Service

Migrating from MySQL-compatible databases

Moving to Amazon Aurora is still a relatively simple process if you are migrating from an RDS for MySQL, or a self- managed MySQL or MySQL-compatible database such as MariaDB, or Percona Server running on Amazon Elastic Compute Cloud (Amazon EC2) or on-premises.

There are many techniques you can use to migrate your MySQL-compatible database workload to Amazon Aurora. This section describes various migration options to help you choose the most optimal solution for your use case.

Percona XtraBackup

Amazon Aurora supports physical migration using Percona XtraBackup method and Amazon S3 bucket. Migrating from binary backup files can be significantly faster than migrating from logical schema and data dumps using tools such as mysqldump. Logical imports work by running SQL commands to re-create the schema and data from the source database, which involves considerable processing overhead. By comparison, you can use a more efficient binary ingestion method to ingest Percona XtraBackup files. Aurora supports both full and incremental backups created using Percona XtraBackup. If you already use Percona XtraBackup to perform full and incremental backups of your MySQL database files, you don’t need to create a full backup again. Instead, you can save a significant amount of time by copying your existing backup directories and files for your full and incremental backups to an Amazon S3 bucket.

This migration method is compatible with source servers using MySQL versions 5.7 and 8.0. We can migrate using Percona Xtrabackup with downtime (without replication) or with near-zero downtime (with binary log replication).

Percona Xtrabackup migration with downtime

The high-level procedure for migrating to Aurora MySQL from a MySQL database is as follows:

  1. Stop all write activity against the source database. Application downtime begins here.

  2. Create backup of the source database using Percona Xtrabackup tool.

  3. Upload the backup files to an Amazon S3 bucket.

  4. Restore backup files to an Aurora MySQL DB cluster through AWS Management Console or AWS CLI.

Note

Refer to the Example Migration Scenarios section for more details.

For details and step-by-step instructions, refer to Migrating data from MySQL by using an Amazon S3 Bucket in the Amazon RDS User Guide.

Percona Xtrabackup migration with near-zero downtime

The following is the high-level procedure for near-zero downtime migration into Aurora MySQL from a self-managed MySQL database:

  1. On the source database, enable binary logging and ensure that binary log files are retained for at least the amount of time that is required to complete the remaining migration steps.

  2. Create backup of the source database using Percona Xtrabackup tool.

  3. Upload the backup files to an Amazon S3 bucket.

  4. Restore the backup files to Aurora MySQL DB cluster through the AWS Management console or AWS CLI.

  5. Capture the binary log and its position from the Events section of the newly restored Aurora cluster then setup the replication between source database and the target Aurora cluster.

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

  7. Stop all write activity against the source database instance. Application downtime begins here.

  8. Double-check that there is no outstanding replication lag. Then configure applications to connect to the newly created target Aurora DB cluster instead of the source database.

  9. Resume write activity. Application downtime ends here.

  10. End replication between the source database and the target Aurora DB cluster.

Note

Refer to the Example Migration Scenarios section for more details.

For details and step-by-step instructions, refer to the example section of this document or Migrating data from MySQL by using an Amazon S3 bucket in the Amazon Aurora User Guide.

Self-managed export/import

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

The tools can be described as MySQL native because they are either part of a MySQL project or were designed specifically for MySQL- compatible databases.

Examples of native migration tools include the following:

  1. MySQL utilities such as mysqldump, mysqlpump, mysqlimport, and mysql command-line client.

  2. Third-party utilities such as mydumper and myloader. For details, refer to this mydumper project page.

  3. Built-in MySQL commands such as SELECT INTO OUTFILE and LOAD DATA INFILE.

Native tools are a great option for power users or database administrators who want to maintain full control over the migration process. Self-managed migrations involve more steps and are typically slower than RDS snapshot or Percona XtraBackup migrations, but they offer the best compatibility and flexibility.

You can do a self-managed migration with downtime (without replication) or with near-zero downtime (with binary log replication).

Self-managed migration with downtime

The high-level procedure for migrating to Amazon Aurora from a MySQL-compatible database is as follows:

  1. Stop all write activity against the source database. Application downtime begins here.

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

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

  4. Configure applications to connect to the newly created target Aurora DB cluster instead of the source database.

  5. Resume write activity. Application downtime ends here.

Note

Refer to the Example Migration Scenarios section for more details.

Self-managed migration with near-zero downtime

The following is the high-level procedure for near-zero downtime migration into Amazon Aurora from a MySQL-compatible database:

  1. On the source database, enable binary logging and ensure that binary log files are retained for at least the amount of time that is required to complete the remaining migration steps.

  2. Perform a schema and data export from the source database. Make sure that the export metadata contains binary log coordinates that are required to establish replication at a later time.

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

  4. On the target Aurora DB cluster, configure binary log replication from the source 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 applications to connect to the newly created target Aurora DB cluster instead of the source database.

  8. Resume write activity. Application downtime ends here.

  9. Terminate replication between the source database and the target Aurora DB cluster.

Note

Refer to the Example Migration Scenarios section for more details.

AWS Database Migration Service

AWS Database Migration Service is a managed database migration service that is available through the AWS Management Console. It can perform a range of tasks, from simple migrations with downtime to near-zero downtime migrations using CDC replication.

AWS Database Migration Service may be the preferred option if your source database can’t be migrated using methods described previously, such as the Aurora Read Replica, RDS snapshot migration, Percona XtraBackup migration, or native export/import tools.

AWS Database Migration Service might also be advantageous if your migration project requires advanced data transformations such as the following:

  • Remapping schema or table names

  • Advanced data filtering

  • Migrating and replicating multiple database servers into a single Aurora DB cluster

Compared to the migration methods described previously, AWS DMS carries certain limitations:

  • It does not migrate secondary schema objects such as indexes, foreign key definitions, triggers, or stored procedures. Such objects must be migrated or created manually prior to data migration.

  • The DMS CDC replication uses plain SQL statements from binlog to apply data changes in the target database. Therefore, it might be slower and more resource-intensive than the native binary log replication in MySQL.

For step-by-step instructions on how to migrate your database using AWS DMS, refer to the AWS whitepaper Migrating Your Databases to Amazon Aurora.