Ben E. C. Boyter's Blog

MySQL Backups Done Easily (180 words)

One thing that comes up a lot on sites like Stackoverflow and the like is how to backup MySQL databases.The first answer is usually use mysqldump. This is all fine and good, till you start to want to dump multiple databases. You can do this all in one like using the –all-databases option however this makes restoring a single database an issue, since you have to parse out the parts you want which can be a pain.

So the question is, have you ever wanted to script mysqldump to dump each database into a seperate gziped file?

The below script is what I use for backing up multiple databases, and does the above.

date=`date -I`</pre>

for I in $(mysql -u root -pPASSWORD -e 'show databases' -s --skip-column-names);
   mysqldump -u root -pPASSWORD $I | gzip &gt; "$date-$I.sql.gz";

Its a simple bash script, which connects to MySQL, prints out all the databases and then uses each line as a separate argument for mysqldump. All the databases are saved in their own file and restoring a single database is easy.