Page History

#!/bin/bash 

# MySQL Backup Script
# PreReqs: Mutt mail client
# Add to cron to make it a scheduled backup

# Copyright 2012 Nik Martin

#   Licensed under the Apache License, Version 2.0 (the "License");
#   you may not use this file except in compliance with the License.
#   You may obtain a copy of the License at
#
#       http://www.apache.org/licenses/LICENSE-2.0
#
#   Unless required by applicable law or agreed to in writing, software
#   distributed under the License is distributed on an "AS IS" BASIS,
#   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
#   See the License for the specific language governing permissions and
#   limitations under the License.

# 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 'mybackupsuserpass';
#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