tags:

views:

54

answers:

1

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?

+1  A: 

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
Andrew M
Okay, but that means I have to list the system databases manually in the script. Is there a way to tell programmatically if a database was created by the system or the user?
Tom
not that I know of. My MySQL db only came with information_schema and mysql as system databases.
Andrew M