Recently I moved all my Amazon-hosted servers for my www.tsugi.org project from Amazon server-based Aurora to Amazon Serverless Aurora. I was experiencing the classic problem where 98% of the time it vastly over paying for my servers and then when I hit a peak (like after I spoke at the Instructure conference), things would get slow. Then I would upgrade my hardware and pay even more when things were slower. So serverless is *perfect* for me.
I made a serverless cluster – in Aurora serverless you just make a cluster and do not make instances. I was looking a this Amazon page – https://aws.amazon.com/rds/aurora/serverless/ and it has said:
You pay on a per-second basis for the database capacity you use when the database is active, and migrate between standard and serverless configurations with a few clicks in the AWS Management Console.
I could not find the “migrate with a few clicks” bit in the Amazon user interface. So I asked a question on Stack Overflow .
When I did not get an answer in a few days, I did the conversion two ways with different results so I figured I would share my results here. I would still love to hear a better approach.
- When I did the conversion using mysqldump and restore, with a short outage things were fine.
- When I used AWS Database Migration Service it went pretty badly.
First, you have to get the binary log format as “ROW” and binlog retention to 24 hours. That necessitated server restarts on my old clusters.
Then when I got the data migration working, the new database was badly broken. I lost all my auto increments, then NULL settings in my columns, the UNIQUE clauses and foreign keys in the new tables.
Literally the only thing that migrated correctly was that the actual data and PRIMARY KEY indications. Also, I would recommend migrating one database at a time (i.e. schema) and don’t try to migrate the mysql internal schemas. I said “migrate everything” and the migration tool tried to migrate the MySQL stuff – sheesh – you would think it would know better.
The one thing the AWS Database Migration Service did that was really cool was the migrate and monitor (made possible by the binary logging on the rows). You could watch it moving rows.
So the lesson is use mysql dump and restore when moving from Aurors server based to serverless.