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.

Related Articles

AWS Jumpstart Program

AWS Jumpstart Program

Do you have a startup that’s looking to build a mobile application or SaaS Platform on Amazon Web Services? Are you thinking about using AWS Services for Analytics, IOT, Machine Learning, Containers or Non-Relational Database? If the answer to both of these questions is yes then work with AllCode. We’re an AWS Consulting Partner who specializes in building out these types of applications. More importantly, AllCode is one of the select few partners that is a member of the AWS Jumpstart Program.

AWS Partner Service Tiers

AWS Partner Service Tiers

This is a status to indicate an organization has proven technical expertise that is directly compatible with AWS tools. It’s a great way to give an organization added value and there are plenty of advantages from advancing through the tiers, from funding to additional resources to funding and additional materials for marketing. The jump from one tier to another is dependent on how many certified individuals are in the organization and their relative experience working with Amazon Services.

Which AWS Certification Should You Get?

Which AWS Certification Should You Get?

Amazon Web Services cloud technology is a continuously growing field with plenty of opportunities on offer. While the inner workings of the cloud is not knowledge that can be easily picked up and utilized, the AWS cloud is not completely esoteric either. Amazon provides several different avenues for becoming certified in how to work with AWS with study material and exams to pursue as a means of further expanding potential business opportunities.

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.