MySQL backup and archive script

dumpmysql.sh

#!/bin/bash

# mysql user needs certain privileges to run this file;
#
# grant SELECT, LOCK TABLES, reload ON *.* TO 'mysql'@'localhost' IDENTIFIED BY 'dump';
#
TODAY=`date '+%Y%m%d%H%M'`
DUMPDIR=/mysql_backups
ARCH_DUMPDIR=/nfs_dumps/`hostname`
LOGFILE=$DUMPDIR/dump.$TODAY.log
BADDUMPS=/tmp/baddumps.txt
GOODSTRING='backup is good'
BADSTRING='backup is incomplete'
KEEPLOCAL=5
KEEPARCH=5

rm /tmp/backupsummary


if [ ! -d $DUMPDIR ]
then
	echo "${DUMPDIR} does not exist"
	exit 1
fi

if [ $(whoami) = "root" ]
then
  echo "You cannot run this script as root."  >> $LOGFILE
  exit 1
fi

echo "starting dump at $TODAY" >> $LOGFILE
echo "----------------------------" >> $LOGFILE
echo "show databases" | mysql -u mysql -pdump | grep -v Database | while read line
do
	if [ ! -d $DUMPDIR/$line ]
	then
		mkdir $DUMPDIR/$line
	fi

	if [ ! "$line" = "information_schema" ]
	then
		# flush and lock tables
		echo "$line: flushing and locking tables" >> $LOGFILE
		echo "flush tables with read lock" | mysql -u mysql  -pdump $line 

		CMD="mysqldump -u mysql -pdump --quick --single-transaction $line"
		OUT="$DUMPDIR/$line/$line.$TODAY.dump"
		echo "$CMD > $OUT" #>> $LOGFILE
		$CMD 1> $OUT 2>> $LOGFILE

		# check veracity of dump
		found=`grep -l -e '-- Dump completed on' $OUT | wc -l`
		if [ "$found" = "0" ]; 
		then 
			echo "$OUT: $BADSTRING" >> $BADDUMPS
		else
			echo "$line is good" >> /tmp/backupsummary
			gzip $DUMPDIR/$line/$line.$TODAY.dump >> $LOGFILE 2>&1 
		fi


		# unlock tables
		echo "$line: dump complete, unlocking tables" >> $LOGFILE
		echo "unlock tables" | mysql -u mysql -pdump $line
	else
		echo "skipping $line" >> $LOGFILE
	fi
done

# my.cnf backup
cnffile=my.cnf.`date '+%a'`
cp -fp /etc/my.cnf $DUMPDIR/$cnffile
echo $DUMPDIR/$cnffile created from /etc/my.cnf  >> $LOGFILE

# remove files older than 1 days from local
find $DUMPDIR/ -type f -name \*.dump\* -mtime +${KEEPLOCAL} &>> $LOGFILE
find $DUMPDIR/ -type f -name \*.dump\* -mtime +${KEEPLOCAL} -exec rm {} \; &>> $LOGFILE
find $DUMPDIR/ -type f -name \*.log\* -mtime +${KEEPLOCAL}  &>> $LOGFILE
find $DUMPDIR/ -type f -name \*.log\* -mtime +${KEEPLOCAL} -exec rm {} \; &>> $LOGFILE

#### below here is archive to NAS ######
# rsync local to nfs
if [ -d $ARCH_DUMPDIR ]
then
	echo 'copy to nfs'  >> $LOGFILE
	rsync -avz $DUMPDIR/* $ARCH_DUMPDIR/  &>> $LOGFILE

	# remove files older than 3 days from nfs
	find $ARCH_DUMPDIR/ -type f -name \*.dump\* -mtime +${KEEPARCH} &>> $LOGFILE
	find $ARCH_DUMPDIR/ -type f -name \*.log\* -mtime +${KEEPARCH} &>> $LOGFILE
	find $ARCH_DUMPDIR/ -type f -name \*.dump\* -mtime +${KEEPARCH} -exec rm {} \; &>> $LOGFILE
	find $ARCH_DUMPDIR/ -type f -name \*.log\* -mtime +${KEEPARCH} -exec rm {} \; &>> $LOGFILE
else
	echo 'nfs is unavailable!' >> $LOGFILE
fi

NOW=`date '+%Y%m%d%H%M'`
echo "dump completed by $NOW" >> $LOGFILE
echo >> $LOGFILE

if [ -f $BADDUMPS ]
then
	echo 'creating error email'  >> $LOGFILE
	SUBJ=`hostname`

	mail -s "$SUBJ: MySql backup errors" me@mymail.com<<END
	`cat $BADDUMPS`
END
	rm $BADDUMPS
fi