{{
#!/bin/bash
#MySQL Backup Script
#PreReqs: Mutt mail client
#Copyright 2012 Nik Martin
#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
#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
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
if [ $? -eq 0 ]; then
echo Removing $DBOUT
rm $DBOUT
else
echo Error sending $DBOUT
fi
done
}}