From On-Prem MySQL to AWS RDS: A Practical Migration Guide

Introduction

When migrating applications to the cloud, databases are often the most critical and sensitive components. A poorly planned database migration can lead to downtime, data loss, or performance issues.

In this hands-on guide, we will migrate a MySQL database from a Linux server to Amazon Aurora (MySQL-Compatible) using AWS RDS. This walkthrough is ideal for DevOps engineers, cloud engineers, and anyone learning AWS database migration.

What You’ll Learn

• How to create an Amazon Aurora MySQL database

• How to export a MySQL database using mysqldump

• How to import data into AWS RDS

• How to configure security groups safely

• How to validate the migration

Architecture Overview

Step 1: Create an Amazon Aurora MySQL Database

1. Open AWS RDS Console

• Go to AWS Console → RDS

• Click Create database

• Choose Standard create

  
  

2. Engine Configuration

• Engine type: Amazon Aurora

• Edition: Amazon Aurora MySQL-Compatible

  
  

3. Database Settings

• Master username: admin

• Password: (set and remember it)

  
  

4. Instance Configuration

• Instance class: db.t3.small

  
  

5. Availability

• Select Don’t create an Aurora Replica

6. Connectivity

• Public access: Yes

  
  

7. Create Database

Click Create database and wait until the status becomes Available.

Step 2: Export MySQL Database from Linux Server

Login to your Linux server:

ssh siddhesh@<SERVER_IP>

Dump the database:

[root@siddhesh ~]# sudo mysqldump -u root \
--databases builddevops_prod \
--single-transaction \
--order-by-primary > builddevops-dump.sql

Verify dump file:

cat builddevops-dump.sql

Step 3: Upload Dump File to AWS CloudShell

1. Open AWS CloudShell

2. Create a new file:

[root@siddhesh ~]# vim builddevops-dump.sql

3. Paste the dump content

4. Ensure the first line is correct

5. Save and exit:

Step 4: Allow CloudShell Access to RDS

Get CloudShell public IP:

[root@siddhesh ~]# curl -s checkip.dyndns.org

Copy the IP address.

Update RDS Security Group

1. Go to RDS → Databases

2. Click your database

3. Open VPC Security Group

4. Click Inbound Rules → Edit

5. Add rule:

Type	Source
All traffic	YOUR_IP/32

Save the rule.

Step 5: Import Database into RDS

Get the RDS Endpoint from AWS console.

Run:

[root@siddhesh ~]# cat builddevops-dump.sql | \
mysql --host=<RDS_ENDPOINT> -u admin -p

Enter the admin password when prompted.

Step 6: Create Application User

[root@siddhesh ~]# mysql --host=<RDS_ENDPOINT> -u admin -p -e \
'CREATE USER "builddevops" IDENTIFIED WITH mysql_native_password BY "Bui1d@123";
 GRANT SELECT ON builddevops_prod.* TO "builddevops";'

Step 7: Verify Database Access

[root@siddhesh ~]# mysql --host=<RDS_ENDPOINT> -u builddevops -p \
-e "SELECT * FROM builddevops_prod.items;"

If records appear, the migration is successful 🎉

Step 8: Secure Your Database

⚠️ Important Step

• Go back to EC2 → Security Groups

• Remove the CloudShell IP rule

• Save changes

  
  

This prevents unauthorized access to your database.

Final Result

• MySQL database successfully migrated

• Data verified on Amazon Aurora

• Secure access configured

• Production-ready setup

Best Practices

• Always use --single-transaction for live databases

• Never expose RDS publicly after migration

• Enable automated backups

• Use AWS Secrets Manager for credentials

• Enable CloudWatch monitoring

  
  

Conclusion

Migrating MySQL to Amazon Aurora is straightforward when done correctly. With proper security, backups, and validation, you can move production workloads safely to AWS and benefit from scalability, reliability, and managed operations.