2021 Fillmore Street #1128

}

24/7 solutions

Top 10 Cloud Providers

How to migrate your EC2 MySQL Instance to RDS

The other day I had to migrate a couple of databases from an EC2 MySQL instance to RDS. I couldn’t find a decent example of how to do this so I wrote this up. Some of you might ask why do you want to do this? You get more control with EC2 than RDS. The reality is the EC2 instance ran out of RAM, and I didn’t want to build out a new box.
Top AWS Services

The other day I had to migrate a couple of databases from an EC2 MySQL instance to RDS. I couldn’t find a decent example of how to do this so I wrote this up.
Some of you might ask why do you want to do this? You get more control with EC2 than RDS. The reality is the EC2 instance ran out of RAM, and I didn’t want to build out a new box.

1. Start by dumping your db

using mysqldump. Yes, if you’re db is too large, this is not the best way. Use Percona. For this particular example, I just kept it simple.

1
mysqldump -uUSER -pPASSWORD DBNAME --single-transaction> DBNAME.sql

Make sure that you use –single-transaction. This will set the isolation mode to Repeateable Read. This will also dump the current state of the db without locking everything, which is crucial.
The default for mysqldump is to dump the stored procs and triggers. If you’re importing these into an RDS instance, then you will either have to rip out the DDL for the store procs and triggers from the SQL file or build your RDS instance with a different set of init parameters. Tweaking the init parameters will be a separate blog post, but if you’re trying to do this today go here to learn about rds-modify-db-instance.

Right now, we’re also dumping to the hard drive that is on the EC2  instance. This can be dangerous.
If you want to move this backup to S3, then I typically use S3Cmd.

1
2
tar -cf db_backup.tar DBNAME.sql
s3cmd put db_backup.tar s3://bucket

2. After your backup completes, go out to your new RDS instance. Create the appropriate USER and DATABASE. Ensure that the new USER can access the RDS instance remotely

3. Configure the Security Group for your new RDS instance. Your User Security Group configuration for your EC2 instance will need to have access to RDS. After it’s configured correctly, you will see this
RDS Security Groups (4-3-13)
4. On the EC2 instance, do the following:

1
mysql -uSUSER -pRDSPASSWORD -hRDSINSTANCE DBNAME < DBNAME.sql

5. If you don’t encounter any errors, then your DDL and Data should be in your DBNAME.

Joel Garcia
Joel Garcia

Joel Garcia has been building AllCode since 2015. He’s an innovative, hands-on executive with a proven record of designing, developing, and operating Software-as-a-Service (SaaS), mobile, and desktop solutions. Joel has expertise in HealthTech, VoIP, and cloud-based solutions. Joel has experience scaling multiple start-ups for successful exits to IMS Health and Golden Gate Capital, as well as working at mature, industry-leading software companies. He’s held executive engineering positions in San Francisco at TidalWave, LittleCast, Self Health Network, LiveVox acquired by Golden Gate Capital, and Med-Vantage acquired by IMS Health.

Related Articles

What is Tigera?

What is Tigera?

An AWS Advanced Technology Partner, Tigera delivers Calico and Calico Enterprise for security and networking on EKS, both of which are AWS Containers Competency certified.

AllCode AWS Analytics Services

AllCode AWS Analytics Services

AWS Analytics ServicesAWS AthenaS3-based query data using SQLAWS CloudsearchManaged Search ServiceAWS ElasticsearchStreamline Elasticsearch...

Free AWS Services List

Download this FREE list of all 200+ AWS services and ensure that you're using the optimal services for your use case to enhance efficiency and save money!

Free AWS Business Continuity Plan Template

Make sure you have the proper business continuity plan explicitly for you AWS infrastructure. our professionals built this template using AWS best practices so you can ensure it's built to scale! 

Free Cloud Migration Checklist

Without the proper cloud migration strategy, you risk losing time and money. Ensure that your migration process is running smoothly with our FREE cloud migration checklist.

Free AWS Services List

You might be optimizing with the wrong AWS services. Download this FREE list of all 200+ AWS services and ensure that you're using the optimal services for your use case to enhance efficiency and save money!

Download your FREE AWS Business Continuity Plan Template
Download Free 200+ AWS Services Checklist
Download our 10-Step Cloud Migration ChecklistYou'll get direct access to our full-length guide on Google Docs. From here, you will be able to make a copy, download the content, and share it with your team.