#!/usr/bin/env bash

#################################################################################
# AUTHOR: Harald Pfeiffer <coding@lirion.de>
# LICENSE: LGPLv3
# Version: 0.1
#
# Small aka. rather quick and dirty script to backup your SQL databases.
#
# What this script does is looking for existing databases and backup all of them.
# The result will be sql.xz files inside /tmp/sqlbackup.XXXXXX.
#
# TO-DOS:
# 1. Create possibilities to define the following through CLI options:
#    --- DONE --- 1.1. MySQL user, and password command(!)
#    1.2. DB exclusion regex
#    1.3. Location of configuration file or log
#################################################################################

function help {
	printf "USAGE: %b [-h] [-l] [-b DIR] [-u USER] [-p PASSWORD]\n\n" "$(basename "$0")"
	printf -- "-h:\tThis help\n"
	printf -- "-b:\tBackup target directory\n"
	printf -- "-l:\tLog to syslog\n"
	printf -- "-p:\tPassword string for MySQL user (hint: use a command for this!)\n"
	printf -- "-u:\tUser in MySQL designated for backups\n"
}
function mlogger {
	[ -z "$2" ]&&return 1
	[ -z "$1" ]&&return 1
	logger -t "sqlbackup" -p "local3.""$1" "$2"
}
function bstatus {
	case "$CURDB" in
		"") printf "Status: Preparing backup.\n";;
		*) printf "Status: Backing up %b.\n" "$CURDB";;
	esac
}
function bkillme {
	printf "\n[CRIT] Got a termination signal, committing sudoku...\n"
	[ ! -z "$LOGGER" ]&&[ "$LOGGER" -eq 1 ]&&mlogger warning "Terminating on external request"
	exit 130
}
trap bkillme SIGINT
trap bkillme SIGTERM
trap bstatus USR1

declare -x BUSER BDIR DBFDIR LOGGER CURDB SCHANAUZE=0 PRMERR=0
while getopts :hld:u:p:qb: SHOPT;do
	case "$SHOPT" in
		h) help;exit 0;;
		l) LOGGER=1;;
		b) BDIR="${OPTARG}";;
		d) DBFDIR="${OPTARG}";;
		u) BUSER="${OPTARG}";;
		p) BPW="${OPTARG}";;
		q) SCHANAUZE=1;;
		*)
			help
			printf "\n[    ]\033[s%b Not an option or empty parameter: -%b\n" \
				"$NEE" "$OPTARG" >&2
			#[ ! -z "$LOGGER" ]&&[ "$LOGGER" -eq 1 ]&&\
				mlogger err "Not an option or empty parameter: -$OPTARG"
			PRMERR=$((PRMERR+1))
		;;
	esac
done
shift $((OPTIND-1))
[ "$PRMERR" -gt 0 ]&&exit 1

# Leave this line alone - location of script
MYDIR="$(cd "$(dirname "$0")"&&pwd)"

# The backup directory. The line here means we will create /tmp/sqlbackup.XXXXXX
# and backup there. If you want another value, enter this here.
#BDIR="$MYDIR/files"
if [ -z "$BDIR" ];then
	BDIR="$(mktemp -d /tmp/sqlbackup.XXXXXX)"||exit 187
fi

# The backup user:
[ -z "$BUSER" ]&&BUSER="tVY8MwA7cTEm"

# Password file. For now, this is unencrypted, so please make this file secure!
# Expects just the password, one line. Also, you will want to use a dedicated
# user for that:
# GRANT SELECT, SHOW DATABASES, LOCK TABLES ON *.* TO 'backupheiner'@'localhost';

#PWFL="$MYDIR/pw"
if [ -z "$BPW" ];then
	PWFL="/etc/sqlbackup/pw"
	if [ ! -r "$PWFL" ];then
		help
		printf "\n[    ]\033[s%b %b not readable, no password supplied!\n" "$NEE" "$PWFL" >&2
		[ ! -z "$LOGGER" ]&&[ "$LOGGER" -eq 1 ]&&\
			mlogger err "$PWFL not readable, no password supplied"
		exit 1
	fi
fi

