From c008a81884dae827388d1d7be5ff09ebb92515a4 Mon Sep 17 00:00:00 2001 From: Harald Pfeiffer Date: Fri, 10 Jan 2020 16:08:31 +0100 Subject: small script for SQL backups, on-demand quality --- bin/sqlbackup | 210 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 210 insertions(+) create mode 100755 bin/sqlbackup 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 +# 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" -- cgit v1.2.3