Running a Startup on AWS? Get Funding With AWS JumpStart. Click Here to Learn More

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

Amazon Web Services – CodeCatalyst

Amazon Web Services – CodeCatalyst

When a development team is building out an application, it helps to have access to the same resources, have the tools for planning and testing, and to have access to the application all in one place. CodeCatalyst comes with a slew of continuous integration/continuous development (CI/CD) tools and can leverage other AWS services and be connected to other AWS projects on an account. As a collaborative tool, it is easy to introduce new members into the project and to log all activity or all tests from a single dashboard. It’s a complete package of all the tools needed to securely work on every step of an application’s lifecycle.

The Definitive Guide to AWS Pricing

The Definitive Guide to AWS Pricing

Perhaps the biggest issue with AWS that its competitors edge out on is the confusing pricing model. It does promise the capacity to help users save significantly on funds that otherwise by avoiding spending on unnecessary resources, but getting to that point isn’t always clear. We will be covering in greater detail how this works.

Using Stable-Diffusion and Dreambooth to create personalized AI art – Part 2 of 3

Using Stable-Diffusion and Dreambooth to create personalized AI art – Part 2 of 3

DreamBooth is a deep learning generation model used to fine-tune existing text-to-image models, developed by researchers from Google Research and Boston University in 2022. Originally developed using Google's own Imagen text-to-image model, DreamBooth implementations can be applied to other text-to-image models, where it can allow the model to generate more fine-tuned and personalised outputs after training on three to five images of a subject.

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.