{{
#!/bin/bash
#MySQL# MySQL Backup Script
#PreReqs# PreReqs: Mutt mail client
# Add to cron to make it a scheduled backup
#Copyright# 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# 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
}} |