• mysqldump_for_backups

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
  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
  
  # Set this up for later
  cat > $LOGROTATE_FRAGMENT <<EOF
  /var/lib/backup/mysql/*.gz {
          daily
          rotate 2
          nocreate
          missingok
  }
  EOF
  
  # Compress data in a manner that will enable rsync to sync efficiently
  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
  $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
  if /usr/bin/mysqladmin variables | grep -q log_bin.*ON
  then
          /usr/bin/mysql -e 'RESET MASTER;'
  fi