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

A similar approach, excluding the dbs you don't want to backup:

user=''
pass=''
# Use a | as a separator
exclude_dbs='information_schema|mysql|performance_schema'

mysqldump -u "$user" -p"$pass" --databases $(mysql -u $user -p$pass -rs -e 'SHOW DATABASES;' | tail -n+1 | grep -v -E '^('$exclude_dbs')$' | tr '\n' ' ') > databases.sql

Answer 2

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