MySQL Backup Script

{{

#!/bin/bash

#MySQL Backup Script
#PreReqs: Mutt mail client

1. Add to cron to make it a scheduled backup

#Copyright 2012 Nik Martin

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

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

1. This command runs the `show databases' query against the mysql server
2. it then pipes that result to awk which prints out the dbnames
3. finally use grep -v to strip out any db's that shouldn't be backed up
4. 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 '

' | grep -v "information_schema"`

#now loop through, dumping each database to a gzip
for i in $DBS
do

1. 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

1. 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
}}
}}