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

Interviewing tip..

I've been involved in a number of interviews over the last few weeks as a client has been loking for a MySQL DBA. When you are looking for position as a DBA in a large scale environement there are some very important things you have to know.

You absolutely must know a scripting language. In a smaller environment this often isn't necessary. You will live and die by this in a large environment. I asked every applicant one specific question..if you had to change a mysql server variable on a pool of 100 mysql servers how would you do this? It's easy when it's one,two or even a dozen servers. just log in, change the my.cnf and change it "on the fly" if you can. Restart mysql if you can't.

You going to do that to 100 servers? It will take all day and be prone to failures. Scripting is the key here. Even just bash shell scripting can be very powerful. In another post I will cover a simple bash script to loop through a series of servers executing MySQL commands on them. Output can be logged for later analysis. Changes to the my.cnf in an automated method are a little more complex but can be accomplished with multiple methods. Perl and even php are quite common as scripting options as well. While I've done no study on this, it has been my experience that those are the top three scripting languages in that order.

It requires a bit of system administration skill for a large environment. Since I came from a sys admin background it wasn't a difficult transition at all. Even if you have no experience with scripting the basics can be learned within weeks or months. This can make a huge difference in your "employability" and this type of automation can make for a much more stable environment no matter the number of servers you manage.

km