MySQL Database Backup Script

dbup.sh

#!/bin/bash
 
DBHOST=localhost
DBUSER=backup
DBPASS=*********
[email protected]
MES=/tmp/myback.txt

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

#get list of databases 
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.sql.gz
        DBOUT=$i-$DATE.sql.gz
 
        echo Backing up $i to $DBOUT
 
        #protect dumped dbs
        umask 066

        #dump that biotch lke a bad habit
        mysqldump -u $DBUSER -h $DBHOST -p$DBPASS --add-drop-table $i | gzip -9 - > $DBOUT

        #built the text file that utt will send via email
        echo "Backup successfully done. Please see attached file." > $MES
        echo "" >> $MES
        echo "Backup file: $DBOUT" >> $MES
        echo "" >> $MES

 
        echo Sending $DBOUT to $TO
 
        which mutt > /dev/null
        if [ $? -eq 0 ]; then
                # now mail backup file with this attachment
                mutt -s "$DBOUT Backup" $TO -a "$DBOUT" < $MES
        else
                echo "Command mutt not found, can't send email"
        fi

        #if mutted the delete the file
        if [ $? -eq 0 ]; then
                echo Removing $DBOUT
                rm $DBOUT
        else
                echo Error sending $DBOUT
        fi
 
done