Script to create full/incremental backups with Percona xtrabackup

xtradbkp.sh

#!/usr/bin/env bash

# based on jmrfederico script
# https://gist.github.com/jmfederico/1495347

set -eo pipefail

function debug() {
  # shellcheck disable=SC2015
  [[ -n $DEBUG ]] && printf "[%s] %s\n" "$(date -Im)" "$@" >&2 || /bin/true
}

BACK_DIR=/backups/DB/MySQL
# USEROPTIONS="--user=${MYSQL_USER} --password=${MYSQL_PASSWORD} --host=${MYSQL_HOST}"
#
BASEBACK_DIR="${BACK_DIR}/base"
CURRENT_TS=$(date +%s)
TODAY=$(date --iso-8601)
INCRBACK_DIR="${BACK_DIR}/incr"
FULL_BACKUP_CYCLE=604800  # 7 days
LOG_FILE=$(mktemp --tmpdir=/tmp percona_xtrabackup.XXXXXX.log)
KEEP=1

[[ ! -d $BASEBACK_DIR ]] && mkdir -vp $BASEBACK_DIR

# mysqladmin ${USEROPTIONS} ping > /dev/null
mysqladmin ping > /dev/null

LATEST_FULL=$(find ${BASEBACK_DIR} -mindepth 1 -maxdepth 1 -type d -printf "%P\n" | sort -nr | head -1)
LATEST_AGE=$(stat -c "%Y" "${BASEBACK_DIR}/${LATEST_FULL}")

if [[ $((LATEST_AGE + FULL_BACKUP_CYCLE )) -lt ${CURRENT_TS} ]]; then
  debug 'New full backup'
  TARGET_DIR="${BASEBACK_DIR}/${TODAY}"
  # /usr/bin/xtrabackup --backup $USEROPTIONS --target-dir="${TARGET_DIR}" > "${LOG_FILE}" 2>&1
  /usr/bin/xtrabackup --backup --target-dir="${TARGET_DIR}" > "${LOG_FILE}" 2>&1
else
  debug 'New incremental backup'
  [[ -d "${INCRBACK_DIR}/${LATEST_FULL}" ]] || mkdir -vp "${INCRBACK_DIR}/${LATEST_FULL}" > "${LOG_FILE}" 2>&1
  TARGET_DIR="${INCRBACK_DIR}/${LATEST_FULL}/${TODAY}"
  # /usr/bin/xtrabackup --backup $USEROPTIONS \
  /usr/bin/xtrabackup --backup \
    --target-dir="${TARGET_DIR}" \
    --incremental-basedir="${BASEBACK_DIR}/${LATEST_FULL}" \
  > "${LOG_FILE}" 2>&1
fi

if [ "$(tail -1 "${LOG_FILE}" | grep -c 'completed OK!' )" -eq 0 ]; then
  debug "xtrabackup failed"
  if [[ -n $DEBUG ]]; then
    debug "--- START ERROR OUTPUT from xtrabackup ---"
    cat "${LOG_FILE}"
    debug "--- END ERROR OUTPUT from xtrabackup ---"
  fi
  rm -f "${LOG_FILE}"
  exit 129
fi

THIS_BACKUP=$(gawk '/Backup created in directory/ {split($0,p);print p[10]}' "${LOG_FILE}")
debug "Databases backed up successfully to: $THIS_BACKUP"

MINS=$((FULL_BACKUP_CYCLE * (KEEP + 1) / 60))
debug "Cleaning up old backups (older than $MINS minutes) and temporary files"

# Delete old backups
while IFS='' read -r -d '' DEL_DATE; do
  echo "deleting ${DEL_DATE}"
  rm -rf "${BASEBACK_DIR:?}/${DEL_DATE}"
  rm -rf "${INCRBACK_DIR:?}/${DEL_DATE}"
done < <(find "${BASEBACK_DIR}" -mindepth 1 -maxdepth 1 -type d -mmin +${MINS} -printf "%P\0")

rm -f "${LOG_FILE}"

debug "took $((($(date +%s) - CURRENT_TS) / 60)) minutes"
debug "$(basename "$0") completed"

exit 0