Switching database engines—for instance, going from a MySQL-compatible database to an Amazon Aurora MySQL DB cluster—and migrating data can be a long and confusing process. For the purpose of offering a little clarity, this article will go through and highlight various aspects of that process, specifically for the case mentioned above.
For those who aren’t familiar with Amazon Aurora or the Aurora DB cluster, here’s some information to catch you up.
In so few words, Amazon Aurora DB operates as a Database engine of its own as opposed to the Amazon Relational Database Service, which operates as a hosted database service with six common database engines to host from (Amazon Aurora, PostgreSQL, MySQL, MariaDB, Oracle, and Microsoft SQL Server).
Having been released in late-2014, Amazon Aurora has been utilized as an alternative relational database engine offering users high-end performance, accessibility, and throughput that greatly exceeds that of MySQL and PostgreSQL. With features like storage scalability and elasticity, Aurora employs a cloud model in its functions as opposed to the traditional framework seen in other database engines.
The Amazon Aurora DB cluster is composed of one or more DB instances (there are two types of DB instances: Primary DB instance and Aurora Replica) and a cluster volume, which commands and directs data for the instances. The cluster volume itself is a virtual database storage volume spanning a variety of Availability Zones, all containing copies of the cluster data.
A detailed comparison of the two could compose an entire article of its own (a few organizations have offered such articles), but the focus will now shift to explaining how users can easily migrate their MySQL-Compatible Database to Amazon Aurora MySQL.
Although Amazon offers a detailed description of this process, this article will allow users to get a quick understanding of the process and how it all works without having to pour through pages of information.
There are a couple different ways that users can migrate their data and a defining factor is what storage engine is being supported. If a user’s database supports tablespaces by InnoDB or MyISAM, then they have a couple options.
The user can create a dump of data and import it into their Amazon Aurora MySQL DB cluster. Because of the replay of commands to recreate the schema and data in from one engine to the other, this option is rather slow and tedious.
The other option for users is to copy source files from their MySQL database (5.5 or 5.6) into an Amazon S3 bucket and restore an Amazon Aurora DB cluster from those files. As opposed to waiting for the commands to replay from the original dump of data, this option allows Amazon Aurora MySQL to instantly use copied files as the primary data for the DB cluster.
With that being said, there are a few necessary conditions to be set in stone before users can even begin to copy their data into an S3 bucket and restore their files. As recommended by Amazon, users first need to Install Percona XtraBackup on their local servers (Amazon Aurora MySQL uses files backed up using Percona XtraBackup to restore a DB cluster).
Once users have downloaded Percona XtraBackup and backed up their files, they must give permission to Amazon RDS to access their list buckets and IAM roles for their AWS account, and to act on behalf of the user to create new IAM service roles.
After all of the files have been copied or restored to an Aurora DB cluster, users can successfully launch their new DB instance in Amazon Aurora MySQL.