small script for SQL backups, on-demand quality

1 files changed, 210 insertions, 0 deletions

diff --git a/bin/sqlbackup b/bin/sqlbackup
new file mode 100755
index 0000000..890f2f6
--- /dev/null
+++ b/bin/sqlbackup
@@ -0,0 +1,210 @@
+#!/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 SIGKILL
+trap bkillme SIGTERM
+trap bstatus USR1
+
+declare -x BUSER BDIR DBFDIR LOGGER CURDB SCHANAUZE=0 PRMERR=0
+while getopts :hld:u:p:q 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
+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.
+[ ! -z "$LOGGER" ]&&[ "$LOGGER" -eq 1 ]&&mlogger info "flushing logs"
+mysql --user="$BUSER" --password="$(cat $PWFL)" -e "flush logs \\G"
+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 ]&&echo -e "$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 ]&&echo -e "$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"
+mysql --user="$BUSER" --password="$(cat $PWFL)" -e "purge binary logs to \"$BINLOGF\" \\G"
+if [ "$?" -ne 0 ];then
+	[ "$SCHANAUZE" -ne 1 ]&&echo -e "$NEE"
+	[ ! -z "$LOGGER" ]&&[ "$LOGGER" -eq 1 ]&&mlogger err "purging of binary logs failed, check $BLOG"
+	exit 3
+fi
+sleep 0.1337
+echo -e "$OGE"