This post's content
We recently migrated some of EverSQL's workload from AWS RDS MySQL to Aurora MySQL. In this post I'll share several options I explored, and the issues I ran into when using each of them, and specifically AWS DMS, so hopefully you could avoid similar issues when going through the migration process.
Potential solutions for migrating RDS MySQL to Aurora MySQL
Naturally, I first started with AWS's documentation, which states you can migrate RDS MySQL to Aurora MySQL by creating an Aurora read replicate and promoting it after replication is done. Unfortunately, that solution wasn't viable in our case, as the RDS instance was already upgraded to MySQL 8.0.28, while Aurora MySQL only supports versions up to MySQL 8.0.23, and therefore we ran into a blocking error within the AWS console.
The manual path was also an option I considered. As I wanted to avoid downtime for our customers though, I was looking for a solution which will not only export the existing data, but also replicate the new data on an ongoing basis, so I could switch when the time is right. Using mysqldump/mydumper + setting up MySQL replication seems rather cumbersome these days, so I ruled out that option and kept it as a last resort.
So the next path I chose was to use AWS DMS, which is AWS's data migration service, allowing one to migrate between different databases. I created the endpoints, set up the instance for migration, and was ready to go. Then, I ran into several issues I'll share with you, so hopefully you'll avoid them.
My expectations from the migration using AWS DMS
In my mind, the migration had 3 steps that I expected DMS to handle:
- Migrating the schema, so it will be exactly the same in Aurora as it was in MySQL (same indexes, same structure, etc.).
- Migrating the existing data.
- Replicating ongoing changes, until I decide to switch to the new instance.
Pitfalls when using AWS DMS to migrate MySQL to Aurora
AWS's documentation states that: "You can migrate schema and data from MySQL to an Aurora MySQL DB cluster without using AWS SCT". AWS SCT is the AWS Schema Conversion Tool, which allows to migrate different schemas from a source database to a destination, with different database types and engines. So this statement in the docs reassured me that I can just start the migration, and I don't need any external tools (such as AWS SCT) to migrate the schema.
So I started the migration, and a couple of days later when the migration completed, I did some sanity checks that the data was transferred ok, and was ready for the switch. Once I switched, I noticed queries started hanging for minutes without returning results, so I started to look the reason.
I found this information on Github, that states: "The behavior you are observing is how DMS is designed. AWS DMS takes a minimalist approach and creates only those objects required to efficiently migrate the data. In other words, AWS DMS creates tables, primary keys, and in some cases unique indexes, but doesn't create any other objects that are not required to efficiently migrate the data from the source.".
So what DMS fell short to do:
- Secondary indexes were not migrated, so queries became a lot slower and the entire database was practically unusable.
- Relevant columns were not set with their Default values, which broke some of the application flows.
- Relevant columns were not set with their Auto-increment settings, breaking some of the application flows.
This is the point I stopped looking for difference in the schema, but you probably got the point. AWS DMS took the minimal approach and created the minimal schema required to hold the data, but that schema was unusable.
Also, in case I wanted to complete the missing parts, that would have taken days or weeks of running Online Schema Changes, to modify the columns to the correct settings, as we hold large amounts of data in those tables.
How to correctly migrate the schema from MySQL to Aurora?
To make sure you migrate the schema as-is, I recommend exporting the original schema (for example, using MySQL workbench) from the source database, and importing it before the migration starts to the target database.
Once you migrated the schema, configure AWS DMS to Do Nothing in the Target table preparation mode setting, when creating the database migration task. That setting will tell DMS not to drop the existing tables (empty tables with the correct schema), and just insert the data into them.
This process will probably be slower, as the secondary indexes are already created, but it will ensure that the schema is migrated properly without any changes that will break your application.
Good to know, before we have a plan to migrate the same scenario.
How about using the AWS Schema Conversion Tool ? Will it create the structure by keeping the secondary indexes ?
To be honest I didn't get a chance to try out the schema conversion tool for this purpose, as it was easy enough to just export the schema structure from MySQL and import it to Aurora using MySQL workbench (it took only a couple of minutes).