#!/bin/sh

# Works as is on Ubuntu/Debian since it relies on the presence of
# /etc/mysql/debian.cnf for access credentials. For other OSes, a
# credential file in the invoking user's home (ex /root/.my.cnf)
# will avoid the password prompt.
#
# Author: Simon Deziel

set -eu

# Credential file (optional)
CRED_FILE="/etc/mysql/debian.cnf"
# Backup directory
BACKUPDIR=/var/backups/mysql
# Group owner
GRP_OWNER="backup"
if ! getent group "$GRP_OWNER" >/dev/null; then
  GRP_OWNER="root"
fi
# Mode
MODE="0640"
# MySQL options used to list existing DBs
OPTS="--batch --silent"
# MySQL admin options used to ping the server
ADMIN_OPTS=""
# MySQL dump options to perform the DB dumps
DUMP_OPTS="--single-transaction --lock-tables=false --quick --events"
# DBs to exclude from the backup (grep regex)
SKIP_BACKUP_LIST="^(information_schema|performance_schema|test)$"
# Numbers of days to keep the local backups
KEEP_DAYS="1"
# Binaries paths
MYSQL="$(which mysql || true)"
MYSQLADMIN="$(which mysqladmin || true)"
MYSQLDUMP="$(which mysqldump || true)"

# Check if the MySQL binaries are present
[ -n "$MYSQL" ]      && [ -x "$MYSQL" ]      || exit 0
[ -n "$MYSQLADMIN" ] && [ -x "$MYSQLADMIN" ] || exit 0
[ -n "$MYSQLDUMP" ]  && [ -x "$MYSQLDUMP" ]  || exit 0

# Create the backup directory
if [ ! -d "$BACKUPDIR" ]; then
  mkdir -p "$BACKUPDIR"
fi

# Avoid taking all the CPU
renice +19 -p "$$" > /dev/null

# Get the password to connect to MySQL
if [ -r "$CRED_FILE" ]; then
  OPTS="--defaults-extra-file=$CRED_FILE $OPTS"
  ADMIN_OPTS="--defaults-extra-file=$CRED_FILE $ADMIN_OPTS"
  DUMP_OPTS="--defaults-extra-file=$CRED_FILE $DUMP_OPTS"
fi

# Is the server running ?
# shellcheck disable=SC2086
"$MYSQLADMIN" $ADMIN_OPTS ping 2>&1 | grep -qx 'mysqld is alive' || exit 0

# Get the database names to backup
# shellcheck disable=SC2086
DATABASES="$("$MYSQL" $OPTS -e 'show databases;' | grep -vE "$SKIP_BACKUP_LIST")"

# Dump each database and compress it
for DATABASE in $DATABASES; do
  DATE="$(date "+%Y%m%d%H%M")"
  DST_FILE="$BACKUPDIR/$DATABASE-$DATE.sql.gz"
  touch "$DST_FILE"
  chmod "$MODE" "$DST_FILE"
  chgrp "$GRP_OWNER" "$DST_FILE"
  # shellcheck disable=SC2086
  "$MYSQLDUMP" $DUMP_OPTS "$DATABASE" | gzip > "$DST_FILE"

  # clean older backups of that DB
  OLDER_DUMPS="$DATABASE-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9].sql.gz"
  find "$BACKUPDIR" -maxdepth 1 -type f -mtime +"$KEEP_DAYS" -delete -name "$OLDER_DUMPS"
done

# Delete old backups
find "$BACKUPDIR" -maxdepth 1 -type f -mtime +"$KEEP_DAYS" -delete