How do you switch between standard and serverless configurations in Amazon Aurora

Question

I am looking a this Amazon page - https://aws.amazon.com/rds/aurora/serverless/ and it has this quote:

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 have a few normal Aurora clusters and want to switch them to serverless. I have looked and looked and cannot find the "migrate with a few clicks" bit in the Amazon user interface. I made a new serverless cluster just fine and so I could do a stop, backup, and restore with a short outage - but If I can do this without an outage - that would be far superior.

So where are these "few clicks" - or perhaps you will tell me the "few clicks" means stop, backup, and restore. Either way I think a lot of folks could benefit from knowing what "few clicks" make this happen.

Answer 1

As a comment on @drchuck's approach - We've learned this the hard way that AWS Database Migration Service does a bad job at creating the schema in the target database. However - there's a simple workaround:

1) Run mysqldump --no-data to get the exact schema from the source database.

2) Execute the dump'd schema on the target database.

3) Within your DMS task, under target table preparation mode, choose "Truncate" instead of "Drop tables on target". (https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Tasks.Creating.html)

With this in place, DMS doesn't create the schema on the target side, and things work pretty well (all existing data is loaded, and then ongoing changes are sync'd in near-real-time).

We've used this approach for minimal downtime cutovers a few times.

Answer 2

It took more than a while to figure out those few clicks.

I'm here initially as I too could not find them and yes I saw the exact quote on the AWS page you indicated saying that yes you could.

First you take a snapshot and then you restore it. In the process of restoring it you can select a serverless instance. (At least under SOME conditions. I do not think that a 5.7.12 (just confirmed actually) can be restored to a serverless configuration).

I suspect that 5.7.12 will happen in due time.

Right now the magic bullet is to start with a 5.6.10a version, take a snapshot and then restore that to a serveless instance.

Answer 3

For what it's worth after the long time:

Apparently Amazon Aurora Serverless is only compatible with MySQL 5.6 - this explains why 5.7 snapshots cannot be recovered.

So the two options are

• downgrading the MySQL version to 5.6 first or
• dumping and importing the data (after I read the other answers, I'd go for the second option).

Further reading: https://aws.amazon.com/rds/aurora/serverless/?nc1=h_ls#How_to_Get_Started

Answer 4

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. (1) When I did the conversion using mysqldump and restore, with a short outage things were fine. (2) When I used AWS Database Migration Service it went pretty badly.

First, you have to get the binary log format as "ROW" and retention to 24 hours. That necessitated server restarts on my old clusters. Then when the data migration worked, I lost all my auto increments, then NULLness 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.

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.