Backup each MySQL database on a MySQL server into a separate file.

dump-all-databases.sh

#!/bin/bash
# dump-all-databases.sh
# 
#
# Backup each MySQL database on a MySQL server into a separate file.
# Optionally files can be gzipped (dbname.sql.gz)
#
# Usage: dump_all_databases [ -u username -o output_dir -h hostname -x exclude -z ]
#		 
#	-u username to connect MySQL server
#	-o [output_dir] optionally specify the output directory where to put the files
# 	-h [hostname] optionally specify the hostname of the target MySQL database.
#	-x [exclude] optionally specify a regex for databases that should be excluded.
#	-z enable gzip
#
# Note: The script will prompt for a password, you cannot specify it as command line argument for security reasons.
#
#
#
# Based on a script of the same name by Daniel Verner [1] which was based on a script
# from [2]. I have made some modifications; namely adding a hostname option and an option
# to exclude databases that match a given regex. Verner's script contained no copyright 
# or license. I credit his work and place the remainder of this script in the public domain. 
# 		- Sam Parsons, 2012
# 
#	[1] http://carrotplant.com/en/blog/how-to-dump-all-mysql-databases-into-separate-files
#	[2] http://soniahamilton.wordpress.com/2005/11/16/backup-multiple-databases-into-separate-files/
#



PROG_NAME=$(basename $0)
USER=""
PASSWORD=""
OUTPUTDIR=${PWD}
HOST=""
HOST_STR=""
EXCLUDE=""
GZIP_ENABLED=0
GZIP=""

MYSQLDUMP="/usr/local/mysql/bin/mysqldump"
MYSQL="/usr/local/mysql/bin/mysql"

while getopts u:o:h:x:z OPTION
do
    case ${OPTION} in
        u) USER=${OPTARG};;
        o) OUTPUTDIR=${OPTARG};;
		h) HOST=${OPTARG};;
		x) EXCLUDE="|$OPTARG";;
        z) GZIP_ENABLED=1;;
        ?) echo "Usage: ${PROG_NAME} [ -u username -o output_dir -h hostname -x exclude -z ]"
           exit 2;;
    esac
done

if [ "$USER" != '' ]; then

echo -ne "Enter password for" $USER": "
oldmodes=`stty -g`
stty -echo
read PASSWORD
stty $oldmodes
echo "";

fi

if [ ! -d "$OUTPUTDIR" ]; then
    mkdir -p $OUTPUTDIR
fi

if [ "$HOST" != '' ]; then
	HOST_STR=" -h $HOST "
else
	HOST_STR=""
fi

# get a list of databases
databases=`$MYSQL $HOST_STR --user=$USER --password=$PASSWORD -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema$EXCLUDE)"`	


# dump each database in turn
for db in $databases; do
    echo $db
	if [ $GZIP_ENABLED == 1 ]; then
		$MYSQLDUMP $HOST_STR --force --opt --user=$USER --password=$PASSWORD --databases $db | gzip > "$OUTPUTDIR/$db.sql.gz"
	else
	    $MYSQLDUMP $HOST_STR --force --opt --user=$USER --password=$PASSWORD --databases $db > "$OUTPUTDIR/$db.sql"
   	fi    
done

Could you do a version where the files are backed up to a remote server with rsync also? It would be really usable, but I wouldn't know how to do that myself.