A fancier mysql backup script for Xtrabackup:

xtrabackup_runner.sh

#!/bin/bash
# 
# Script to create full and incremental backups (for all databases on server) using innobackupex from Percona.
# http://www.percona.com/doc/percona-xtrabackup/innobackupex/innobackupex_script.html
#
# Every time it runs will generate an incremental backup except for the first time (full backup).
# FULLBACKUPLIFE variable will define your full backups schedule.
# 
# 2012 Brad Svee modified to try to use xbstream
# (C)2012 Atha Kouroussis @ Vurbia Technologies International Inc.
# (C)2010 Owen Carter @ Mirabeau BV
# This script is provided as-is; no liability can be accepted for use.
# You are free to modify and reproduce so long as this attribution is preserved.
#
#example crontab entry for backup keep 10 full daily backups and create incremental every hour
#1 */1 * * * /usr/bin/xtrabackup_runner.sh -a backup -T auto -c /etc/my.cnf -u backup -p SecretPw! -k 10 -o /mnt/backup/ >/dev/null 2>&1


# Set some default variables
PRETEND=true # by default we do dry-runs
INVALID=false # Flag if invalid config was found
LOG_OUTPUT=${LOG_OUTPUT:-/dev/stdout}
LOG_DEFAULT_FMT='[$TS][$_LOG_LEVEL_STR][${FUNCNAME[1]}:${BASH_LINENO[0]}]'
LOG_DEBUG_LEVEL=10
LOG_INFO_LEVEL=20
LOG_WARNING_LEVEL=30
LOG_ERROR_LEVEL=40
LOG_CRITICAL_LEVEL=50
LOG_LEVEL=${LOG_LEVEL:-$LOG_INFO_LEVEL}
# LOG_LEVELS structure:
# Level, Level Name, Level Format, Before Log Entry, After Log Entry
LOG_LEVELS=(
  $LOG_DEBUG_LEVEL    'DEBUG   ' "$LOG_DEFAULT_FMT" "\e[1;34m"    "\e[0m"
  $LOG_INFO_LEVEL     'INFO    ' "$LOG_DEFAULT_FMT" "\e[1;32m"    "\e[0m"
  $LOG_WARNING_LEVEL  'WARNING ' "$LOG_DEFAULT_FMT" "\e[1;33m"    "\e[0m"
  $LOG_ERROR_LEVEL    'ERROR   ' "$LOG_DEFAULT_FMT" "\e[1;31m"    "\e[0m"
  $LOG_CRITICAL_LEVEL 'CRITICAL' "$LOG_DEFAULT_FMT" "\e[1;37;41m" "\e[0m"
)
XBSTREAM=false
CALG='gzip'
CALG_EXT='gz'
XOPTIONS=''
CALG_OPT='-z'


