Migrating existing database to Amazon RDS

Question

How can I import existing MySQL database into Amazon RDS?

Answer 1

Simplest example:

# export local db to sql file:
mysqldump -uroot -p —-databases qwe_db > qwe_db.sql

# Now you can edit qwe_db.sql file and change db name at top if you want

# import sql file to AWS RDS:
mysql --host=proddb.cfrnxxxxxxx.eu-central-1.rds.amazonaws.com --port=3306 --user=someuser -p qwe_db < qwe_db.sql

Answer 2

I found this page on the AWS docs which explains how to use mysqldump and pipe it into an RDS instance.

Here's their example code (use in command line/shell/ssh): mysqldump acme | mysql --host=hostname --user=username --password acme

where acme is the database you're migrating over, and hostname/username are those from your RDS instance.

You can connect to RDS as if it were a regular mysql server, just make sure to add your EC2 IPs to your security groups per this forum posting.

I had to include the password for the local mysqldump, so my command ended up looking more like this: mysqldump --password=local_mysql_pass acme | mysql --host=hostname --user=username --password acme

FWIW, I just completed moving my databases over. I used this reference for mysql commands like creating users and granting permissions.

Hope this helps!