Migrate to Amazon RDS Using Percona Xtrabackup

April 2, 2018

Author

Daniel Kowalewski

Cloud

Insight for DBAs

MySQL

Share this Post:

In this blog post, we’ll look at how to migrate to Amazon RDS using Percona XtraBackup.

Until recently, there was only one way to migrate your data from an existing MySQL instance into a new RDS MySQL instance: take and restore a logical backup with mysqldump or mydumper. This can be slow and error-prone. When Amazon introduced Amazon Aurora MySQL, you could use Percona XtraBackup to take an online physical backup of your database and restore that into a new Aurora instance. This feature is now available for RDS MySQL as well. Using Percona XtraBackup instead of a logical backup can save a lot of time, especially with a large dataset.

There are many caveats and limitations listed in Amazon’s documentation, but the most important ones are:

Source and destination databases must be MySQL 5.6. Earlier and later major versions are not supported at this time.

You can’t restore into an existing RDS instance using this method.

The total data size is limited to 6 TB.

User accounts, functions, and stored procedures are not imported automatically.

You can’t choose which databases and tables to migrate this way — migrate the whole instance. (You can’t use Percona Xtrabackup’s partial backup feature when migrating to RDS.)

If those limitations don’t apply to your use case, read on to learn how to migrate to Amazon RDS using Percona XtraBackup and restoring it into RDS.

Demonstration – Migrate to Amazon RDS Using Percona Xtrabackup

For this demonstration, I created a Percona Server for MySQL 5.6 instance on EC2 with the sakila sample database and an extra InnoDB table. I filled the table with junk data to make the total data size about 13.5 GB. Then I installed the latest percona-xtrabackup-24 (2.3 would also have worked) and the AWS CLI tools. I took a backup from the EC2 instance with this command, using gzip to create a compressed backup:

sudo xtrabackup --backup --stream=tar | gzip -c > /data/backups/xtrabackup.tar.gz

1	sudo xtrabackup --backup --stream=tar \| gzip -c > /data/backups/xtrabackup.tar.gz

Note that Amazon prepares the backup, so there’s no need to run xtrabackup --prepare yourself.

For comparison, I took a mysqldump backup as well:

mysqldump --all-databases --triggers --events --routines --master-data=1 --single-transaction | gzip -c > /data/backups/mysqldump.sql.gz

1	mysqldump --all-databases --triggers --events --routines --master-data=1 --single-transaction \| gzip -c > /data/backups/mysqldump.sql.gz

I could have used mydumper to make this process multi-threaded, but to reduce complexity I did not. I then uploaded the backup to an S3 bucket (setting up credentials beforehand):

sudo aws s3 cp /data/backups/xtrabackup.tar.gz s3://dankow/

1	sudo aws s3 cp /data/backups/xtrabackup.tar.gz s3://dankow/

After that, I navigated to Relational Database Service in the AWS Console, and instead of clicking Launch DB Instance, I clicked Restore from S3. After that, the process is almost identical to creating a normal RDS MySQL or Amazon Aurora MySQL instance, with the addition of this box on Step 2:

I chose a db.m4.xlarge instance with 1000 Provisioned IOPS for this test. After I configured all the other options, I clicked “Launch DB Instance” and waited for my backup to decompress, prepare and restore into a new RDS instance.

For time comparison, I imported the backup I took with mysqldump, ignoring all the expected errors about privileges because they don’t affect the tables that we’re really interested in:

gunzip -c /data/backups/mysqldump.sql.gz | mysql --defaults-file=rds.cnf --force

1	gunzip -c /data/backups/mysqldump.sql.gz \| mysql --defaults-file=rds.cnf --force

Replication

If you’re planning on migrating a non-RDS instance to RDS, you might want to make your new RDS instance an async replica of the source instance. If there is a network path between the two instances, this is simple. Use the binary log coordinates from the xtrabackup_binlog_info (RDS does not support master_auto_position with GTID replication), and use them as arguments to the RDS external replication stored procedures, like this:

CALL mysql.rds_set_external_master (  <br>"<host_name>",  <br> <host_port>,  <br> "<replication_user_name",  <br> "replication_password",  <br> "<mysql_binary_log_file_name>",  <br> mysql_binary_log_file_position,  <br> 0<br>);<br><br>CALL mysql.rds_start_replication;

1	CALL mysql.rds_set_external_master ( <br>"<host_name>", <br> <host_port>, <br> "<replication_user_name", <br> "replication_password", <br> "<mysql_binary_log_file_name>", <br> mysql_binary_log_file_position, <br> 0<br>);<br><br>CALL mysql.rds_start_replication;

Currently, there is no way to make this connection use SSL. If the source instance is not in the same VPC as the RDS instance, set up a VPN connection between the two networks in order to protect the replication traffic.

Time Comparison

The time to back up was close: 8 minutes for Percona XtraBackup, and 7.5 minutes for mysqldump. Add the time to copy the backup to S3 (37 seconds), and the two methods are almost identical. The difference comes with restore time. The mysqldump backup took 22.5 minutes to restore, and Amazon took 10 minutes and 50 seconds to create the RDS instance from the backup. Some part of that is the normal overhead of creating an RDS instance, which always takes a few minutes.

Although my test dataset was small (13.5 GB) compared to most production databases, it was large enough to show a significant difference between physical (Percona XtraBackup) and logical (mysqldump) backups. The XtraBackup method was about 60% faster than mysqldump. If your dataset is larger, you will see even more of a difference.

Conclusion

When you migrate to Amazon RDS using a physical backup, it can be much faster than using a logical backup — but it’s not the right option for every use case. If your InnoDB tablespaces have significant fragmentation, or if you’re not currently using innodb_file_per_table, you may want to perform a logical migration to fix those issues. If you normally create RDS instances programmatically, the AWS CLI does not currently support creating an RDS instance from a physical backup. Any corruption in the InnoDB files transfers over to the RDS instance if you use a physical backup, but a logical backup will fail and allow you to fix the corruption before it gets to RDS.

For many use cases, however, building an RDS instance from Percona XtraBackup is a convenient way to get your data into RDS MySQL or Aurora relatively quickly. In this one small-scale test, migrating using XtraBackup was 60% faster than using mysqldump.