mysqldump for backups

Making it awesome!

• mysqldump --quick --complete-insert --quote-names mysql | gzip > dump.gz

• logrotate fragment

  /var/lib/backup/mysql/*.gz {
          daily
          rotate 2
          nocreate
          missingok
  }

• the script

  #!/bin/bash
  #
  # Backup the entire MySQL
  #
  
  MY_BACKUP=/var/lib/backup/mysql
  LOGROTATE_BINARY=/usr/sbin/logrotate
  LOGROTATE_FRAGMENT=/tmp/mysqldump_logrotate.tmp.conf
  GZIP_BINARY=gzip
  # Once upon a time we tested using pigz, but it doesn't support --rsyncable
  # This is important, because lots of our servers use rdiff-backup
  # Maybe pigz will support it in future - <barney@anchor.net.au> 2010-06-17
  #if which pigz >/dev/null ; then GZIP_BINARY=pigz ; fi
  
  if [ ! -d $MY_BACKUP ]
  then
          mkdir -v -p -m 0700 $MY_BACKUP
  fi
  
  if [ ! -d $MY_BACKUP ]
  then
          echo "ERROR: database backup directory '$MY_BACKUP' does not exist." 1>&2
          exit 1
  fi
  
  
  # Compress data in an rsync-efficient manner, if possible
  # This extra test is for pigz, as it dies if it even smells the GZIP env-var
  if [ x$GZIP_BINARY == x'gzip' ] ; then
          if gzip --help | grep -q -- --rsyncable
          then
                  # gzip reads this environment variable automatically
                  export GZIP="--rsyncable"
          fi
  fi
  
  
  # rotate the old one out, clears the way for mysqldump.gz
  cat > $LOGROTATE_FRAGMENT <<EOF
  /var/lib/backup/mysql/*.gz {
          daily
          rotate 2
          nocreate
          missingok
  }
  EOF
  $LOGROTATE_BINARY -f $LOGROTATE_FRAGMENT
  rm -f $LOGROTATE_FRAGMENT
  
  
  # do the dump
  /usr/bin/mysqldump --quick --complete-insert --all-databases --quote-names | $GZIP_BINARY > $MY_BACKUP/mysqldump.gz
  
  
  # Purge the binary log
  # This could/should be templated for puppetised systems
  if /usr/bin/mysqladmin variables | grep -q log_bin.*ON
  then
          /usr/bin/mysql -e 'RESET MASTER;'
  fi