How to create a local backup of a remote Amazon RDS MySQL database?
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';
Solution 1:
mysqldump --databases mydb -h xxxxx.us-east-1.rds.amazonaws.com -u myuser -P 3306 -p > rds.sql
Solution 2:
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.