# Some support functions
find_log_level () {
  local LEVEL=$1
  local i
  _LOG_LEVEL_STR="$LEVEL"
  for ((i=0; i<${#LOG_LEVELS[@]}; i+=5)); do
    if [[ "$LEVEL" == "${LOG_LEVELS[i]}" ]]; then
      _LOG_LEVEL_STR="${LOG_LEVELS[i+1]}"
      _LOG_LEVEL_FMT="${LOG_LEVELS[i+2]}"
      _LOG_LEVEL_BEGIN="${LOG_LEVELS[i+3]}"
      _LOG_LEVEL_END="${LOG_LEVELS[i+4]}"
      return 0
    fi
  done
  _LOG_LEVEL_FMT="$LOG_DEFAULT_FMT"
  _LOG_LEVEL_BEGIN=""
  _LOG_LEVEL_END=""
  return 1
}

# General logging function
# $1: Level
log () {
  local LEVEL=$1
  shift
  (( LEVEL < LOG_LEVEL )) && return 1
  local TS=$(date +'%Y-%m-%d %H:%M:%S.%N')
  # Keep digits only up to milliseconds
  TS=${TS%??????}
  find_log_level $LEVEL
  local OUTPUT
  eval "OUTPUT=\"$_LOG_LEVEL_FMT\""
  echo -ne "$_LOG_LEVEL_BEGIN$OUTPUT " > "$LOG_OUTPUT"
  echo -n $@ > "$LOG_OUTPUT"
  echo -e "$_LOG_LEVEL_END" > "$LOG_OUTPUT"
}

shopt -s expand_aliases
alias debug='log 10'
alias info='log 20'
alias warn='log 30'
alias error='log 40'
alias critical='log 50'
alias call_stack='debug Traceback ; log_call_stack'

# Log Call Stack
log_call_stack () {
  local i=0
  local FRAMES=${#BASH_LINENO[@]}
  # FRAMES-2 skips main, the last one in arrays
  for ((i=FRAMES-2; i>=0; i--)); do
    echo '  File' \"${BASH_SOURCE[i+1]}\", line ${BASH_LINENO[i]}, in ${FUNCNAME[i+1]}
    # Grab the source code of the line
    sed -n "${BASH_LINENO[i]}{s/^/    /;p}" "${BASH_SOURCE[i+1]}"
    # TODO extract arugments from "${BASH_ARGC[@]}" and "${BASH_ARGV[@]}"
    # It requires 'shopt -s extdebug'
  done
}

function validate {
  # Validate that we were supplied with a valid action to perform
  if [ -z $ACTION ]
  then
    error "You must specify and action to perform (backup or restore)"
    INVALID=true
  else
    case $ACTION in
      backup)
        # Check for mysql binary
        if [ ! -f $MYSQL ]
        then
          error "mysql not found under ${MYSQL}. Please specify the correct binary path (currently ${BIN_DIR}) with -b and/or correct version with -v (currently ${VERSION})"
          INVALID=true
        else
          if [ ! -x $MYSQL ]
          then
            error "mysql found under ${MYSQL} but we don't seem to have permission to execute"
            INVALID=true
          fi
        fi
        
        # Check for mysql binary
        if [ ! -f $MYSQLADMIN ]
        then
          error "mysqladmin not found under ${MYSQLADMIN}. Please specify the correct binary path (currently ${BIN_DIR}) with -b and/or correct version with -v (currently ${VERSION})"
          INVALID=true
        else
          if [ ! -x $MYSQLADMIN ]
          then
            error "mysqladmin found under ${MYSQLADMIN} but we don't seem to have permission to execute"
            INVALID=true
          fi
        fi
        
        # Check that MySQL is running
        # TODO: change this since its not reliable (maybe credentials are incorrect)
        if [ -z "$($MYSQLADMIN $USEROPTIONS status | grep 'Uptime')" ] ; then
         error "MySQL server does not appear to be running."
         INVALID=true
        fi

        # Check we can access MySQL
        if ! $(echo 'exit' | $MYSQL -s $USEROPTIONS) ; then
         error "Supplied mysql username or password appears to be incorrect. Please supply correct credentials with -u and -p"
         INVALID=true
        fi
        
        # Validate the presence of the backup directory
        if [ ! -d $BACKUPDIR ]
        then
          error "Backup directory ${BACKUPDIR} does not exist or its not accesible. Please specify the correct path with -o"
          INVALID=true
        else
          if [ ! -d $FULLBACKUPDIR ]
          then
            warn "Full backup directory $FULLBACKUPDIR does not exist. Creating..."
            mkdir $FULLBACKUPDIR
          fi

          if [ ! -d $INCRBACKUPDIR ]
          then
            warn "Incremental backup directory INCRBACKUPDIR does not exist. Creating..."
            mkdir $INCRBACKUPDIR
          fi
        fi
        
        # Check and normalize backup type
        case $TYPE in
          full|Full)
            TYPE="full"
            ;;
          incr|incremental|Incremental|diff|differential|Differential)
            TYPE="incr"
            ;;
          auto)
            TYPE="auto"
            ;;
          *)
            error "Unknown backup type ${TYPE}. Valid backup types are full|incremental|auto. Default is auto."
            INVALID=true
            ;;
        esac
        ;;
      restore)
        # Validate the presence of the backup directory
        if [ ! -d $BACKUPDIR ]
        then
          error "Backup directory ${BACKUPDIR} does not exist or its not accesible. Please specify the correct path with -o"
          INVALID=true
        fi
        
        # Validate that we were supplied with a valid directory to restore from
        if [ -z $RESTORE_DIR ]
        then
          error "You must specify a valid directory to restore from"
          INVALID=true
        else
          if [ ! -d $RESTORE_DIR ]
          then
            error "Restore directory ${RESTORE_DIR} does not exist or its not accesible. Please specify the correct path with -r"
          fi
		fi
        ;;
      *)
        error "Invalid action ${ACTION}. Valid actions are backup|restore"
        INVALID=true
        ;;
    esac
  fi
  
  # Validate the innobackupex binary is present and executable
  if [ ! -f $INNOBACKUPEXFULL ]
  then
    error "innobackupex not found under ${INNOBACKUPEXFULL}. Please specify the correct binary path (currently ${BIN_DIR}) with -b and/or correct version with -v (currently ${VERSION})"
    INVALID=true
  else
    if [ ! -x $INNOBACKUPEXFULL ]
    then
      error "innobackupex found under ${INNOBACKUPEXFULL} but we don't seem to have permission to execute"
      INVALID=true
    fi
  fi
  
  # Validate the presence of the MySQL config file
  if [ ! -f $MYCNF ]
  then
    error "MySQL config file not found under ${MYCNF}. Please specify the correct path with -c"
    INVALID=true
  fi
  
  # Exit if we found problems
  if $INVALID
  then
    critical "Validation errors found. Exiting."
    exit 2
  fi
}

