11

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';
  • Is it possible to download one of the snapshots saved on AWS onto my local machine? I'd prefer not to connect directly to my database to get these backups. – modulitos Dec 11 '17 at 3:58
8

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

  • 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
  • 1
    the same command i get Got error: 1045: Access denied for user 'root' with root user – Sushivam Nov 14 '16 at 12:39
  • 1
    @modulitos you have to create an instance from a snapshot and then mysqldump that instance – Raisen Nov 1 '18 at 18:26
1

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.

Your Answer

By clicking "Post Your Answer", you agree to our terms of service, privacy policy and cookie policy

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