# Location of the mysql/maria db files, default: /var/lib/mysql. No trailing slashes.
[ -z "$DBFDIR" ]&&DBFDIR="/var/lib/mysql"

# Databases to be excluded. Meant to work with grep -P, i.e. perl regular expressions.
# The minimum is to separate any database with the pipe character (|).
# Caution: an empty variable will backup nothing.
#
# The default line excludes the MySQL internal databases:
#NODB="^information_schema$|^mysql$|^performance_schema$"
# Very offensive backup, meaning even internals - should exclude information_schema:
NODB="^information_schema$|^performance_schema$"

#########
# MYSQL #
#########
DUMPOPTS=( "--add-drop-table" "--add-locks" )
DUMPOPTS+=( "--complete-insert" "--create-options" "--max_allowed_packet=512M" )
# Flush on backup of every subordinate db would create too many new files.
# Hence, commented out.
#DUMPOPTS+=( "--flush-logs" )
ISAM_DUMPOPTS=( ${DUMPOPTS[@]} "--lock-tables" )
INNO_DUMPOPTS=( ${DUMPOPTS[@]} "--single-transaction" )

############
# RET VERB #
############
OGE="\033[666D[ \033[0;32mOK \033[0m]\033[u\033[K"
NEE="\033[666D[\033[0;31mFAIL\033[0m]\033[u\033[K"
WAT="\033[666D[\033[0;33mWARN\033[0m]\033[u\033[K"

[ -z "$BDIR" ]&&printf "No backup directory specified!" >&2&&exit 191
mkdir -p "$BDIR"&&chmod 0700 "$BDIR"||exit 188
BLOG="$BDIR""/sqlbackup.log"
touch "$BLOG"||exit 189
# check normal write exit explicitly before first write to ensure there is no
# "backyard" file lock like stale mounts or whatever
true >> "$BLOG"||exit 189
chmod 0600 "$BLOG"||exit 190


#########


TIME="$(date +"%Y-%m-%dT%H:%M:%S%z")"
[ "$SCHANAUZE" -ne 1 ]&&printf "File timestamp: %s\n" "$TIME"
printf "File timestamp: %s\n" "$TIME" >> "$BLOG"
# Flush prior to backups, do not purge.
[ "$SCHANAUZE" -ne 1 ]&&printf "\n[....] Flushing logs.\033[s..\033[0m"
[ ! -z "$LOGGER" ]&&[ "$LOGGER" -eq 1 ]&&mlogger info "flushing logs"
mysql --user="$BUSER" --password="$(cat $PWFL)" -e "flush logs \\G" 2>> "$BLOG"
if [ "$?" -ne 0 ];then
	[ ! -z "$LOGGER" ]&&[ "$LOGGER" -eq 1 ]&&mlogger err "flushing logs failed, check $BLOG"
	[ "$SCHANAUZE" -ne 1 ]&&printf "%b\n\n" "$NEE"
	exit 101
else
	[ "$SCHANAUZE" -ne 1 ]&&printf "%b\n\n" "$OGE"
fi
LOGPOS="$(mysql --user="$BUSER" --password="$(cat $PWFL)" -e "show master status \G"|grep Position|awk '{print $NF}')"
[ "$SCHANAUZE" -ne 1 ]&&printf "Log position: %s\n" "$LOGPOS"
printf "Log position: %s\n" "$LOGPOS" >> "$BLOG"
# Get latest non-relay binary log:
find "$DBFDIR" -maxdepth 1 -cmin 2 >/dev/null 2>&1
if [ "$?" -ne 0 ];then
	[ "$SCHANAUZE" -ne 1 ]&&printf "[    ]\033[s%b Failed to read %b for binary logs.\n" "$NEE" "$DBFDIR"
	[ ! -z "$LOGGER" ]&&[ "$LOGGER" -eq 1 ]&&mlogger err "Failed to read $DBFDIR for binary logs."
	exit 127
