Those looking for a solution to alleviate performance and resource issues with their data tier should consider Amazon RDS’ Aurora a viable option. While the benefits laid out from the AWS marketing team are straightforward and have general appeal, I’d like to take some time to explain just how *easy* it is to transition from existing on-prem or hosted RDS to Aurora.
What limitations there are in compatibility between MySQL and Aurora are made for the sake of performance. For example, myISAM tables are not supported by Aurora, which means that some datatypes, not yet supported by InnoDB, will be unable to carry over without undergoing some sort of conversion.
Looking into the future, Innodb with MySQL 5.7 includes support for spatial datatypes that aren’t currently supported by 5.6. This problem may very well resolve itself with a bit of time.
While AWS does provide an EC2 image pre-built for migrating databases, I recommend only utilizing this tool if undergoing conversions from one type of relational database to another, such as MSSQL to MySQL. Otherwise, traditional tools like mysqldump are more than sufficient to accomplish the task and are more in line with how things are done in RDS.
If coming from MySQL RDS, migrating is as simple as creating a read replica of the existing database, stopping replication on the replica, snapshotting this replica and migrating the resulting snapshot directly into Aurora.
If the MySQL database staged for transition is on-prem, a simple shell one-liner is often enough to move everything over:
Looking at the above we can see that the migration does not include any of our stored procedures, triggers, functions or events. Unfortunately, these need to be created manually. Fortunately, this is not a daunting task.
Below, we have a line to export these objects (excluding views) to a .sql that can then be imported to Aurora after slight modifications:
This basically takes all of our objects, strips the DEFINER which specifies what user that object belongs to, thus making it usable by RDS which has several restrictions on users (mostly in regards to SUPER privileges).
So once we have our data migrated, how do we get our new database up to the point in time where our production database is now?
Thankfully, Aurora has all of the same stored procedures used for replication as MySQL RDS. Thus catching up our database is as simple as making it a replica of our existing database utilizing two calls to stored procedures:
This works for both on-premises databases, as well as instances already in RDS.
Once your data tier is in Aurora, it’s time to enjoy the benefits. A few that I find personally pleasing:
Snapshots are faster
The time to create a snapshot, or to restore a snapshot, is orders of magnitude quicker than MySQL RDS. Things that previously would’ve taken all day now occur in a few hours, and those that would’ve taken a few hours happen in a few minutes.
Aurora read replicas are robust
Typically replication starts to bottleneck when write IOPS get into the neighborhood of ~2k IOPS on traditional MySQL due to the single-threaded nature of the replicas. Aurora replicas do not suffer from these limitations and perform well up to much higher IOPS.
Snapshotting and backups are automated
All that time spent creating backups, scripting a backup solution, getting frustrated with SMB and NFS and shares used for storing dumps can now be spent doing more important things.
If the worry of a painful migration was holding you back from migrating to Aurora and enjoying its numerous benefits (performance, scalability, etc.), hopefully my experience helps illustrate how easy the transition to Amazon’s Aurora is. If it wasn’t able to alleviate your fears, let’s chat or comment below.