# Check for errors in innobackupex output
check_innobackupex_error() {
	if [ -z "$(tail -1 $TMPFILE | grep 'completed OK!')" ] ; then
    critical "$INNOBACKUPEX failed:"; echo
    critical "---------- ERROR OUTPUT from $INNOBACKUPEX ----------"
    cat $TMPFILE
    rm -f $TMPFILE
    exit 1
  fi
}

function backup {
  # Grab start time
  STARTED_AT=$(date +%s)
  info "tmp file location: $TMPFILE"  
  # Some info output
  info "----------------------------"
  info
  info "$0: MySQL backup script"
  info "Backup started: `date`"
  info
  
  case $TYPE in
    full)
      full_backup
      ;;
    incr)
      incremental_backup
      ;;
    auto)
      auto_backup
      ;;
  esac
  
  check_innobackupex_error

  THISBACKUP=$(awk -- "/Backup created in directory/ { print p[2] }" $TMPFILE)  
 #THISBACKUP=$(awk -- "/Backup created in directory/ { split( \\$0, p, \"'\" ) ; print p[2] }" $TMPFILE)
  rm -f $TMPFILE

  info "Databases backed up successfully to: $THISBACKUP"
  info

  # Cleanup
  info "Cleanup. Keeping only $KEEP full backups and its incrementals."
  AGE=$(($FULLBACKUPLIFE * $KEEP / 60))
  find $FULLBACKUPDIR -maxdepth 1 -type d -mmin +$AGE -execdir echo "removing: "$FULLBACKUPDIR/{} \; -execdir rm -rf $FULLBACKUPDIR/{} \; -execdir echo "removing: "$INCRBACKUPDIR/{} \; -execdir rm -rf $INCRBACKUPDIR/{} \;

  info
  info "completed: $(date)"
  exit 0
}

function find_latest_full_backup {
  # Find latest full backup
  LATEST_FULL=$(find $FULLBACKUPDIR -mindepth 1 -maxdepth 1 -type d -printf "%P\n" | sort -nr | head -1)
  # Get latest backup last modification time
  LATEST_FULL_CREATED_AT=$(stat -c %Y $FULLBACKUPDIR/$LATEST_FULL)
  info "Starting full backup, latest full: $LATEST_FULL_CREATED_AT"
}

function find_latest_incremental_backup {
  # Find latest incremental backup.
  LATEST_INCR=$(find $TMPINCRDIR -mindepth 1 -maxdepth 1 -type d | sort -nr | head -1)
  
  # If this is the first incremental, use the full as base. Otherwise, use the latest incremental as base.
  if [ ! $LATEST_INCR ] ; then
    INCRBASEDIR=$FULLBACKUPDIR/$LATEST_FULL
  else
    INCRBASEDIR=$LATEST_INCR
  fi
}

