How can I im开发者_高级运维port existing MySQL database into Amazon RDS?

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!

There are two ways to import data :

mysqldump : If you data size is less than 1GB, you can directly make use of mysqldump command and import your data to RDS.
mysqlimport : If your data size is more than 1GB or in any other format, you can compress the data into flat files and upload the data using sqlimport command.

I'm a big fan of the SqlYog tool. It lets you connect to your source and target databases and sync schema and/or data. I've also used SQLWave, but switched to SqlYog. Been so long since I made the switch that I can't remember exactly why I switched. Anyway, that's my two cents. I know some will object to my suggestion of Windows GUI tools for MySQL. I actually like the SqlYog product so much that I run it from Wine (works flawlessly from Wine on Ubuntu for me). This blog might be helpful.

Migrating existing database to Amazon RDS

A quick summary of a GoSquared Engineering post:

Configuration + Booting

Select a maintenance window and backup window when the instance will be at lowest load
Choose Multi-AZ or not (highly recommended for auto-failover and maintenance)
Boot your RDS instance
Configure security groups so your apps etc can access the new instance

Data migration + preparation

Enable binlogging if you haven't already
Run mysqldump --single-transaction --master-data=2 -C -q dbname -u username -p > backup.sql on the old instance to take a dump of the current data
Run mysql -u username -p -h RDS_endpoint DB_name < backup.sql to import the data into your RDS instance (this may take a while depending on your DB size)
In the meantime, your current production instance is still serving queries - this is where the master-data=2 and binlogging comes in
In your backup.sql file, you'll have a line at the top that looks like CHANGE MASTER TO MASTER_LOG_FILE=’mysql-bin.000003′, MASTER_LOG_POS=350789121;
Get the diff since backup.sql as an SQL file mysqlbinlog /var/log/mysql/mysql-bin.000003 --start-position=350789121 --base64-output=NEVER > output.sql
Run those queries on your RDS instance to update it cat output.sql | mysql -h RDS_endpoint -u username -p DB_name
Get the new log position by finding end_log_pos at the end of the latest output.sql file.
Get the diff since the last output.sql (like step 6) and repeat steps 7 + 8.

The actual migration

Have all your apps ready to deploy quickly with the new RDS instance
Get the latest end_log_pos from output.sql
Run FLUSH TABLES WITH READ LOCK; on the old instance to stop all writes
Start deploying your apps with the new RDS instance
Run steps 6-8 from above to update the RDS instance with the last queries to the old server

Conclusion

Using this method, you'll have a small amount of time (depending on how long it takes to deploy your apps + how many writes your MySQL instance serves - probably only a minute or two) with writes being rejected from your old server, but you will have a consistent migration with no read downtime.

A full and detailed post explaining how we (GoSquared) migrated to RDS with minimal downtime (including error debugging) is available here: https://engineering.gosquared.com/migrating-mysql-to-amazon-rds.

I am completely agree with @SanketDangi.

There are two ways of doing this one way is as suggested using either mysqldump or mysqlimport.

I have seen cases where it creates problem while restoring data on cloud gets corrupt.

However importing applications on cloud has became much easier now a days. You try uploading your DB server on to public cloud through ravello.

You can import your database server itself on Amazon using ravello.

Disclosure: I work for ravello.

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

AWS RDS Customer data Import guide for Mysql is available here : http://aws.amazon.com/articles/2933

Create flat files containing the data to be loaded
Stop any applications accessing the target DB Instance
Create a DB Snapshot
Disable Amazon RDS automated backups
Load the data using mysqlimport
Enable automated backups again

If you are using the terminal this is what worked for me:

mysqldump -u local_username -plocal_password local_db_name | mysql -h myRDS-at-amazon.rds.amazonaws.com -u rds-username -prds_password_xxxxx remote_db_name

and then i used MYSQL WorkBench (free download) to check it was working because the command line was static after pressing submit, i could have probably put -v at end to see it's output

Note: there is no space after -p

Here are the steps which i have done and had sucess.

Take the MySQLdump of the needed database.

mysqldump -u username -p databasename --single-transaction --quick --lock-tables=false >databasename-backup-$(date +%F).sql

( Dont forget to replace the username as root – most of the times, and databasename -> Db name of database which you are going to migrate to RDS )

Once prompted, enter your password.

Once done, login to the RDS Instance from your MySQL server ( Make sure the security groups are configured to allow the connection from Ec2 to RDS )

mysql -h hostaddress -P 3306 -u rdsusername -p

( Dont forget to replace hostaddress with the address of your RDS Instance and rdsusernmae with username for your RDS Instance, when prompted give the password too )

You find that hostaddress under – Connectivity & security -> Endpoint & port under RDS Database From AWS Console.