Backup script using wsrep_desync and mysqldump for MariaDB/MySQL+Galera clusters

mariadb-backup.sh

#!/bin/bash

# Usage:
#   [BACKUP_DIR=/var/backups] \
#   [RETENTION=30] \
#   [WSREP_DESYNC=true] \
#   ./mariadb-backup.sh <hostname> <database> <user> <password> [<port>]

DB_HOST=${1}
DB_NAME=${2}
DB_USER=${3}
DB_PASSWORD=${4}
DB_PORT=${5}

if [ -z "$BACKUP_DIR" ]; then
  BACKUP_DIR="/var/backups/mariadb-backup"
fi

if [ -z "$DB_PORT" ]; then
  DB_PORT=3306
fi

FILE=${BACKUP_DIR}/${DB_HOST}-`echo $DB_NAME | tr -d "-"`-`date +"%Y-%m-%d_%H%M%S"`.sql.gz
mkdir -p ${BACKUP_DIR}
echo "Backing up to ${FILE}"

wsrep_desync()
{
  if [ "$WSREP_DESYNC" == "true" ]; then
    echo "Setting wsrep_desync=${1}"
    echo "SET GLOBAL wsrep_desync=${1};" \
      | mysql --host=$DB_HOST --port=$DB_PORT --user=$DB_USER --password=$DB_PASSWORD
  fi
}

# this needs subsequent connections to hit the same backend
# server for this to be effective and not break things
wsrep_desync "ON"
mysqldump \
  --port=$DB_PORT \
  --flush-privileges \
  --single-transaction \
  --host=$DB_HOST \
  --user=$DB_USER \
  --password=$DB_PASSWORD \
  $DB_NAME \
  | gzip > ${FILE}
STATUS=$?
wsrep_desync "OFF" || exit 1

# exit script if any command fails, so we don't
# delete old backups if they start failing
if [ $STATUS -ne 0 ]; then
  exit $STATUS
fi

# tidy up old backups according to retention
if [ -n "$RETENTION" ]; then
  echo "Removing backups older than ${RETENTION} days according to retention"
  find "${BACKUP_DIR}" \
    -name "${DB_HOST}-`echo $DB_NAME | tr -d \"-\"`-*.sql.gz" \
    -type f \
    -mtime +${RETENTION} \
    -delete
fi