15

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';
djangofan
  • 4,182
  • 10
  • 46
  • 59
  • 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 03:58

2 Answers2

11

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

bsima
  • 103
  • 3
nlo
  • 341
  • 2
  • 3
  • 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
  • 2
    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 03: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
  • 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 03:58
  • 1
    @modulitos you have to create an instance from a snapshot and then mysqldump that instance – Raisen Nov 01 '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.

user1819998
  • 56
  • 1