Simple script for sql databases backup.

sql-backup.sh

#!/bin/bash

# Simple script for sql databases backup.
# written by Gian Luca Vagnuzzi <vagnu00@gmx.com>
#
# ==============================
# USAGE:
# 1. Put script in /usr/local/bin & make executale
# 2. Create the envs file: '$HOME/.sql-backupEnvs'.
# Content:
#
# MYSQL_HOST="127.0.0.1"
# MYSQL_USER="root"
# MYSQL_PORT="3306"
# MYSQL_PWD="rootpass"
# DIR_BACKUP="/srv/sql-backup"
# KEEP_DAYS="20" # backup to keep
#
# The script will automatically read this file.
#
# 3. Set envs file with 0600 perms: chmod 0600 $HOME/.sql-backupEnvs
# 4. Run it or place in crontab
# ==============================

required_cmds="mysqldump nc mail"
MYSQL_SECRET_FILE="/root/.data.cnf"
SCRIPT_ENVS_FILE="$HOME/.sql-backupEnvs"
: ${backup_result:=ERR}

# Load variables
if [ ! -f $SCRIPT_ENVS_FILE ]; then
 echo "Error: $SCRIPT_ENVS_FILE not found. See the instructions in the comment at the top."
 exit 1
else
source $SCRIPT_ENVS_FILE
fi

function chk_required_cmds {
 # verify if all commands are installed in the system paths
 for i in $@; do
  which "$i" >/dev/null 2>&1
  [ $? -ne 0 ] && echo "WARNING: the command '$i' doesn't exist, please install it and retry!" && exit 1
 done
}

function fn_main {
# run check of needed packages
chk_required_cmds $required_cmds

# check service
nc -z $MYSQL_HOST $MYSQL_PORT
[ $? != 0 ] && echo "WARNING: MySQL service seems down!" && exit 1

# make dest dir if not present
[ ! -e ${DIR_BACKUP} ] && mkdir -p ${DIR_BACKUP}

# make password file
echo "[mysqldump]
password=${MYSQL_PWD}" > ${MYSQL_SECRET_FILE}

# make backup
mysqldump --defaults-file=$MYSQL_SECRET_FILE --all-databases --compress -h $MYSQL_HOST -u $MYSQL_USER -P $MYSQL_PORT > $DIR_BACKUP/db-all-"$(date +%d)".sql

# if backup result is OK... send "Success" email.
[ $? = 0 ] && backup_result="OK" && echo "Backup SQL $(hostname) OK" | mail -s "[Success] $(hostname) - SQL Backup" root

# count nr of backup present
OLD_BACKUP_NR=$(ls -1 $DIR_BACKUP/ | wc -l)

# If backup OK and nr old backup is more than KEEP_DAYS value, apply backup retentions rules.
# If not, doesn't remove any older backup & send "Failed" email.
if [ $backup_result = OK ] && [ $OLD_BACKUP_NR -gt $KEEP_DAYS ]; then
 ls -1tr $DIR_BACKUP/ | head -n -${KEEP_DAYS} | xargs -I{} -d '\n' rm $DIR_BACKUP/{}
elif [ $backup_result = ERR ]; then
 echo "Backup SQL $(hostname) FAILED" | mail -s "[Failed] $(hostname) - SQL Backup" root
fi
}

fn_main