Tuesday, August 03, 2010

Quickly dumping a MySQL database out to a file

I've found that sometimes, I just need a quick and dirty copy of a database to test changes against, and it doesn't matter if the data is recent, or even consistent for that matter. That's where mysqldump comes in handy with the --single-transaction option. It can be used on a live database because it doesn't lock the tables and prevent your web application from continuing to insert, modify and delete new records. One example would be :

mysqldump -u myusername -h myhost -p --single-transaction mydbname | gzip > mybackupfile.20100803.sql.gz &

This can be made even quicker by combining this dumping into an SSH transfer to copy the output data to another machine.

No comments: