Scripting continued

Since I have been discussing scripting lately I thought I would continue with another topic I touched on briefly - backups.

I have written and modified the following script over the last few years. I have used it (and continue to use it) with multiple clients. It uses Percona's Xtrabackup to take the backup (although it can be easily modified to use mysqldump instead).

First the script

-----------------------------------------------------------------------------------------------------------------

#!/bin/bash
SAVEIFS=$IFS
IFS=$""
day_of_week=`date +%a`
backup_dir=/mysql-backup/
logfile=/root/backup_log.txt
report=/tmp/report.txt
servername=slave1
email=bmurphy@paragon-cs.com
password=`cat /root/.ssh/.backup_password`

# run backup
echo ' ' > $report
echo 'The backup is now beginning:' >> $report
echo ' ' >> $report
echo `date` >> $report


# remove compressed files older than 7 days

find $backup_dir/compressed -type f -mtime +6 -exec rm {} \;
rm -rf $backup_dir/tmp/*
/usr/bin/innobackupex-1.5.1 --user=root --password=$password --slave-info $backup_dir/tmp > $logfile 2>&1
echo ' ' >> $report
echo 'The backup is now complete:' >> $report
echo ' ' >> $report
echo `date` >> $report

# When innobackup finishes successfully, it writes to the log:
#   innobackupex-1.5.1: completed OK!
# The following checks if that string exists in the log file

grep "innobackupex-1.5.1: completed OK!" "/root/backup_log.txt" 2>&1 > /dev/null
if [ $? -ne 0 ]         # string not found, ie. backup failed
then
  echo "Backup failure. Please check log at /root/backup_log.txt" >> $report
  /bin/mailer.pl "$email" "$servername-mysql-backup@pargon-cs.com" "Failed: $servername MySQL Backup Report" "The backup failed, see attached report." "/root/backup_log.txt"
      exit 1
else
      echo "Backup completed successfully. Results logged at /root/backup_log.txt" >> $report

      echo ' ' >> $report
      echo 'Beginning tarring/compression:' >> $report
      echo ' ' >> $report
      echo `date` >> $report


      # compress backup directory and store in $backup_dir/compressed

      tar cvzf $backup_dir/compressed/mysql-backup-$day_of_week.tar.gz $backup_dir/tmp/ >> $logfile 2>&1
      status=$?
      if [[ $status != 0 ]] ; then

        echo ' ' >> $report
        echo 'Tar compressions failed.' >> $report
        echo 'Please check backup!!!' >> $report
        echo 'The backup was left in $backup/tmp' >> $report
        echo ' ' >> $report
        success='no'
        /bin/mailer.pl "$email" "$servername-mysql-backup@paragon-cs.com" "Failure: $servername MySQL Backup Report" "The compression of the backup was a failure. See attached report." "/tmp/report.txt"

        exit 1

      else
        echo ' ' >> $report
        echo 'The compression of backup is now complete:' >> $report
        echo ' ' >> $report
        echo `date` >> $report
        rm -rf $backup_dir/tmp/*
        success='yes'
      fi

      echo 'Disk space used:' >> $report
      echo ' ' >> $report
      echo `df -kh` >> $report
      echo ' ' >> $report
      echo `date` >> $report


      /bin/mailer.pl "$email" "$servername-mysql-backup@paragon-cs.com" "Success: $servername MySQL Backup Report" "The backup was successful. See attached report." "/tmp/report.txt"



      IFS=$SAVEIFS
  exit 0

fi

 -----------------------------------------------------------------------

Notice I have logging of success (and failure). There is some error checking (although probably not enough). I abstract quite a bit of it so it makes it easy to modify things like where the password is located and the location of the backup. The mailer program is a custom perl script that is used to provide a little more reliability and logging. since that was written by a colleague I don't have the ability to include it. However, there are other similar scripts available online.

Do NOT rely on yourself to run backups. Cron is your friend. Logging is your friend for when there are problems. And since we are on the topics of backups...do not ever assume backups are running correctly. I prefer to have restores automated and checked weekly. At least quarterly a backup should be restored and checked by hand.

And a full dump is not the only backup you should be making. But this isn't a post on backups. So we'll save that for another post.

km