How to Migrate from Amazon RDS Aurora or MySQL to Amazon Aurora Serverless

A Step by Step Guide…

The answer to the second question, however, is what I was after as far as how to accomplish the migrate from my Aurora RDS instance to Amazon Aurora Serverless. As you can see from the response to the question…Easy enough….Not so fast!

In this blog, I’ll go through the migration steps and the challenges encountered and what other alternatives are available to perform a migration.

Aurora Serverless has many benefits in comparison to provisioned Aurora Cluster (RDS). Aurora Serverless provides an on-demand, auto-scaling, high-availability relational database that only charges you when it’s in use (i.e., the database will automatically start-up, shut down when not in use, and scale capacity up or down based on your application’s needs). It enables you to run your database in the cloud without provisioning and managing any database instances. All the maintenance, patching, backups, replication, and scaling are handled automatically for you.

While Amazon Aurora Serverless is an elegant and seemingly cost-effective database option to use in the AWS cloud, it is only recommended for infrequent, intermittent or unpredictable workloads. Note that consumers are billed on a per-second basis and when the DB engine is idle for some time, billing and the DB serverless instance is paused.

Based on the FAQ mentioned above on how to proceed with the migration, here are the steps that I performed:

3. A Restore DB UI. Note that the DB Engine Version has defaulted to “Aurora(MySQL 5.6) 1.19.5 (default)”, and that Serverless selection (radio button) is grayed out and cannot be selected.

5. Per the image provided below, note the empty drop-down for the DB cluster parameter group while restoring a snapshot. In Aurora serverless, while restoring a snapshot you cannot change the default parameter group associated to avoid any incompatible-parameters issue while creation.

However, on the console page, you can still see the DB cluster parameter option as a part of the bug in the console UI. When I contacted AWS support, they indicated that the AWS internal development team is aware of this bug and they are currently working on a fix.

So, as of now, there are only two viable options:

We have chosen the first option for our migration as the best alternative since we were OK with minimal downtime (1–2 hours) and due to a number of prerequisites and limitations that DMS currently present in which I will not expand on there as it beyond the scope of this article.

1. Stop the running web server HTTPD (Apache server) so that no data is going to try to communicate to the DB:

3. Dump the existing DB to a backup file using mysqldump to create a .sql file that we could import into an Aurora Serverless instance:

Note that mysqldump prompts for a password before it starts the backup process. Depending on the size of the database, it could take a while to complete. The option to skip-lock-tables is used here since we have disabled our application server and thus no need to lock the tables. When performing a backup of a database, the DB locks the tables by default in order to ensure that the data does not change while the backup is underway.

The database backup will be created in the directory the command is run and $(date +%F) adds a timestamp to the filename in a format (YYYY-MM-DD).

4. Restore the DB backup file to the newly created Aurora Serverless instance:

5. Disable access to the old Aurora RDS instance from the application EC2 instance by removing the old Aurora RDS cluster from the security group (SG) and create a new SG and attach it to the old Aurora RDS cluster with inbound left blank/default which means nothing is allowed into the RDS, preventing any inbound connection to the old DB.

6. Change the DNS CNAME of your DB to point at the newly created Aurora Serverless instance.

7. Create a DB user on the newly created Aurora Serverless instance. Note that to create a user that can connect from any host, use the ‘%’ wildcard as a host part:

Unless you need to provide this DB this user with full privileges — in which you may specify “ ALL PRIVILEGES”, I’d recommend that you apply the Principle of Least Privilege.

Note: While you could use mysqldump utility to dump database table that contains users/privileges/passwords, and then restore it to the new database and that would probably work, I strongly recommend that you don’t do this for several reasons. First, migrating users from different versions of DB can be a problem and it may block you from making any modification or changes to the new DB. This is because mysql.users table structure or schema differs (example, one version has 43 rows vs the other version with a 42 rows user table) between mysql server versions. Secondly, properly documenting all the access and setting it up again with GRANT statements is much more secure, because:

8. Startup/enable HTTPD (using Apache) on your Web Server under EC2 instance

Congratulations! Your new serverless backend should be up and running.

Related posts:

The global Dibutyl Fumarate market size is expected to expand at a CAGR of -13.88% during the forecast period, reaching USD 3.1 million by 2027 was valued at USD 7.6 million in 2021. 360 Market…