function full_backup {
  if [ $COMPRESSION ] ; then
    compressed_full_backup
  else
    info "Running new full backup."
	$INNOBACKUPEXFULL --defaults-file=$MYCNF $USEROPTIONS $FULLBACKUPDIR > $TMPFILE 2>&1
  fi

}

function compressed_full_backup {
	TSTAMP=$(date +%Y-%m-%d_%H-%M-%S)
	mkdir -p $FULLBACKUPDIR/$TSTAMP
	info "Running new compressed full backup. *Note2 tar -fixz backup.tar.gz must use the -i attribute to extract"
    $INNOBACKUPEXFULL --defaults-file=$MYCNF $USEROPTIONS $XOPTIONS --stream=tar ./  | $CALG - > $FULLBACKUPDIR/$TSTAMP/backup.tar.$CALG_EXT

# 2>$TMPFILE
    info "extracing checkpoint file: tar -C $FULLBACKUPDIR/$TSTAMP -xi ${CALG_OPT} -f $FULLBACKUPDIR/$TSTAMP/backup.tar.$CALG_EXT xtrabackup-checkpoints"
    tar -C $FULLBACKUPDIR/$TSTAMP -xi ${CALG_OPT} -f $FULLBACKUPDIR/$TSTAMP/backup.tar.$CALG_EXT $FULLBACKUPDIR/$TSTAMP/xtrabackup-checkpoints
}

function incremental_backup {
  # Create incremental backups dir if not exists.
  TMPINCRDIR=$INCRBACKUPDIR/$LATEST_FULL
  mkdir -p $TMPINCRDIR

  find_latest_incremental_backup
  if [ $COMPRESSION ] ; then
    compressed_incremental_backup
  else
	info "Running new incremental backup using $INCRBASEDIR as base."
	$INNOBACKUPEXFULL --defaults-file=$MYCNF $USEROPTIONS $XOPTIONS --incremental $TMPINCRDIR --incremental-basedir $INCRBASEDIR > $TMPFILE 2>&1
  fi
}

function compressed_incremental_backup {
	TSTAMP=$(date +%Y-%m-%d_%H-%M-%S)
	mkdir -p $INCRBASEDIR/$TSTAMP
	info "Running new compressed incremental backup using $INCRBASEDIR as base. *Note2 tar -xizf backup.tar.gz must use the -i attribute to extract"
	$INNOBACKUPEXFULL --defaults-file=$MYCNF $USEROPTIONS $XOPTIONS --incremental $TMPINCRDIR --incremental-basedir $INCRBASEDIR --stream=tar ./ 2>$TMPFILE | $CALG - 1> $INCRBASEDIR/$TSTAMP/inc_backup.tar.$CALG_EXT
	tar -C $INCRBASEDIR/$TSTAMP -x $CALG_OPT --file=$INCRBASEDIR/$TSTAMP/inc_backup.tar.$CALG_EXT xtrabackup-checkpoints
}

function auto_backup {
  find_latest_full_backup

  # Run an incremental backup if latest full is still valid. Otherwise, run a new full one.
  if [ "$LATEST_FULL" -a $(expr $LATEST_FULL_CREATED_AT + $FULLBACKUPLIFE + 5) -ge $STARTED_AT ] ; then
    incremental_backup
  else
    full_backup
  fi
}

