mysql backup script

backup.sh

#!/bin/bash

# backup mysql tables - does an initial dump and then backs up to final
# location using rdiff-backup

# NOTE: do NOT manually edit or update the rdiff-backup managed final backup
# location. ONLY use rdiff-backup to update files in that backup directory. if
# the files are changed directly, the historical backups that rdiff-backup
# maintains will be corrupted.

# must be nobody
NOBODY_UID=`/usr/bin/id -u nobody`
if [[ "$EUID" != "$NOBODY_UID" ]]; then
  if [[ "$EUID" != "0" ]]; then
    echo "$0 - you need to be root for this to run correctly"
    exit 6
  fi
  /bin/su -s /bin/bash nobody -- -c "$0 $*"
  exit $?
fi

###

# tmpdir for temp files for this script:
backup_tmpdir=`mktemp -d`

# script dumps mysql tables here first:
backup_initial_dir=`mktemp -d -p /data/tmp`

# final backup location, managed by rdiff-backup:
backup_final_dir="/backup/wh1/mysql"

###

# sanity check

if ! [[ -d $backup_final_dir ]]; then
  echo "$0: [$(date)] $backup_final_dir does not exist, aborting"
  exit 7
fi

###

# grab lock
ZERO_BASENAME=`/bin/basename $0`
LOCKFILE="/tmp/$ZERO_BASENAME.lock"
echo "$0: grabbing local lock - $LOCKFILE"
/usr/bin/dotlockfile -p $LOCKFILE 2> /dev/null
if [[ $? != 0 ]]; then
  echo "$0: couldn't obtain lock - lockfile is $LOCKFILE" 1>&2
  exit 1
fi
trap "/usr/bin/dotlockfile -pu $LOCKFILE ; exit 4" 1 2 3 13 15

###

# to speed up backup and restore operations, we apply different options to
# mysqldump for innodb tables. to do this, we first generate a list of innodb
# and non-innodb tables:

# generate list of table names with type
echo "$0: [$(date)] dumping table metadata"
time /usr/bin/mysql -u root -paspcms1 -sse 'show databases' |
  /bin/egrep -v '_wh$|_wh_loading$|^(test|te?mp|Database|information_schema|mysql)$' |
  while read i; do
    echo "$0: [$(date)] db $i"
    /usr/bin/mysql -u root -paspcms1 -sse 'show tables' "$i" |
      while read j; do
        echo -n "$j "
        /usr/bin/mysql -u root -paspcms1 -sse "show create table $j" $i |
          /bin/grep 'ENGINE' |
          /usr/bin/perl -p -e 's/^.*ENGINE=(\S+).*$/$1/'
      done > $backup_tmpdir/$i
  done

# generate list of InnoDB tables in each database
echo "$0: [$(date)] generating list of innodb tables"
/usr/bin/mysql -u root -paspcms1 -sse 'show databases' |
  /bin/egrep -v '_wh$|_wh_loading$|^(test|te?mp|Database|information_schema|mysql)$' |
  while read i; do
    /bin/egrep -e ' InnoDB$' $backup_tmpdir/$i |
      /usr/bin/perl -p -e 'chomp; s/ \S+$/ /' > $backup_tmpdir/$i.innodb
    if [[ -s $backup_tmpdir/$i.innodb ]]; then
      echo >> $backup_tmpdir/$i.innodb
    fi
  done

# generate list of non-InnoDB tables in each database
echo "$0: [$(date)] generating list of non-innodb tables"
/usr/bin/mysql -u root -paspcms1 -sse 'show databases' |
  /bin/egrep -v '_wh$|_wh_loading$|^(test|te?mp|Database|information_schema|mysql)$' |
  while read i; do
    /bin/egrep -ve ' InnoDB$' $backup_tmpdir/$i |
      /usr/bin/perl -p -e 'chomp; s/ \S+$/ /' > $backup_tmpdir/$i.other
    if [[ -s $backup_tmpdir/$i.other ]]; then
      echo >> $backup_tmpdir/$i.other
    fi
  done

###

# run mysqldump

# backup tables in each database
echo "$0: [$(date)] dumping mysql data"
/usr/bin/mysql -u root -paspcms1 -sse 'show databases' |
  /bin/egrep -v '_wh$|_wh_loading$|^(test|te?mp|Database|information_schema|mysql)$' |
  while read i; do
    if [[ -s $backup_tmpdir/$i.innodb ]] ; then
      echo "$0: [$(date)] dumping innodb tables in db $i"
      time /usr/bin/mysqldump -uroot -paspcms1 --max_allowed_packet=512M --opt --events \
        --routines --triggers --no-autocommit --single-transaction \
        --skip-lock-tables $i \
        `/bin/cat $backup_tmpdir/$i.innodb` |
          /bin/gzip > $backup_initial_dir/$i.innodb.gz
    fi
    if [[ -s $backup_tmpdir/$i.other ]] ; then
      echo "$0: [$(date)] dumping non-innodb tables in db $i"
      time /usr/bin/mysqldump -uroot -paspcms1 --max_allowed_packet=512M --opt --events \
        --routines --triggers --no-autocommit $i \
        `/bin/cat $backup_tmpdir/$i.other` |
          /bin/gzip > $backup_initial_dir/$i.other.gz
    fi
  done

# backup data dumps with rdiff-backup
echo "$0: [$(date)] backing up mysql dump to $backup_final_dir"
time /bin/nice /usr/bin/rdiff-backup $backup_initial_dir $backup_final_dir

# remove initial backup files
echo "$0: [$(date)] removing temporary mysql dump data"
time /bin/rm -rf $backup_initial_dir

echo "$0: [$(date)] current disk usage:"
/bin/df -h

echo "$0: [$(date)] done"

# release lock
/usr/bin/dotlockfile -pu $LOCKFILE

exit 0