How to Migrate from Amazon RDS Aurora or MySQL to Amazon Aurora Serverless
A Step by Step Guide…
A decision was made to migrate from Amazon RDS Aurora instance (or from Amazon MySQL) to Amazon Aurora Serverless. At first glance, the migration seemed simple and can be accomplished in a short time and without significant downtime. Based on Aurora Serverless FAQ, I did not think it would require manual steps to accomplish the migration.
The Aurora Serverless FAQ page (see a snippet below) presented two questions with answers. On the first question “Which versions of Amazon Aurora are supported for Aurora Serverless?”, in which the answer is Aurora with MySQL 5.6 compatibility (as of this writing). The current version of RDS MySQL compatible Aurora that I was using is “5.6.mysql_aurora.1.19.5” — major engine version is 5.6.
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.
But why migrating to Amazon Aurora Serverless? and what are the benefits?
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.
Steps that did not work…
Based on the FAQ mentioned above on how to proceed with the migration, here are the steps that I performed:
- Took a snapshot of the RDS Aurora Instance Cluster. This took a few minutes to create.
- Once the snapshot creation has completed, selected the snapshot and then selected “Restore Snapshot” from the “Actions” pull-down menu.
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.
4. In order to enable the Serverless selection, I had to try to select each Aurora (MySQL 5.6) version, and the only selection that enables Serverless selection was the “Aurora (MySQL 5.6) global_10a”.
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.
As a workaround, for now, leave the DB cluster parameter group empty and change it later as described above. When clicking on the restore button to restore a snapshot to a serverless database, we got the error shown below. It is basically stating that the engine version “5.6.mysql_aurora.1.19.5” that I am using is higher/newer than the selected version. Once the engine is upgraded to higher versions, there is no direct way to roll back to previous versions. However unless if you have a snapshot of RDS instance with the old version then you can restore that snapshot which is unfortunately not an option in my case.
So, as of now, there are only two viable options:
- Create a new serverless cluster with the desired version and use a native dump tool (for ex: mysqldump) to generate a logical backup for export and import data into the new cluster.
- Use AWS Database Migration Service (DMS) for both one-time data migration as well as for continuous data replication. AWS Database Migration Service will capture changes on the source database and apply them in a transactionally-consistent way to the target.
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.
The use of mysqldump tool
The mysqldump client utility performs logical backups, producing a set of SQL statements that can be executed to reproduce the original database object definitions and table data. It dumps one or more MySQL databases for backup or transfer to another SQL server. The mysqldump command can also generate output in CSV, other delimited text, or XML format.
1. Stop the running web server HTTPD (Apache server) so that no data is going to try to communicate to the DB:
$sudo service httpd stop
2. Create a new Aurora Serverless cluster. AWS provided a great step-by-step guide on how to create it.
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:
> mysqldump --skip-lock-tables -h [RDS endpoint] -u [username] -p [dbname] > [filename]-$(date +%F).sql
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:
> mysql -h [Aurora Serverless Cluster Endpoint]-u [username] -p [ databasename] < [filename]-$(date +%F).sql
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:
> CREATE USER ‘[dbuser]’@’%’ IDENTIFIED BY ‘[user_password]’;
> GRANT SELECT, INSERT, UPDATE, DELETE ON `[dbname]`.* TO ‘[dbuser]’@’%’
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:
- You will have the chance to review and understand your database security and how it is implemented.
- You will get to remove access that is no longer required.
8. Startup/enable HTTPD (using Apache) on your Web Server under EC2 instance
$sudo service httpd start
Congratulations! Your new serverless backend should be up and running.