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.
- 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.
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
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
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/
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
xtrabackup_binlog_info(RDS does not support
master_auto_positionwith GTID replication), and use them as arguments to the RDS external replication stored procedures, like this:
CALL mysql.rds_set_external_master (
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.
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.
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.