fi
BINLOGF="$(find "$DBFDIR" -maxdepth 1 -nowarn -type f -regextype posix-extended -regex ".*-bin\.[0-9]+$"|grep -v relay-bin|awk -F\. '{print $NF,$0}'|sort -n|cut -f2- -d' '|tail -1)"
BINLOGF="$(basename "$BINLOGF")"
[ -z "$BINLOGF" ]&&exit 127
DBARR=( $(mysql -u "$BUSER" --password="$(cat $PWFL)" -e "show databases \\G"|grep ^Data|awk '{print $NF}'|grep -vP "$NODB") )
j=1
[ "$SCHANAUZE" -ne 1 ]&&printf "\nDatabases being backed up\n======================\n"
[ "$SCHANAUZE" -ne 1 ]&&for i in "${DBARR[@]}";do
	echo -n "$i"
	case "$(( j % 3 ))" in
		0) echo " " ;;
		*) echo -n ";";;
	esac
	j="$((j+1))"
done|column -ts\;
[ "$SCHANAUZE" -ne 1 ]&&printf "\n"
[ "$SCHANAUZE" -ne 1 ]&&printf "Current binary log: %b\n\n" "$BINLOGF"
for i in "${DBARR[@]}";do
	CURDB="$i"
	[ "$SCHANAUZE" -ne 1 ]&&printf "[....] Backing up database %s.\033[s..\033[0m" "$i"
	[ ! -z "$LOGGER" ]&&[ "$LOGGER" -eq 1 ]&&mlogger info "backing up database $i"
	sleep 0.1337
	[ "$SCHANAUZE" -ne 1 ]&&printf ".. \033[u\033[K \033[1;30mdump running...\033[0m"
		if ( [ "$i" == "mysql" ] || [ "$i" == "information_schema" ] || [ "$i" == "performance_schema" ] );then
			mysqldump "${ISAM_DUMPOPTS[@]}" -u "$BUSER" --password="$(cat "$PWFL")" --databases "$i" > "$BDIR/$i-$TIME.sql" 2>> "$BLOG"
		else
			mysqldump "${INNO_DUMPOPTS[@]}" -u "$BUSER" --password="$(cat "$PWFL")" --databases "$i" > "$BDIR/$i-$TIME.sql" 2>> "$BLOG"
		fi
	if [ "$?" -ne 0 ];then
		[ ! -z "$LOGGER" ]&&[ "$LOGGER" -eq 1 ]&&mlogger err "backup of $i failed, check $BLOG"
		[ "$SCHANAUZE" -ne 1 ]&&printf "%b\n" "$NEE"
		[ ! -z "$LOGGER" ]&&[ "$LOGGER" -eq 1 ]&&mlogger err "backup failed, check $BLOG"
		exit 2
	fi
	sleep 0.1337
	[ "$SCHANAUZE" -ne 1 ]&&printf ".. \033[u\033[K \033[1;30mcompression running...\033[0m"
	xz "$BDIR/$i-$TIME.sql"&&chmod 0600 "$BDIR/$i-$TIME.sql.xz"
	if [ "$?" -ne 0 ];then
		[ "$SCHANAUZE" -ne 1 ]&&printf "%b\n" "$NEE"
		[ ! -z "$LOGGER" ]&&[ "$LOGGER" -eq 1 ]&&mlogger err "compression failed, check $BLOG"
		exit 3
	fi
	sleep 0.1337
	echo -e "$OGE"
done
[ "$SCHANAUZE" -ne 1 ]&&printf "[....] Purging binary logs.\033[s..\033[0m"
[ ! -z "$LOGGER" ]&&[ "$LOGGER" -eq 1 ]&&mlogger info "Purging binary logs..."
mysql --user="$BUSER" --password="$(cat $PWFL)" -e "purge binary logs to \"$BINLOGF\" \\G" 2>> "$BLOG"
if [ "$?" -ne 0 ];then
	[ "$SCHANAUZE" -ne 1 ]&&printf "%b\n" "$NEE"
	[ ! -z "$LOGGER" ]&&[ "$LOGGER" -eq 1 ]&&mlogger err "purging of binary logs failed, check $BLOG"
	exit 3
fi
sleep 0.1337
[ "$SCHANAUZE" -ne 1 ]&&printf "%b\n" "$OGE"
[ ! -z "$LOGGER" ]&&[ "$LOGGER" -eq 1 ]&&mlogger info "sqlbackup finished."