Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
up vote 5 down vote accepted

mysqldump --databases mydb -h 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
    
Hi I am trying to use this to create e remote bakup of Mysql in aws. I am getting error access denied even with correct credentials is it because of aws ssh security? I tried from windows and local jenkins server both with same error – Utsav Gupta Oct 13 '15 at 3:09

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.