function restore() {
  # Some info output
  info "----------------------------"
  info
  info "$0: MySQL backup script"
  info "Restore started: $(date)"
  info

  PARENT_DIR=$(dirname $RESTORE_DIR)

  if [ $PARENT_DIR = $FULLBACKUPDIR ]; then
    FULLBACKUP=$RESTORE_DIR

    info "Restore $(basename $FULLBACKUP)"
    info
  else
    if [ $(dirname $PARENT_DIR) = $INCRBACKUPDIR ]; then
      INCR=$(basename $RESTORE_DIR)
      FULL=$(basename $PARENT_DIR)
      FULLBACKUP=$FULLBACKUPDIR/$FULL

      if [ ! -d $FULLBACKUP ]; then
        error "Full backup: $FULLBACKUP does not exist."
        exit 1
      fi

      info "Restore $FULL up to incremental $INCR"
      info
	  if [ $COMPRESSION ]; then
        info "Full backup extracting archive: $FULLBACKUP"
        tar -C $FULLBACKUPDIR/$FULL -xif ${CALG_OPT} $FULLBACKUPDIR/$FULL/backup.tar.${CALG_EXT}
      fi

      info "Replay committed transactions on full backup"
      $INNOBACKUPEXFULL --defaults-file=$MYCNF --apply-log --redo-only --use-memory=$MEMORY $FULLBACKUP > $TMPFILE 2>&1
      check_innobackupex_error

      # Apply incrementals to base backup
      for i in $(find $PARENT_DIR -mindepth 1 -maxdepth 1 -type d -printf "%P\n" | sort -n); do
        info "Applying $i to full ..."
		if [ $COMPRESSION ]; then
          info "Full backup extracting archive: $FULLBACKUP"
          tar -C $PARENT_DIR/$i -xif ${CALG_OPT} $PARENT_DIR/$i/inc_backup.tar.${CALG_EXT}
        fi

        $INNOBACKUPEXFULL --defaults-file=$MYCNF --apply-log --redo-only --use-memory=$MEMORY $FULLBACKUP --incremental-dir=$PARENT_DIR/$i > $TMPFILE 2>&1
        check_innobackupex_error

        if [ $INCR = $i ]; then
          break # break. we are restoring up to this incremental.
        fi
      done
    else
      error "Unknown backup type"
      exit 1
    fi
  fi

  prepare_restore

  do_restore

  rm -f $TMPFILE
  info "Backup restored successfully. You are able to start mysql now."
  info "Verify files ownership in mysql data dir."
  info "Run 'chown -R mysql:mysql /path/to/data/dir' if necessary."
  info
  info "completed: $(date)"
  exit 0
}

prepare_restore() {
  info "Preparing restore ..."
#  if [ $COMPRESSION ]; then
#    info "Full backup extracting archive: $FULLBACKUP"
#    tar -C $FULLBACKUPDIR/$FULL -xif ${CALG_OPT} $FULLBACKUP/backup.tar.${CALG_EXT}
#  fi
  $INNOBACKUPEXFULL --defaults-file=$MYCNF --apply-log --use-memory=$MEMORY $FULLBACKUP > $TMPFILE 2>&1
  check_innobackupex_error
}

do_restore() {
  info
  info "Restoring ..."
  $INNOBACKUPEXFULL --defaults-file=$MYCNF --copy-back $FULLBACKUP > $TMPFILE 2>&1
  check_innobackupex_error
}

