Amazon RDS MySQL – How to Create a Local Backup of a Remote Database

amazon ec2amazon-rdsMySQL

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';

Best Answer

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

Related Solutions

Mysql – Import a 260GB csv file into MySQL

I don't know if there are limits for particular DB engines but I've met with some Department of Defense admins who manage a MySQL system which imports 10TB every day and they are not experiencing this kind of problem.

Rather than use macros I recommend writing a script to parse the input file and create insert statements from it, which can be fed straight to MySQL. That way you are not dealing with the entire file in one hit, just one line at a time. I like to use Perl for such things.

You may also like to consider using truncate instead of dropping he table and recreating it each time.

Mysql – Cherry pick and import tables from remote thesql database into local

You can use mysqldump with remote hosts:

mysqldump -h database.server.com -u username -pPassword dbName tblName > local.file.sql

instad of redirecting to a file, you can just pipe it to mysql:

mysqldump <args> | mysql -u uname -pPwd localDbName

Best Answer

Related Solutions

Mysql – Import a 260GB csv file into MySQL

Mysql – Cherry pick and import tables from remote thesql database into local

Related Topic