Is there a way to dump all mysql databases except for system databases?

Question

I use mysqldump to automatically dump all my databases to a text file and save this dump as backup. I use the --all-databases option which dumps my databases, but it also dumps system databases (information_schema, phpmyadmin, etc.) which I don't need.

Is there a way to dump all my databases with mysqldump without naming them explicitly on the command line (so that I don't have to modify the backup script every time I create a new database), but ignore all the system databases?

Answer 1

You could write a bash script like this. It checks the database's name before dumping it.

#!/bin/sh
DATABASES="$(/lighttpd/local/bin/mysql --user=user --password=pass -Bse 'show databases')"

for db in ${DATABASES[@]}
do
if [ $db == "information_schema" ]
then
continue
fi
echo ${db}-$(date +%m-%d-%y).sql.bz2 is being saved in /backup/mysql
mysqldump --user=user --password=pass $db --single-transaction -R | bzip2 -c > ${db}-$(date +%m-%d-%y).sql.bz2
done