mysqldump to multiple files

It is a good idea to run mysqldump by cron to regurlarly make backups of your MySQL databases. An easy way to do this is by adding the –all-databases parameter.  This way you will have a backup of all your databases – in one file though. If the amount of data and the number of databases is small this might be ok.

After the first session of restoring just one database or one table from your one-file-backup you realize that it would be nice to have the dumps split up in some convenient way. Like dumping each database to one file, one for each database.

This little shell script does the trick for you. It is really simple and has no error handling what so ever. It is released under GNU GPLv2 license. If you modifiy it or add features (like error handling or so), please do send me a copy or post it as a comment here.

# Copyright HelTech Communication, Stefan Helander 2010
# Licensed under GNU GPLv2
# Dumps all MySQL databases. Each database stored in a single file.
# settings


# dump each db

for DB in `echo 'show databases' | $MYSQL -h$MYSQL_HOST -u$MYSQL_USER -p$MYSQL_PASS --batch -N`
$MYSQLDUMP -h$MYSQL_HOST -u$MYSQL_USER -p$MYSQL_PASS --quote-names --create-options --force $DB > $DUMPDIR/$DB.sql