Migrating to AWS Aurora MySQL Serverless

AWS Aurora Serverless is a MySQL and PostgreSQL compatible database that scales up and down on demand. If you have low database workloads with occasional spikes, Serverless could save you a bunch of time and money. I recently implemented a migration for a client realizing an 80% drop in AWS RDS database costs. The previous database had been scaled to handle high-water marks for occasional and variable spikes (feeds from external sources) when typical load was a fraction of that, making it a great candidate for Serverless.

Steps For Migration:

Export Data
Prepare Security Groups
Create Serverless Database
Connect to Aurora
Create Schema
Import Data

Export Data

This is a simplified case of just creating a mysqldump that can be passed around servers in AWS. This might not be the best approach if your database is very large, but it works for a lot of cases.

mysqldump -h dbHost -u mydb1-user -p mydb1 --no-tablespaces | gzip -9 -c > mydb1.sql.gz

Prepare Security Groups

Create Application-sg

Create a security group for the application that will access the database. The rules in this group are not super important because we will be telling the Aurora database to allow access to all servers with this security group. For the details of your application the rules may be different, but I just opened up inbound HTTPS to have something there and SSH to gain access later through an EC2.

Create Mysql-sg

Create a security group for the Aurora database cluster we are about to create giving access on port 3306 to any servers with the security group “Application-sg”. You will have to add an inbound rule searching for this group and add it to the Source section for Type “MYSQL/Aurora”.

Create Serverless Database(cluster)

Select Create Database under the RDS service in the console.

Select MySQL compatibility. Note- Serverless Aurora limits you to a very small number of MySQL versions, so this might be a limitation for your project. Under credentials make sure you enter a master database password for the admin user and save it for later to create new schemas and access.

Set max and minimum capacity limits.

After the database is created and the status is “Available” you can grab the endpoint to connect to through a mysql client.

Connect to Aurora

Create EC2 Instance with “Application-sg” security group assigned to it.

SSH in to the EC2 with the key-pair you started it with. You will need the admin password you set when you created your Aurora database and the cluster endpoint from the console.

# If mysql client is not installed install it
sudo yum update
sudo yum install mysql

# Test access
mysql -h my-serveless.cluster-coopfg.us-west-2.rds.amazonaws.com -u admin -p

Create Schema

Example creating a new database, “mydb1”, with a user “mydb1-user” with the password “mypass”.

mysql -h my-serveless.cluster-coopfg.us-west-2.rds.amazonaws.com -u admin -p

SQL to create database and user.

create database mydb1;

CREATE USER 'mydb1-user'@'%' IDENTIFIED BY 'mypass';

GRANT ALL PRIVILEGES ON mydb1 . * TO 'mydb1-user'@'%';

Import Data

You will need to upload the gzipped database dump to the EC2 to import it into the new database.

gzip -dc < mydb1.sql.gz | mysql -h my-serveless.cluster-coopfg.us-west-2.rds.amazonaws.com -u mydb1-user -p mydb1