Migrate Your Local MySQL Database to AWS RDS

In this post, I'll share detailed steps to migrate data in your local MySQL database to newly created instances on AWS RDS with MySQL.

Background

I worked on a full-stack project in 2023 where I manually handled the production deployments of my database and backend to AWS. Earlier this year, I shut down all infrastructure because I was accumulating too much monthly costs with the database (no thanks to me overprovisioning).

Now, fast forward to this month and I still had lots of AWS credits (perks of being a Community Builder) due to expire by the end of the year. I tried to get my project back up only to realise it would not be as easy as I thought.

First, I had not properly created a snapshot that I could use to restore the production data and kept getting access errors. I was also reminded of how excruciatingly manual the entire deployment had been, and how I did not document any of the steps I took (neither did I remember them).

That was a lesson learnt, so this time, I am documenting the entire process. In line with one of the sayings in an Udemy course I never finished:

'You have to know to perform a task manually before proceeding to automate it'.

Hence, I'll first share the steps I took to carry out the migration manually, and in a following post, I'll write scripts to provision the infrastructure with Terraform and also dump the local data into the remote db after successful launch.

This is also part of my self-assigned ongoing project to learn more about CI/CD by building a pipeline to automate the entire deployment of the project.

A) Manual Steps

1. Create a new DB instance on Amazon RDS

This time, I was more intentional and practical with the configuration. Although it was a 'production deployment', I still opted for what's likely the cheapest running costs since it's not an actual live product (this also influenced some security options I chose not to add).

I have summarised the options I chose in the 'Create Database' wizard below:

AWS Region: eu-west-2
Database creation method: Standard Create
Engine type/edition/version: MySQL/MySQL Community/MySQL 8.0.35
Use case template: Free Tier
Credential settings: define these as you'd prefer
DB instance class: db.t3.micro
Storage: gp2, 20GB, auto-scaling enabled up to 100GB
Connectivity: 
    - define these for your specific use-case; I chose not to use an EC2
    - Public access: Yes, because I wanted to connect to the database locally via MySQL Workbench
Database authentication: Password auth
Additional configuration:
    - I created one database from here; but you can also leave it blank and create one when you get access via Workbench
Others: 
    - use default options or modify for your use-case
Deletion protection:
    - enabled (to dissuade myself from deleting easily like the last time)

Estimated monthly costs:

2. Connect to the RDS instance locally

After the instance has successfully launched, use mysql client to connect to the instance. This assumes you have your MySQL server installed and running.

Getting started with MySQL
MySQL client

You will also need the instance endpoint, and the username and password you defined in the credentials settings, to run this command:

mysql -h sample_endpoint.rds.amazonaws.com -u username_sample -p

Note: Your instance would have been created with the correct inbound and outbound rules for the selected VPC security groups.

However, if you run into errors connecting, confirm that your IP address is included in the allowed source for the inbound rules. I encountered a similar issue when I created the instance in a specific location with its IP address, and when I tried to connect with a different IP address in another location, the connection timed out.

[Optional] Create your database
If you skipped the Additional configuration step during the instance creation, you can create one at this step:

CREATE DATABASE sample_db;

3. Import data from your local database

To do this, you first need to use the mysqldump command to export the schema and data in your local db to a .sql dump file:

mysqldump -u root -p local_db_name > sample_dump.sql

Note: If you run into errors while trying to export, check out my question on Stack Overflow and also an answer that could resolve some possible issues.

Afterwards, use the mysql command to import the dump file to your RDS instance:

mysql -h sample_endpoint.rds.amazonaws.com -u username_sample -p 

sample_db < sample_dump.sql

1. [Optional] Connect to your RDS instance via MySQL Workbench

Confirm that you are able to connect to the instance without issues, and check the schema and data inside the database to confirm that they were also imported correctly.

Unable to connect?

• Ensure you have correctly configured the public access settings, or
• Use an EC2 instance to securely connect to the database and run your sql commands through the instance. Ensure that the EC2 instance is also inside the VPC where the RDS instance is located.

Next up will be:

• deploying to App Runner with the required database credentials from this post (and necessary infrastructure access),
• scripting with Terraform instead, and
• creating a CI/CD pipeline to update future backend and frontend updates.