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.sqls3cmd 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
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 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.
