If you run mysqldump --all-databases you get one monolithic file which can be a pain when trying to restore one database.

So it's nice to call mysqldump dbname so you get individual database backup files.

To call mysqldump for each database requires that you either hardcode the db names in your backup script, or as below, use the mysql client to get the names of the databases you want to backup and then pipe them into a loop for mysqldump to action.

This following script queries the MySQL database server for all the databases that the DBUSER has access to (it's good to give this backup user SELECT access but not UPDATE, CREATE, DROP, ALTER, GRANT ... etc permissions). Then loops through and creates a gzip compressed backup of each database to a single date stamped file. Then it uses uuencode to attach the db backup to an email and send it to an email address of your choosing. Finally on successfully sending the db attachment via email it removes the db backup gzip file.

Warnings:

• This script leaves a password on disk which will enable anyone reading it to gain access to the contents of your database. So make sure you leave it in a location that is not world readable and probably it's a good idea to change the permissions to 770 or even 700 using chmod 770 filename. However many opensource packages such as wordpress and joomla maintain a user/pass in a world readable config file on disk also so risk or risk you take your pick.
• Depending on the size of the databases that you are backing up the uuencode to email feature may not work. Obviously if your email system has a gateway limit of 10MB then all emails that you send should be smaller that this, allowing for the expansion that occurs during SMTP encoding and a 10 MB gateway limit you should only try to email an attachment less than about 7MB.

#!/bin/bash

# backup_dbs.sh  2010-04-28
# [email protected]
# Add to cron to make it a scheduled backup

DBHOST=mysql.yourdomain.com
DBUSER=mysqldb_user
DBPASS=mysqldb_password

TO=you@yourdomain

# format is YYYYMMDD
DATE=`date +%Y%m%d`

# This command runs the `show databases' query against the mysql server
# it then pipes that result to awk which prints out the dbnames
# finally use grep -v to strip out any db's that shouldn't be backed up
# to skip multiple databases use grep -E -v "dbname1|dbname2|dname3"

DBS=`mysql --host=$DBHOST -p$DBPASS -u $DBUSER --skip-column-names  -e "show databases;" | awk  '{ print $1 }' | grep -v "information_schema"`

for i in $DBS
do

	# format is dbname-YYYYMMDD.gz

	DBOUT=$i-$DATE.gz

	echo Backing up $i to $DBOUT

	mysqldump -u $DBUSER -h $DBHOST -p$DBPASS --add-drop-table $i | gzip - > $DBOUT

	echo Seding $DBOUT to $TO

	( echo backup of $i-$DATE; uuencode $DBOUT $DBOUT ) | mail -s "Backup of $i $DATE" $TO
	if [ "$?" ] ; then 
		echo Removing $DBOUT
		rm $DBOUT
	else
		echo Error sending $DBOUT
	fi

done