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 might ask why you want to do this. You get more control with EC2 than with RDS. The reality is that the EC2 instance ran out of RAM, and I didn’t want to build a new box.

However, let’s dive deeper to understand the broader context of when to use Amazon RDS versus other services like DynamoDB for a given workload.

Analyzing When to Use RDS

1. Structured Data with Relational Needs: If your application requires complex queries, transaction management, or joins, Amazon RDS is typically the better choice. It supports SQL databases, making it ideal for relational data.
2. Automated Management: For those who prefer reduced operational overhead, RDS offers automated backups, patching, and scaling. This allows you to focus more on your application rather than database maintenance.
3. Consistent Performance: When your workload demands consistent, predictable performance and high IOPS (Input/Output Operations Per Second), RDS can be fine-tuned to meet these requirements.

When to Consider DynamoDB

1. High Scalability: DynamoDB shines in scenarios where you need to handle large volumes of data with high throughput and low latency. It’s designed for applications that require horizontal scaling.
2. Flexible Data Models: If your data isn’t strictly relational and can benefit from a NoSQL model, DynamoDB allows for more flexible, schema-less data models.
3. Serverless Architecture: For those looking to leverage a serverless architecture, DynamoDB integrates seamlessly, eliminating the need for server provisioning and maintenance.

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.

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.

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

4. On the EC2 instance, do the following:

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