mysql-backup/mysqlbackup.sh

#!/bin/bash
## author      : Dryusdan
## date        : 15/02/2020
## description : A MySQL dumper
## usage       : ./mysqlbackup.sh /BASE/BACKUP/FOLDER RETENTION USERNAME PASSWORD HOST HEALTCHECK_UUID

## Bash strict mode ####################################
set -o errexit   # abort on nonzero exitstatus
set -o pipefail  # don't hide errors within pipes

#set -o nounset   # abort on unbound variable

## Logs ################################################
readonly SCRIPTNAME="$(basename "$0")"
info()    { echo -e "[INFO] $* "    | logger --tag "${SCRIPTNAME}" ; }
warning() { echo -e "[WARNING] $* " | logger --tag "${SCRIPTNAME}" ; }
error()   { echo -e "[ERROR] $* "   | logger --tag "${SCRIPTNAME}" ; }
fatal()   { echo -e "[FATAL] $* "   | logger --tag "${SCRIPTNAME}" ; exit 1 ; }
########################################################

## Define variables ###################################
info "Define variables"
DATE=$(date '+%s')

info "Source ${HOME}/.env"
source "${HOME}/.env"
info "Sourced"

function cleanup()
{
    sudo /usr/bin/umount ${HOME}/cifs/
}

trap cleanup EXIT

sudo /usr/bin/mount -t cifs  -o seal,user="${CIFSUSER}",pass="${CIFSPASSWD}",uid=$(id -u),gid=$(id -g),forceuid,forcegid, //"${CIFSUSER}.your-storagebox.de"/"${CIFSUSER}" ${HOME}/cifs/

if [ $# -eq 0 ]
then
	fatal "No arguments supplied. Usage : ./mysqlbackup.sh /BASE/BACKUP/FOLDER RETENTION USERNAME PASSWORD HOST PORT"
fi

if [ -z "${1}" ];
then
	fatal "Folder is not defined. Usage : ./mysqlbackup.sh /BASE/BACKUP/FOLDER RETENTION USERNAME PASSWORD HOST PORT"
else
	FOLDER="${1}/${DATE}"
	FOLDER_WITHOUT_DATE="${1}/"
fi

if [ -z "${2}" ];
then
	fatal "Retention is not defined. Usage : ./mysqlbackup.sh /BASE/BACKUP/FOLDER RETENTION USERNAME PASSWORD HOST PORT"
else
	RETENTION="${2}"
fi

if [ -z "${3}" ];
then
	fatal "Username is not defined. Usage : ./mysqlbackup.sh /BASE/BACKUP/FOLDER RETENTION USERNAME PASSWORD HOST PORT"
else
	USERNAME=${3}
fi

if [ -z "${4}" ];
then
	fatal "Password is not defined. Usage : ./mysqlbackup.sh /BASE/BACKUP/FOLDER RETENTION USERNAME PASSWORD HOST PORT"
else
	PASSWORD=${4}
fi

if [ -z "${5}" ];
then
	warning "Host not defined, use 127.0.0.1 by default"
	HOST="127.0.0.1"
else
	HOST=${5}
fi

if [ -z "${6}" ];
then
	warning "Port not defined, use 3306 by default"
	PORT="3306"
else
	PORT=${6}
fi

if [ -z "${7}" ];
then
    HC=false
else
    HC=true
    HC_UUID="${7}"
fi

## Run dump ######################################
info "Create backup folder"
mkdir -p ${FOLDER}/{databases,schemas,datas,extras}/
info "Dumping databases"
curl --silent --retry 3 "https://cron.dryusdan.net/ping/${HC_UUID}/start" > /dev/null
for dbname in $(/usr/bin/mysql --user=${USERNAME} --password=${PASSWORD} --host=${HOST} --port=${PORT} -N -e "show databases" | grep -v "information_schema" | grep -v "mysql" | grep -v "performance_schema")
do
	if ! mountpoint -q -- "${FOLDER_WITHOUT_DATE}"; then
		curl --silent --retry 3 "https://cron.dryusdan.net/ping/${HC_UUID}/fail" > /dev/null
		fatal "Mountpoint not mounted"
	fi
	info "Dumping ${dbname}"
	mkdir -p ${FOLDER}/{schemas,datas}/${dbname}
	info "Dumping ${dbname} schema"
	/usr/bin/mysqldump --user=${USERNAME} --password=${PASSWORD} --host=${HOST} --port=${PORT} --no-data ${dbname} | gzip > ${FOLDER}/databases/${dbname}.sql.gz
	info "Dumping events, routines, triggers of ${dbname}"
	/usr/bin/mysqldump --user=${USERNAME} --password=${PASSWORD} --host=${HOST} --port=${PORT} --no-data --no-create-info --routines --triggers --events ${dbname} | gzip > ${FOLDER}/extras/${dbname}.sql.gz
	info "Know engine of database"
	## If all tables use InnoDB engine, we use --single-transaction
	## Also, if one or more table use MyISAM engine, we need lock all tables
	if /usr/bin/mysql --user=${USERNAME} --password=${PASSWORD} --host=${HOST} --port=${PORT} -N -e "select engine from information_schema.tables where table_schema = '${dbname}'" | grep "MyISAM" -q;
	then
		warning "${dbname} have a table who using MyISAM engine."
		info "Dumping with lock"
		for tablename in $(/usr/bin/mysql --user=${USERNAME} --password=${PASSWORD} --host=${HOST} --port=${PORT} -N -e "show tables" ${dbname})
		do
			info "Dumping ${tablename}'s schema of ${dbname}"
			/usr/bin/mysqldump --user=${USERNAME} --password=${PASSWORD} --host=${HOST} --port=${PORT} --no-data --add-locks --add-drop-table  ${dbname} ${tablename} | sed 's/ AUTO_INCREMENT=[0-9]*\b//g' | gzip > ${FOLDER}/schemas/${dbname}/${tablename}.sql.gz
			info "Dumping ${tablename}'s data of ${dbname}"
			/usr/bin/mysqldump --user=${USERNAME} --password=${PASSWORD} --host=${HOST} --port=${PORT} --no-create-info --add-locks --extended-insert=FALSE  ${dbname} ${tablename} | gzip > ${FOLDER}/datas/${dbname}/${tablename}.sql.gz
		done
	else
		info "Dumping with single transaction"
		for tablename in $(/usr/bin/mysql --user=${USERNAME} --password=${PASSWORD} --host=${HOST} --port=${PORT} -N -e "show tables" ${dbname})
		do
			info "Dumping ${tablename}'s schema of ${dbname}"
			/usr/bin/mysqldump --user=${USERNAME} --password=${PASSWORD} --host=${HOST} --port=${PORT} --no-data --skip-lock-tables --add-drop-table ${dbname} ${tablename} | sed 's/ AUTO_INCREMENT=[0-9]*\b//g' | gzip > ${FOLDER}/schemas/${dbname}/${tablename}.sql.gz
			info "Dumping ${tablename}'s data of ${dbname}"
			/usr/bin/mysqldump --user=${USERNAME} --password=${PASSWORD} --host=${HOST} --port=${PORT} --no-create-info --skip-lock-tables --single-transaction --extended-insert=FALSE  ${dbname} ${tablename} | gzip > ${FOLDER}/datas/${dbname}/${tablename}.sql.gz
		done
	fi
done
info "Backup is done"
curl --silent --retry 3 "https://cron.dryusdan.net/ping/${HC_UUID}/" > /dev/null
info "Removing old backup"
find ${FOLDER_WITHOUT_DATE} -mtime +${RETENTION} -exec rm -rf {} \;
info "Removing done"