Are you getting the most out of your AWS investment? Get your free AWS Well-Architected Assessment.

2021 Fillmore Street #1128

}

24/7 solutions

Top AWS Services

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

AWS Think Big with Small Business Program’s Competitive Edge

AWS Think Big with Small Business Program’s Competitive Edge

The Amazon Cloud can help alleviate most issues involving transitioning the cloud. Businesses of any scope can hope to build solutions that are scalable and adaptable to their industry of work. Smaller or minority-owned businesses may still struggle to stand out among bigger companies or make an AWS environment as efficient as possible with fewer funds to spend. To alleviate these burdens, Amazon has the Think Big With Small Business program available through their Public Sector partnership program.

AWS Think Big for Small Business, Data Analytics, and Business Intelligence

AWS Think Big for Small Business, Data Analytics, and Business Intelligence

The AWS Think Big for Small Business Program is an outreach program designed to provide small and/or minority-owned public sector organizations support in the form of business intelligence, technical expertise, and marketing strategies. With cloud-based solutions and experience, various public institutions globally have seen continued success in government, educational, and nonprofit sectors. While the funding provided can help significantly to meet business objectives, the expertise on navigating the cloud and how to extend outwards towards customers is just as critical.

Best Practices for Using AWS Systems Manager

Best Practices for Using AWS Systems Manager

As newer users adopt the AWS cloud, it can be difficult to watch for issues and resolve them as needed. AWS Systems Manager grants better visibility over the AWS environment by clustering resources, providing legible and usable data based on performance, and actions to take that abide by AWS compliance requirements and best practices. This service provides everything management needs to evaluate the cloud and ensure continued functionality.