MySQL Backup+email bash script

EDIT: January 11 2013 - I tried this on a newer Centos server and it appears the syntax to mutt has changed - you need to add "--" between the attachment and the destination email to have the command processed properly.

There are TONS of MySQL backup scripts around, but none on the first two pages of a Google search that did just what I wanted: backup each MySQL database on a server separately, then email them to me, one at a time. I found a few that were close, and modified them to do what I needed.  Credit is given to the original author when known.

Credit to jamesmcdonald.id.au for the bulk of the main script



#!/bin/bash 

# Add to cron to make it a scheduled backup

#MySQL Backup User Info 
DBHOST=localhost
DBUSER=backup
DBPASS=mybakupuserpass

#a backup user needs select, lock tables, and reload privileges in MySQL:
#GRANT SHOW DATABASES, SELECT, LOCK TABLES, RELOAD ON *.* to backup@localhost IDENTIFIED BY 'readonly';
#FLUSH PRIVILEGES;

#where to send backed up file 
TO=you@yourdomain.com
#mutt has to read body of email from file, not stdin
MUTTXT=/tmp/myback.txt 

# 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"

#get list of databases on server 
DBS=`mysql --host=$DBHOST -p$DBPASS -u $DBUSER --skip-column-names  

\-e "show databases;" | awk  '{ print $1 }' | grep -v "information_schema"`
 

#now loop through, dumping each database to a gzip
for i in $DBS
do
 
        # format is dbname-YYYYMMDD.gz

        DBOUT=$i-$DATE.sql.gz 
        echo Backing up $i to $DBOUT

        #set umask to protect file
        umask 006       

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

        echo "Backup successfully done. Please see attached file." > $MUTTXT
        echo "" >> $MUTTXT
        echo "Backup file: $DBOUT" >> $MUTTXT
        echo "" >> $MUTTXT 
 
        echo Sending $DBOUT to $TO
 
        which mutt > /dev/null
        if [ $? -eq 0 ]; then
                # now mail backup file with this attachment
                mutt -s "$DBOUT Backup" -a "$DBOUT" $TO < $MUTTXT
        else
                echo "Command mutt not found, cannot send an email with attachment"
        fi
        
        if [ $? -eq 0 ]; then
                echo Removing $DBOUT
                rm $DBOUT
        else
                echo Error sending $DBOUT
        fi
 
done