# Main routine
# Parse commandline arguments
while getopts 'a:b:c:hk:l:m:o:p:r:t:T:u:v:y:z:Z:x:O' OPTION
do
	case $OPTION in
	  a)	ACTION=$OPTARG
  		;;
      b)	BIN_DIR=$OPTARG
        ;;
  	  c)	CONGIG_FILE=$OPTARG
  		;;
      k)	KEEP=$OPTARG
  	    ;;  
  	  l)	LIFE=$OPTARG
  		;;
  	  m)	MEMORY=$OPTARG
  	    ;;
  	  o)	OUTPUT_DIR=$OPTARG
        ;;
  	  p)	PASSWORD=$OPTARG
  		;;
  	  r)  	RESTORE_DIR=$OPTARG
  	    ;;
  	  t)	TMP_DIR=$OPTARG
  		;;
  	  T)  	TYPE=$OPTARG
  	    ;;
  	  u)	USER=$OPTARG
  		;;
      v)	VERSION=$OPTARG
    	;;	
  	  y)	PRETEND=false
  		;;
      z)	COMPRESSION=true
	   ;;
      Z)	COMPRESSION=true
			CALG='bzip2'
			CALG_EXT='bz2'
			CALG_OPT='-j'
	   ;;
	  x)	XBSTREAM=true
	   ;;
	  O)	XOPTIONS=$OPTARG
	   ;;
  	h|?)  	printf "Usage: %s -a ACTION [-u USER] [-p PASSWORD] [-b BIN_DIR] [-c CONFIG_FILE] [-k KEEP] [-l LIFE] [-m MEMORY] [-o OUTPUT_DIR] [-r RESTORE_DIR] [-t TMP_DIR] [-T TYPE] [-v VERSION] [-h] [-y]\n" $(basename $0) >&2
  		echo 'Options:'
  		echo '     -h             display help'
  		echo '     -a ACTION      specify operation to perform (backup|restore)'
  		echo '     -u USER        set MySQL user (if backing up)'
  		echo '     -p PASSWORD    set MySQL user password (if backing up)'
  		echo '     -b BIN_DIR     specify the directory where binaries reside (/usr/bin by default)'
  		echo '     -c CONFIG_FILE specify the MySQL config file path (/etc/mysql/my.cnf by default)'
  		echo '     -k KEEP        specify the number of full backups (and its incrementals) to keep (1 by default)'
  		echo '     -l LIFE        specify the lifetime of the latest full backup in seconds (86400 by default)'
  		echo '     -m MEMORY      specify the amount of memory to use when preparing the backup (1024M by default)'
  		echo '     -o OUTPUT_DIR  specify the backups base directory (/backup by default)'
  		echo '     -r RESTORE_DIR specify the backups directory to restore'
  		echo '     -t TMP_DIR     specify the temporary directory path (/tmp by default)'
  		echo '     -T TYPE        specify the backup type (full|incremental|auto)'
  		echo '     -v VERSION     specify innobackupex version to use (1.5.1 by default)'
  		echo '     -y             enable commit mode (otherwise will only perform dry-run)'
	    echo '	   -z use gzip compression'
	    echo '     -Z use bzip2 compression'
	    echo '     -x use xbstream '
	    echo '     -O OPTIONS for innobackupex --galera-info --parallel=4 --compress --compress-threads=4'
  		exit 1
      ;;
	esac
done
shift $(($OPTIND - 1))

# Lets populate variables with default values if none where supplied
# through commandline options
BIN_DIR=${BIN_DIR:-/usr/bin}
VERSION=${VERSION:-1.5.1}

# More variables
INNOBACKUPEX=innobackupex-${VERSION}
USEROPTIONS="--user=${USER} --password=${PASSWORD}"
TMPFILE="${TMP_DIR:-/tmp}/innobackupex-${ACTION}.$$.tmp"
TYPE="${TYPE:-auto}"
MYCNF=${CONFIG_FILE:-/etc/my.cnf}
INNOBACKUPEXFULL=${BIN_DIR}/$INNOBACKUPEX
MYSQL=${BIN_DIR}/mysql
MYSQLADMIN=${BIN_DIR}/mysqladmin
BACKUPDIR=${OUTPUT_DIR:-/mnt/backups} # Backups base directory
FULLBACKUPDIR=$BACKUPDIR/full # Full backups directory
INCRBACKUPDIR=$BACKUPDIR/incr # Incremental backups directory
FULLBACKUPLIFE=${LIFE:-86400} # Lifetime of the latest full backup in seconds
KEEP=${KEEP:-1} # Number of full backups (and its incrementals) to keep
MEMORY=${MEMORY:-1024M} # Amount of memory to use when preparing the backup

debug "ACTION: ${ACTION}"
debug "INNOBACKUPEX: ${INNOBACKUPEX}"
debug "USEROPTIONS: ${USEROPTIONS}"
debug "TYPE: ${TYPE}"
debug "TMPFILE: ${TMPFILE}"
debug "MYCNF: ${MYCNF}"
debug "INNOBACKUPEXFULL: ${INNOBACKUPEXFULL}"
debug "MYSQL: ${MYSQL}"
debug "MYSQLADMIN: ${MYSQLADMIN}"
debug "BACKUPDIR: ${BACKUPDIR}"
debug "FULLBACKUPDIR: ${FULLBACKUPDIR}"
debug "INCRBACKUPDIR: ${INCRBACKUPDIR}"
debug "RESTORE_DIR: ${RESTORE_DIR}"
debug "FULLBACKUPLIFE: ${FULLBACKUPLIFE}"
debug "KEEP: ${KEEP}"
debug "MEMORY: ${MEMORY}"

# Validate input
validate

# Execute the requested action
eval $ACTION