mysql: Backup all databases in separate files

In order to backup all databases on a mysql server at once, you can use the following command:

# mysqldump --all-databases -u xxx --password="xxx" | gzip > full.backup.sql.gz

This will create an sql file with a dump of all databases and compress it. It’s nice, fast and easy. But it’s then not so easy to work with this one single file (especially if only one database needs to be restored).
But this can also be done with a one-liner (though a long one…):

# echo 'show databases' | mysql -u xxx --password="xxx" --skip-column-names | grep -v information_schema | xargs -I {} -t bash -c 'mysqldump -u xxx --password="xxx" {} | gzip > /backup/mysqldump-$(hostname)-{}-$(date +%Y-%m-%d-%H.%M.%S).sql.gz'

This first gets all databases in the local mysql server, dumps each of them and stores them compressed.

One thought on “mysql: Backup all databases in separate files

Leave a Reply

Your email address will not be published.