How To Carry Out A Percona MySql 5.5 Migration With No Downtime
Gabriele Lomusico, Cloud Systems Developer at Cloudreach, explains how he completed a Percona MySql 5.5 migration to AWS with no downtime.
The customer asked us to move seven on-premises databases to AWS in a short period of time (about ten days) without any downtime.
Each database was configured in replication mode (master/slave) with a Percona MySql version 5.5. The databases were installed on physical machines with no test environment, only production. Each of the database would be about 500GB in size, containing lots of stored procedures and triggers.
The migration strategy that was adopted involved creating a daisy chain of slave on AWS, with two slave instances that would receive data from their master on premise. Binary logs were used to catch up with the modifications happening after the launch of the database dump.
Once the replication was configured, the first slave on AWS (SLAVE 2 in the picture) was promoted to being the new master and the master on premises was cut out and no longer used by the business applications.
Even though we were clear on the strategy to adopt, we had to quickly decide if the target AWS database would be a self-managed RDS instance or an EC2 instance and this is what I will present in the next paragraph.
AWS infrastructure: RDS or EC2 db instances
Moving to RDS
Moving to a self-managed database is an attractive solution because a lot of time-consuming actions are handled directly by Amazon (like OS and software patches). On the other hand, going to a self-managed solution, like RDS, implies some restrictions. So, you really have to check the Amazon RDS limitations (here and here) before deciding to move to RDS.
In our case, RDS limitations were not an obstacle, as opposed to other restrictions concerning the actual migration procedure to AWS.
When performing a homogeneous migration (MySql to MySql), Amazon recommends using native tools instead of other migration services like DMS, which is mostly recommended when migrating from engine A to engine B. Moreover, the DMS feature CDC (change data capture) is not supported with MySql 5.5.
Therefore, the only options at our disposal to migrate to RDS consisted in the following export/import tools:
- Percona xtrabackup
The main difference between these two tools is that xtrabackup makes a physical dump of the database while mysqldump makes a logical dump. As a consequence, xtrabackup results in a faster and raw export/import process, while mysqldump is much slower but produces a cleaner output table in terms of memory.
The tool xtrabackup is not supported in the case of MySql version 5.5 by Amazon RDS import procedure, only MySql 5.6 is supported at the time this article was written.
The only remaining option was to use the mysql native tool, mysqldump. This procedure involves creating a dump of the master on premises and copying it to an EC2 instance on AWS. From this instance, an import operation is launched into RDS.
The procedure is broken down into three diagrams below:
Phase #1: Create a dump file using native tool mysqldump, compress it and copy it to an EC2 instance.
Phase #2: Start import operation from the EC2 instance.
As said before, mysqldump performs a logical dump of the database. This procedure is much slower then xtrabackup. In our case, considering the size of the database, the import operation could take up to 15-20 hours. Others time-consuming operations that must be performed include: the export time, the transfer time to AWS and the compression and decompression of the dump. All these delays can easily add up to more than 30 hours for one single database.
Moreover, you might have surprises during the import operation into Amazon RDS, where some of the statements of the dump file are not compatible with the restrictions of Amazon RDS.
Another point to take into consideration is that all stored procedures, triggers and events must be exported and imported manually into RDS, as indicated by the AWS documentation. Also, with RDS being a self-managed database, you cannot allow users to have all privileges. You will have to reconfigure some of the users to make them compliant with RDS.
The customer didn’t have any test environment for the database, thus it was impossible to perform (export/import) tests without impacting the production environment. Because of the production impact and the time constraints that were imposed to us, we couldn’t test the import operation of the dump into AWS RDS.
Whether you use xtrabackup or mysqldump at the end of the procedure, if the database is live during the export/import migration, you will have to catch up the data to the master latest state using binary logs.
Considering all the analysis above we decided to perform the migration onto EC2 instances.
EC2 instances using xtrabackup tool
By migrating to EC2 instances we were able to use the faster tool xtrabackup. This tool couldn’t be used to move to RDS because the AWS procedure to import the dump requires MySql version 5.6 and we had MySql 5.5 on premises instead.
By using the xtrabackup tool, the import operation took about 30 minutes instead of 15-20 hours of mysqldump.
It is important to test data integrity before cutting out the master on premises. For this purpose, you can use the Percona tool: pt-table-checksum.
Can we go to RDS eventually?
The answer is definitely yes. We advised the customer to first upgrade his database instances to MySql 5.6 and then enable the use of xtrabackup for the import operation into RDS.