a
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

Top CI/CD Tools to Use in App Development

Top CI/CD Tools to Use in App Development

Modern software development requires continuous maintenance over the course of its operational lifespan in the form of continuous integration (CI) and continuous deployment (CD). It is tedious work, but helps developers worry less about critical breakdowns. Automating this cycle provides an easier means by which rollbacks can occur in the case of a bad update while providing additional benefits such as security and compliance functionality.

Top Software as a Service Companies in 2024

Top Software as a Service Companies in 2024

Spending for public cloud usage continues to climb with every year. In 2023, nearly $600 billion was spent world-wide with a third of that being taken up by SaaS. By comparison, Infrastructure as a Service only takes up $150 billion and Platform as a Service makes up $139 billion. On average, companies use roughly 315 individual SaaS applications for their operations and are gradually increasing on a yearly basis. SaaS offers a level of cost efficiency that makes it an appealing option for consuming software.

AWS Graviton and Arm-architecture Processors

AWS Graviton and Arm-architecture Processors

AWS launched its new batch of Arm-based processors in 2018 with AWS Graviton. It is a series of server processors designed for Amazon EC2 virtual machines. The EC2 AI instances support web servers, caching fleets, distributed data centers, and containerized microservices. Arm architecture is gradually being rolled out to handle enterprise-grade utilities at scale. Graviton instances are popular for handling intense workloads in the cloud.