Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

How do I create a local backup of a remote Amazon RDS MySQL database? What I need to know is how to make a complete full local backup of a MySQL database (not a remote Amazon snapshot) that can be used to restore to a new database anywhere, on any MySQL server (same version of course).

NOTE: I know how to import data from a flat file via MySQL. Here is how I connect:

mysql -h mydb.xxxxx.us-east-1.rds.amazonaws.com -u myuser -P 3306 -p

Here is how I load a single database table:

mysql> LOAD DATA LOCAL INFILE 'C:/Temp/t1.tab' INTO TABLE t1 LINES TERMINATED BY '\r\n';
share|improve this question

2 Answers 2

up vote 1 down vote accepted

mysqldump -h mydb.xxxxx.us-east-1.rds.amazonaws.com -u myuser -P 3306 -p > rds.sql

share|improve this answer
    
Answer turned out to be: mysqldump --databases myDB -v -h mydbInst.xxxxxx.us-east-1.rds.amazonaws.com -u myuser -P 3306 -p > rdsmyDB.sql –  djangofan Aug 18 '11 at 18:48
1  
This fails for even moderately sized databases. The RDS instance times out and drops the connection, which mysqldump unfortunately interprets as "download complete". I can only download about 30MB of my 175MB database before mysqldump fails. –  Cerin Nov 16 '11 at 15:57

mysqldump should be used with --single-transaction if you are using InnoDB. We do our backups using an AWS micro instance, then transfer/archive that in premise. That is also configured to run a jenkins slave and all this is managed through a central jenkins server we run in premise.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.