Last active
November 13, 2018 17:19
-
-
Save pkutzner/099a32e256fb2fa26016aa227e04a36f to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| #!/bin/bash - | |
| #=============================================================================== | |
| # | |
| # FILE: csb_updated.sh | |
| # | |
| # USAGE: ./csb_updated.sh | |
| # | |
| # DESCRIPTION: Updated version of Client SQL Backup (csb) script. | |
| # | |
| # OPTIONS: --- | |
| # REQUIREMENTS: gzip/bzip2, mysql, ssh, mysqldump, mail/mailx | |
| # BUGS: --- | |
| # NOTES: --- | |
| # AUTHOR: Preston Kutzner (pmk), pkutzner at gmail.com | |
| # Loosely based off of the logic in MySQL Backup Script | |
| # http://sourceforge.net/projects/automysqlbackup | |
| # Copyright (c) 2002-2003 wipe_out@lycos.co.uk | |
| # COMPANY: | |
| # CREATED: 05/28/2010 17:01:25 CDT | |
| # REVISION: 3.3 | |
| # LICENSE: GNU GPLv2 | |
| # | |
| #=============================================================================== | |
| export PATH="/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin" | |
| set -o nounset # Treat unset variables as an error | |
| #------------------------------------------------------------------------------- | |
| # BEGIN User Selectable Options | |
| #------------------------------------------------------------------------------- | |
| CONFDIR="/data/mysql_auto_backup/config" # Default config directory | |
| BACKUPDIR="/data/client_db_backups" # Default backup storage location | |
| MAILADDR="user@example.com" # E-Mail reports to this addr. | |
| LOGTYPE="quiet" # Default logging type, see -help | |
| COMP="bzip2" # Default compression to use | |
| DOWEEKLY=6 # Day of week to perform weekly | |
| # backup. (1=Monday) | |
| # Default options for compression programs | |
| SZ_OPTS="a -an -txz -m0=lzma2 -mx=5 -md32m -mmt=2 -si -so" # 7z options | |
| BZ2_OPTS="-fc" # bzip2 options | |
| GZ_OPTS="-c" # gzip options | |
| # The following options allow us to ssh to hosts without adding them to our known | |
| # hosts file. This also tells SSH to fail with an error if it is unable to | |
| # establish a port forward. | |
| #SSH_OPTS="-oUserKnownHostsFile=/dev/null -oStrictHostKeyChecking=no -oLogLevel=Error" | |
| #SSH_TUN_OPTS="-oExitOnForwardFailure=yes $SSH_OPTS" | |
| SSH_OPTS=( '-oUserKnownHostsFile=/dev/null' | |
| '-oStrictHostKeyChecking=no' | |
| '-oLogLevel=Error' | |
| '-oBatchMode=yes' | |
| '-oConnectTimeout=5' | |
| ) | |
| SSH_TUN_OPTS=( $SSH_OPTS[@] '-oExitOnForwardFailure=yes' ) | |
| # These are switches that get evaluated True/False. Bash/sh/zsh True=0 False=1 | |
| DEBUG=1 | |
| SEPDIR=1 # Use separate directories for | |
| # each database | |
| #------------------------------------------------------------------------------- | |
| # END User Selectable Options | |
| #------------------------------------------------------------------------------- | |
| #------------------------------------------------------------------------------- | |
| # BEGIN Constants | |
| #------------------------------------------------------------------------------- | |
| EXCLUDED=( 'information_schema' 'mysql.event' 'performance_schema' ) # Excluded databases | |
| declare -a EXCLUDE=() | |
| #------------------------------------------------------------------------------- | |
| # END Constants | |
| #------------------------------------------------------------------------------- | |
| #------------------------------------------------------------------------------- | |
| # BEGIN Function Definitions | |
| #------------------------------------------------------------------------------- | |
| #=== FUNCTION ================================================================ | |
| # NAME: usage | |
| # DESCRIPTION: Display script usage to user | |
| # PARAMETERS: None | |
| # RETURNS: Nothing | |
| #=============================================================================== | |
| usage () | |
| { | |
| cat <<EOF | |
| USAGE: | |
| $(basename "$0") [options] | |
| OPTIONS: | |
| -h : Show this help | |
| -v : Debug output (verbose) | |
| -b <path> : Specify output path for backups. | |
| -c <path> : Specify config file path. | |
| -f <file> : Use only specified config file. | |
| -a <address> : Email address to send logs to. | |
| -d <db_name(s)> : Database name, or comma-separated list of databases. | |
| to be backed up. (e.g. "db1,db2,etc.") | |
| -D : Debug | |
| -o <type> : Specify output (stdout|log|both|quiet) | |
| screen - Output to screen only. | |
| log - Output to file, mail when done. | |
| both - log + stdout | |
| quiet - Mail log(s) only if errors reported. | |
| -z <type> : Specify compression type ( bzip2 | gzip | 7zip ) | |
| EOF | |
| exit 0 | |
| } # ---------- end of function usage ---------- | |
| #=== FUNCTION ================================================================ | |
| # NAME: send | |
| # DESCRIPTION: Email contents of log files to $MAILADDR | |
| # PARAMETERS: Logfile as $1 | |
| # RETURNS: Success/Fail | |
| #=============================================================================== | |
| send () | |
| { | |
| mail -s "MySQL backup log for $JOBNO - $DATE" $MAILADDR <"$1" || return 1 | |
| return 0 | |
| } # ---------- end of function send ---------- | |
| # Test SSH connectivity | |
| _testssh() { | |
| $SSH_CMD -qq -oUserKnownHostsFile=/dev/null -oStrictHostKeyChecking=no -oBatchMode=yes -oConnectTimeout=5 -i "${KEYFILE}" -p ${SSH_PORT:-22} ${RUNAME}@${RHOST} "echo 2>&1" && return 0 || return 1 | |
| } | |
| # Check if list contains value | |
| # e.g. contains $MYLIST $MYVALUE | |
| # If MYLIST is an array, it must be passed as string (${MYLIST[*]}) | |
| contains() { | |
| [[ "$1" =~ (^|[[:space:]])"$2"($|[[:space:]]) ]] && return 0 || return 1 | |
| } | |
| #=== FUNCTION ================================================================ | |
| # NAME: startLogging | |
| # DESCRIPTION: Setup logging according to specified log type | |
| # PARAMETERS: None | |
| # RETURNS: Success/Fail | |
| #=============================================================================== | |
| startLogging () | |
| { | |
| # The "${0##*/}" variable reference convention used below gives us the same result | |
| # as doing a `basename $0` but without the expense of having to do a nested subshell | |
| # call for it. | |
| if ! LOGFILE=$(mktemp -q "/tmp/${0##*/}-log.XXXXXX"); then | |
| echo >&2 "[ERROR]: Unable to create temporary log file. (mktemp exited with $?)." | |
| exit 1 | |
| elif ! ERRFILE=$(mktemp -q "/tmp/${0##*/}-err.XXXXXX"); then | |
| echo >&2 "[ERROR]: Unable to create temporary error log. (mktemp exited with $?)." | |
| exit 1 | |
| fi | |
| exec 6>&1 # Attach stdout to FD #6 | |
| exec 7>&2 # Attach stderr to FD #7 | |
| case "$LOGTYPE" in | |
| both) | |
| # Send output both to file and screen using BASH process substitution and tee. | |
| if ! exec > >(tee -a "$LOGFILE"); then # Pipe stdout to file and screen | |
| exec 1>&6 6>&- # Restore stdout | |
| exec 2>&7 7>&- # Restore stderr | |
| echo >&2 "[ERROR]: Unable to redirect stdout." | |
| return 1 | |
| elif ! exec 2> >(tee -a "$ERRFILE"); then # Pipe stderr to file and screen | |
| exec 1>&6 6>&- # Restore stdout | |
| exec 2>&7 7>&- # Restore stderr | |
| echo >&2 "[ERROR]: Unable to redirect stderr." | |
| return 1 | |
| fi | |
| ;; | |
| log|quiet) | |
| if ! exec > "$LOGFILE"; then # Redirect stout to file | |
| exec 1>&6 6>&- # Restore stdout and release FD#6 | |
| exec 2>&7 7>&- # Restore stderr and release FD#7 | |
| echo >&2 "[ERROR]: Unable to redirect stdout." | |
| return 1 | |
| elif ! exec 2> "$ERRFILE"; then # Redirect stderr to file | |
| exec 1>&6 6>&- # Restore stdout and release FD#6 | |
| exec 2>&7 7>&- # Restore stderr and release FD#7 | |
| echo >&2 "[ERROR]: Unable to redirect stderr." | |
| return 1 | |
| fi | |
| ;; | |
| screen) | |
| # Drop through. | |
| ;; | |
| esac | |
| } # ---------- end of function startLogging ---------- | |
| #=== FUNCTION ================================================================ | |
| # NAME: stopLogging | |
| # DESCRIPTION: Stop logging, send logs if neccessary and clean-up. | |
| # PARAMETERS: None | |
| # RETURNS: Success/Fail | |
| #=============================================================================== | |
| stopLogging () | |
| { | |
| case "$LOGTYPE" in | |
| log|both) | |
| exec 1>&6 6>&- # Restore stdout | |
| exec 2>&7 7>&- # Restore stderr | |
| # If the log file is not empty, send it. | |
| if [[ -s "${LOGFILE}" ]]; then | |
| send "${LOGFILE}" || echo >&2 "${0##*/}: Unable to send ${LOGFILE}" | |
| rm -f "${LOGFILE}" | |
| else | |
| rm -f "${LOGFILE}" | |
| fi | |
| # If the error log is non-empty, email it. | |
| if [[ -s "${ERRFILE}" ]]; then | |
| send "${ERRFILE}" || echo >&2 "${0##*/}: Unable to send ${ERRFILE}" | |
| rm -f "${ERRFILE}" | |
| else | |
| rm -f "${ERRFILE}" | |
| fi | |
| ;; | |
| quiet) | |
| exec 1>&6 6>&- # Restore stdout | |
| exec 2>&7 7>&- # Restore stderr | |
| # If the error log is non-empty, email both logs. | |
| if [[ -s "${ERRFILE}" ]]; then | |
| send "${LOGFILE}" || echo >&2 "${0##*/}: Unable to send ${LOGFILE}" | |
| rm -f "${LOGFILE}" | |
| send "${ERRFILE}" || echo >&2 "${0##*/}: Unable to send ${ERRFILE}" | |
| rm -f "${ERRFILE}" | |
| else | |
| rm -f "${LOGFILE}" "${ERRFILE}" | |
| fi | |
| ;; | |
| screen) | |
| # Drop through | |
| ;; | |
| esac | |
| } # ---------- end of function stopLogging ---------- | |
| #=== FUNCTION ================================================================ | |
| # NAME: getDatabases | |
| # DESCRIPTION: Retrieve list of databases from $RHOST | |
| # PARAMETERS: Nothing, uses globals. | |
| # RETURNS: Array of database names. | |
| #=============================================================================== | |
| getDatabases () | |
| { | |
| if ! $SSH_CMD ${SSH_OPTS[*]} -p ${SSH_PORT:-22} -i "$KEYFILE" ${RUNAME}@${RHOST} "/usr/bin/mysql -u $MUNAME -p'$MPASSWD' -s -r -e \"SHOW DATABASES;\" | grep -Ev \"\(information_schema\|performance_schema\)\""; then | |
| echo >&2 "[ERROR]: Unable to retrieve list of databases from $RHOST" | |
| return 1 | |
| fi | |
| return 0 | |
| } # ---------- end of function getDatabases ---------- | |
| #--- FUNCTION ---------------------------------------------------------------- | |
| # NAME: set_compression | |
| # DESCRIPTION: Set compression command-lines and file suffix based on | |
| # selected compression program. | |
| # PARAMETERS: none | |
| # RETURNS: success/fail | |
| #------------------------------------------------------------------------------- | |
| set_compression () | |
| { | |
| local ssh_line="$SSH_CMD ${SSH_OPTS[*]} -p ${SSH_PORT:-22} -i \"$KEYFILE\" ${RUNAME}@${RHOST}" | |
| echo "Compression chosen: ${COMP}" | |
| case "$COMP" in | |
| gzip) | |
| if ! COMP_CMD=$(eval "$ssh_line" "which gzip"); then | |
| echo "[INFO]: gzip specified, but unavailable! Attempting fallback to bzip2" | |
| if ! COMP_CMD=$(eval "$ssh_line" "which bzip2"); then | |
| echo "[INFO]: bzip2 specified, but unavailable! Attempting fallback to 7zip" | |
| if ! COMP_CMD=$(eval "$ssh_line" "which 7z"); then | |
| echo >&2 "[ERROR]: Unable to find suitable compresson program on $RHOST!" | |
| return 1 | |
| else | |
| COMP="7zip" | |
| COMP_CMD="$COMP_CMD $SZ_OPTS" | |
| COMP_TEST_CMD="$LOC_7ZIP t" | |
| COMP_INFO_CMD="$LOC_7ZIP l" | |
| SUFFIX=".xz" | |
| fi | |
| else | |
| COMP="bzip2" | |
| COMP_CMD="$COMP_CMD $BZ2_OPTS" | |
| COMP_TEST_CMD="$LOC_BZIP2 -t" | |
| SUFFIX=".bz2" | |
| fi | |
| else | |
| COMP_CMD="$COMP_CMD $GZ_OPTS" | |
| COMP_TEST_CMD="$LOC_GZIP -t" | |
| COMP_INFO_CMD="$LOC_GZIP -l" | |
| SUFFIX=".gz" | |
| fi | |
| ;; | |
| bzip2) | |
| if ! COMP_CMD=$(eval "$ssh_line" "which bzip2"); then | |
| echo "[INFO]: bzip2 specified but unavailable! Attemting fallback to gzip" | |
| if ! COMP_CMD=$(eval "$ssh_line" "which gzip"); then | |
| echo "[INFO]: gzip specified but unavailable! Attempting fallback to 7zip" | |
| if ! COMP_CMD=$(eval "$ssh_line" "which 7z"); then | |
| echo >&2 "[ERROR]: Unable to find suitable compresson program on $RHOST!" | |
| return 1 | |
| else | |
| COMP="7zip" | |
| COMP_CMD="$COMP_CMD $SZ_OPTS" | |
| COMP_TEST_CMD="$LOC_7ZIP $SZ_TOPTS" | |
| COMP_INFO_CMD="$LOC_7ZIP l" | |
| SUFFIX=".xz" | |
| fi | |
| else | |
| COMP="gzip" | |
| COMP_CMD="$COMP_CMD $GZ_OPTS" | |
| COMP_TEST_CMD="$LOC_GZIP $GZ_TOPTS" | |
| COMP_INFO_CMD="$LOC_GZIP -l" | |
| SUFFIX=".gz" | |
| fi | |
| else | |
| COMP_CMD="$COMP_CMD $BZ2_OPTS" | |
| COMP_TEST_CMD="$LOC_BZIP2 $BZ2_TOPTS" | |
| SUFFIX=".bz2" | |
| fi | |
| ;; | |
| 7zip) | |
| if ! COMP_CMD=$(eval "$ssh_line" "which 7z"); then | |
| echo "[INFO]: 7zip specified but unavailable! Attempting fallback to bzip2" | |
| if ! COMP_CMD=$(eval "$ssh_line" "which bzip2"); then | |
| echo "[INFO]: bzip2 specified but unavailable! Attempting fallback to gzip" | |
| if ! COMP_CMD=$(eval "$ssh_line" "which gzip"); then | |
| echo >&2 "[ERROR]: Unable to find suitable compression program on $RHOST!" | |
| return 1 | |
| else | |
| COMP="gzip" | |
| COMP_CMD="$COMP_CMD $GZ_OPTS" | |
| COMP_TEST_CMD="$LOC_GZIP $GZ_TOPTS" | |
| COMP_INFO_CMD="$LOC_GZIP -l" | |
| SUFFIX=".gz" | |
| fi | |
| else | |
| COMP="bzip2" | |
| COMP_CMD="$COMP_CMD $BZ2_OPTS" | |
| COMP_TEST_CMD="$LOC_BZIP2 $BZ2_TOPTS" | |
| SUFFIX=".bz2" | |
| fi | |
| else | |
| COMP_CMD="$COMP_CMD $SZ_OPTS" | |
| COMP_TEST_CMD="$LOC_7ZIP $SZ_TOPTS" | |
| COMP_INFO_CMD="$LOC_7ZIP l" | |
| SUFFIX=".xz" | |
| fi | |
| ;; | |
| esac | |
| #echo "Remote compression command: ${COMP_CMD}" | |
| } # ---------- end of function set_compression ---------- | |
| #--- FUNCTION ---------------------------------------------------------------- | |
| # NAME: getSize | |
| # DESCRIPTION: Get size of database to be dumped | |
| # PARAMETERS: Database name | |
| # RETURNS: Size of database to be dumped | |
| #------------------------------------------------------------------------------- | |
| getSize () | |
| { | |
| $SSH_CMD ${SSH_OPTS[*]} -p ${SSH_PORT:-22} -i "$KEYFILE" ${RUNAME}@${RHOST} <<DATA | |
| set -o pipefail | |
| eval /usr/bin/mysql --user=$MUNAME --password="\"$MPASSWD\"" -s -r -e 'SELECT CONCAT\(sum\(ROUND\(\(\(DATA_LENGTH + INDEX_LENGTH - DATA_FREE\) / 1024 / 1024\),2\)\)," MB"\) FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA like "$1";' | |
| DATA | |
| } # ---------- end of function getSize ---------- | |
| #=== FUNCTION ================================================================ | |
| # NAME: backup | |
| # DESCRIPTION: Back up database to the given filename. | |
| # PARAMETERS: Database to backup as $1, filename as $2 | |
| # RETURNS: Success/Fail | |
| #=============================================================================== | |
| backup () | |
| { | |
| local outzip="$2.sql${SUFFIX}" | |
| local mdbopt="" | |
| # We have to set these options to properly suppress warning messages from | |
| # mysqldump regarding the mysql.event table, which doesn't exist. Unfortunately | |
| # this is the "correct" way to do this, as there is no '--ignore-events' option. | |
| # Yes, we're saying "also capture the events table, but don't really capture it." | |
| [[ "$1" = "mysql" ]] && mdbopt=( '--events' '--ignore-table=mysql.event' ) | |
| # Backup existing files, if any, in case things go wrong... | |
| [[ -e "$outzip" ]] && mv "${outzip}"{,.bak} | |
| # IMO, this is ugly, but allows us to pull large database dumps compressed | |
| # over the wire. 'set -o pipefail' is necessary here due to the fact that | |
| # $COMP_CMD will return success even if mysqldump was unsuccessful. This | |
| # convention allows us to detect a failure anywhere in the pipe and have | |
| # ssh pass it back to us via $?, which we can then trap and make decisions | |
| # based on its value. | |
| # | |
| # The use of the "here" file below is required for proper esacping of quotes | |
| #echo "Remote dump command: $MYSQLDUMP --user=$MUNAME --password=\""${MPASSWD}"\" $MOPT "$1" | $COMP_CMD 2>/dev/null" | |
| # $SSH_CMD $SSH_OPTS -i "$KEYFILE" ${RUNAME}@${RHOST} > $outzip <<DATA | |
| #set -o pipefail | |
| #eval $MYSQLDUMP --user=$MUNAME --password=\""${MPASSWD}"\" $MOPT "$1" | $COMP_CMD 2>/dev/null | |
| #DATA | |
| $SSH_CMD ${SSH_OPTS[*]} -p ${SSH_PORT:-22} -i "$KEYFILE" ${RUNAME}@${RHOST} >/dev/null <<DATA | |
| set -o pipefail | |
| eval $MYSQLDUMP --user=$MUNAME --password=\""${MPASSWD}"\" "${MOPT[*]}" "${mdbopt[*]}" "$1" | $COMP_CMD > /var/tmp/dump 2>/dev/null | |
| DATA | |
| # If dump failed, restore backups and return from function with a failure. | |
| if [[ $? -ne 0 ]]; then | |
| echo >&2 "[ERROR]: Failed to dump database $1 to file ${RHOST}:/var/tmp/dump" | |
| # Since we'll get a file even if the dump fails, we need to clean up | |
| # after ourselves. We do that w/the next 2 lines. | |
| echo >&2 " Removing failed dump archive..." | |
| $SSH_CMD ${SSH_OPTS[*]} -p ${SSH_PORT:-22} -i "$KEYFILE" ${RUNAME}@${RHOST} "set -o pipefail; [[ -e /var/tmp/dump ]] && rm -f /var/tmp/dump" && echo "done." || echo "failed." | |
| #[[ -e "$outzip" ]] && rm -f "$outzip" | |
| #[[ -e "$outzip.bak" ]] && mv ${outzip}{.bak,} | |
| #echo >&2 " Backup restored." | |
| echo >&2 " Dump archive removed." | |
| return 1 # Return to calling function w/error | |
| fi | |
| if [[ -e "$outzip" ]]; then | |
| echo -n "Backing up previous archive... " | |
| if mv "${outzip}"{,.bak}; then | |
| echo "done." | |
| else | |
| echo "failed." | |
| echo >&2 "[ERROR]: Unable to backup original archive." | |
| return 1 | |
| fi | |
| fi | |
| echo -n "Downloading archive from ${RHOST}... " | |
| scp -qB ${SSH_OPTS[*]} -P ${SSH_PORT:-22} -i "$KEYFILE" ${RUNAME}@${RHOST}:/var/tmp/dump "${outzip}" | |
| if [[ $? -ne 0 ]]; then | |
| echo "failed." | |
| echo >&2 "[ERROR]: Unable to copy dumped database from ${RHOST} to ${outzip}." | |
| echo >&2 " Removing failed dump archive..." | |
| [[ -e "$outzip" ]] && rm -f "$outzip" | |
| [[ -e "$outzip.bak" ]] && mv "${outzip}"{.bak,} | |
| echo >&2 " Backup restored." | |
| return 1 | |
| else | |
| echo "done." | |
| fi | |
| # Test the compressed file. If it fails, restore backup file and | |
| # throw an error. | |
| echo -n "Testing archive... " | |
| if ! $COMP_TEST_CMD "$outzip" >/dev/null 2>&1; then | |
| echo "failed." | |
| echo >&2 "[ERROR]: Archive file failed verification!" | |
| [[ -e "$outzip.bak" ]] && mv "${outzip}"{.bak,} | |
| echo >&2 " Backup restored." | |
| echo >&2 | |
| echo >&2 "Removing remote dump file..." | |
| $SSH_CMD ${SSH_OPTS[*]} -p ${SSH_PORT:-22} -i "$KEYFILE" ${RUNAME}@${RHOST} "rm -f /var/tmp/dump" | |
| if [[ $? -ne 0 ]]; then | |
| echo >&2 "[WARNING]: Unable to remove remote dump file /var/tmp/dump" | |
| echo >&2 " Please log in and remove it manually." | |
| fi | |
| return 1 # Return to calling function w/error | |
| fi | |
| echo "OK" | |
| echo | |
| echo -n "Removing remote dump file... " | |
| $SSH_CMD ${SSH_OPTS[*]} -p ${SSH_PORT:-22} -i "$KEYFILE" ${RUNAME}@${RHOST} "rm -f /var/tmp/dump" | |
| if [[ $? -ne 0 ]]; then | |
| echo "failed!" | |
| echo >&2 "[WARNING]: Unable to remove remote dump file /var/tmp/dump" | |
| echo >&2 " Please log in and remove it manually." | |
| else | |
| echo "done." | |
| fi | |
| case $COMP in | |
| 7zip) | |
| "$COMP_INFO_CMD" "$outzip" | tail -n5 | |
| ;; | |
| gzip) | |
| "$COMP_INFO_CMD" "$outzip" | |
| ;; | |
| bzip2) | |
| echo "No archive info available for bzip2 files." | |
| ;; | |
| *) | |
| ;; | |
| esac | |
| # If everything went well, remove backups. | |
| [[ -e "$outzip.bak" ]] && rm -f "$outzip.bak" | |
| echo | |
| return 0 | |
| } # ---------- end of function doBackup ---------- | |
| #=== FUNCTION ================================================================ | |
| # NAME: doMonthly | |
| # DESCRIPTION: Perform monthly backup of databases for $RHOST. | |
| # PARAMETERS: None | |
| # RETURNS: Success/Fail | |
| #=============================================================================== | |
| doMonthly () | |
| { | |
| local retcode=0 | |
| local database='' | |
| for database in "${DBLIST[@]}"; do | |
| # We almost never have permission to backup this database, so if it's | |
| # in the list, skip it. | |
| contains "${EXCLUDED[*]}" "$database" && continue | |
| #if [[ "$database" = "information_schema" ]] || [[ "$database" = "mysql.event" ]] || [[ "$database" = "performance_schema" ]]; then | |
| # continue | |
| #fi | |
| echo "Monthly bakcup of database: $database" | |
| if [[ $SEPDIR ]]; then | |
| local outdir="${BACKUPDIR}/${JOBNO}/monthly/$database" | |
| else | |
| local outdir="${BACKUPDIR}/${JOBNO}/monthly" | |
| fi | |
| local outfile="$outdir/${database}_month-$MONTH" | |
| if [[ ! -e $outdir ]] && ! mkdir -p "$outdir"; then | |
| echo >&2 "[ERROR]: Unable to create output directory $outdir" | |
| retcode=1 | |
| continue | |
| fi | |
| if ! backup "$database" "$outfile"; then | |
| echo >&2 "[ERROR]: Unable to backup database $database" | |
| retcode=1 | |
| continue | |
| fi | |
| done | |
| return $retcode | |
| } # ---------- end of function doMonthly ---------- | |
| #=== FUNCTION ================================================================ | |
| # NAME: doWeekly | |
| # DESCRIPTION: Perform a weekly backup of databases on $RHOST | |
| # PARAMETERS: None | |
| # RETURNS: Success/Fail | |
| #=============================================================================== | |
| doWeekly () | |
| { | |
| local retcode=0 | |
| local database='' | |
| for database in "${DBLIST[@]}"; do | |
| contains "${EXCLUDED[*]}" "$database" && continue | |
| #if [[ "$database" = "information_schema" ]] || [[ "$database" = "mysql.event" ]] || [[ "$database" = "performance_schema" ]]; then | |
| # continue | |
| #fi | |
| echo "Weekly backup of database: $database" | |
| if [[ $SEPDIR ]]; then | |
| local outdir="${BACKUPDIR}/${JOBNO}/weekly/$database" | |
| else | |
| local outdir="${BACKUPDIR}/${JOBNO}/weekly" | |
| fi | |
| if [[ ${WEEK} -le 5 ]]; then | |
| local remw=$((48 + WEEK)) | |
| elif [[ ${WEEK} -lt 15 ]]; then | |
| local remw=0$((WEEK - 5)) | |
| else | |
| local remw=$((WEEK - 5)) | |
| fi | |
| local outfile="${outdir}/${database}_week-$remw" | |
| if [[ ! -e $outdir ]] && ! mkdir -p "$outdir"; then | |
| echo >&2 "[ERROR]: Unable to create output directory $outdir" | |
| retcode=1 | |
| continue | |
| fi | |
| if ! backup "$database" "$outfile"; then | |
| echo >&2 "[ERROR]: Unable to backup database $database" | |
| retcode=1 | |
| continue | |
| fi | |
| done | |
| return $retcode | |
| } # ---------- end of function doWeekly ---------- | |
| #=== FUNCTION ================================================================ | |
| # NAME: doDaily | |
| # DESCRIPTION: Daily backup of databases on $RHOST | |
| # PARAMETERS: None | |
| # RETURNS: Success/Fail | |
| #=============================================================================== | |
| doDaily () | |
| { | |
| local retcode=0 | |
| local database='' | |
| for database in "${DBLIST[@]}"; do | |
| contains "${EXCLUDED[*]}" "$database" && continue | |
| #if [[ "$database" = "information_schema" ]] || [[ "$database" = "mysql.event" ]] || [[ "$database" = "performance_schema" ]]; then | |
| # continue | |
| #fi | |
| if [[ $SEPDIR ]]; then | |
| local outdir="${BACKUPDIR}/${JOBNO}/daily/$database" | |
| else | |
| local outdir="${BACKUPDIR}/${JOBNO}/daily" | |
| fi | |
| local outfile="$outdir/${database}_day-$DOW" | |
| if [[ ! -e $outdir ]] && ! mkdir -p "$outdir"; then | |
| echo >&2 "[ERROR]: Unable to create output directory $outdir" | |
| retcode=1 | |
| continue | |
| fi | |
| echo "Info for database $database: " | |
| #echo "Database size (uncompressed): " $(getSize $database) | |
| backup "$database" "$outfile" | |
| # if ! backup "$database" "$outfile"; then | |
| if [[ ! $? ]]; then | |
| echo >&2 "[ERROR]: Unable to backup database $database" | |
| retcode=1 | |
| echo | |
| continue | |
| fi | |
| done | |
| return $retcode | |
| } # ---------- end of function doDaily ---------- | |
| #--- FUNCTION ---------------------------------------------------------------- | |
| # NAME: hms | |
| # DESCRIPTION: Convert secons to h:m:s | |
| # PARAMETERS: Seconds to convert | |
| # RETURNS: String '##h:##m:##s' | |
| #------------------------------------------------------------------------------- | |
| hms () | |
| { | |
| printf "%dh:%dm:%ds\n" $(($1/3600)) $(($1%3600/60)) $(($1%60)) | |
| } # ---------- end of function hms ---------- | |
| #=== FUNCTION ================================================================ | |
| # NAME: header | |
| # DESCRIPTION: Output log header | |
| # PARAMETERS: None | |
| # RETURNS: None | |
| #=============================================================================== | |
| header () | |
| { | |
| cat <<EOF | |
| $THICKBAR | |
| Client Database Backup | |
| Backup of database(s) for $JOBNO on server - $RHOST | |
| $THICKBAR | |
| $THINBAR | |
| Backup start time: $(date -d "@$STARTTIME") | |
| $THINBAR | |
| EOF | |
| } # ---------- end of function header ---------- | |
| #=== FUNCTION ================================================================ | |
| # NAME: footer | |
| # DESCRIPTION: Output log footer | |
| # PARAMETERS: None | |
| # RETURNS: None | |
| #=============================================================================== | |
| footer () | |
| { | |
| cat <<EOF | |
| $THINBAR | |
| Backup end time: $(date -d "@$ENDTIME") (elapsed: $(hms $((ENDTIME - STARTTIME)))) | |
| $THINBAR | |
| $THICKBAR | |
| Size - Location | |
| $(du -hs "${BACKUPDIR}/${JOBNO}" 2>/dev/null || echo "N/A ${BACKUPDIR}/${JOBNO}") | |
| $(du -hs "$BACKUPDIR") | |
| $THICKBAR | |
| EOF | |
| } # ---------- end of function footer ---------- | |
| #------------------------------------------------------------------------------- | |
| # BEGIN Program Constants | |
| #------------------------------------------------------------------------------- | |
| # In 2 of the date commands below we store the number with the leading 0 stripped | |
| # off. This is because, under certain circumstances, Bash will treat a number -- | |
| # specifically 08 -- as an octal number when doing number comparisons. This | |
| # causes the comparisons to fail with a 'value too great for base (error token is | |
| # "08")' error. Storing these numbers without a leading '0' mitigates this | |
| # problem. | |
| DATE=$(date +%Y-%m-%d_%Hh%Mm) # Today's date | |
| DOW=$(date +%u) # Current day of week (0=Sun, 6=Sat) | |
| DNOW=$(date +%y) # 2-digit year | |
| DOM=$(date +%-d) # Day of the month, don't pad w/leading 0 | |
| MONTH=$(date +%B) # Current month | |
| WEEK=$(date +%-V) # Current week number (don't pad w/leading 0) | |
| #MOPT="-q --databases --compress --add-drop-database --add-drop-table" # Default opts for mysqldump | |
| MOPT=('-q' '--databases' '--compress' '--add-drop-database' '--add-drop-table' '--skip-events' '--single-transaction' '--skip-lock-tables') | |
| MYSQL_HOST=127.0.0.1 # Default mysql host (localhost) | |
| CODE=0 | |
| THICKBAR="==============================================================================" | |
| THINBAR="------------------------------------------------------------------------------" | |
| SSH_CMD="$(which ssh) -q" # -q = quiet mode | |
| MYSQL=$(which mysql) | |
| LOC_GZIP=$(which gzip) | |
| LOC_BZIP2=$(which bzip2) | |
| LOC_7ZIP=$(which 7z) | |
| TEE=$(which tee) | |
| GZ_TOPTS="-t" | |
| BZ2_TOPTS="-t" | |
| SZ_TOPTS="t" | |
| # Due to our '-o nounset' option in this script, we must declare all variables | |
| # before we use them, otherwise bash will throw an error. | |
| MYSQL_TCP_PORT='' | |
| COMP_CMD='' | |
| COMP_TEST_CMD='' | |
| COMP_INFO_CMD='' | |
| CONFIGS='' | |
| LOGDIR='' | |
| LOGFILE='' | |
| ERRFILE='' | |
| DATABASE='' | |
| JOBNO='' | |
| STARTTIME='' | |
| ENDTIME='' | |
| i='' | |
| #------------------------------------------------------------------------------- | |
| # END Program Constants | |
| #------------------------------------------------------------------------------- | |
| #------------------------------------------------------------------------------- | |
| # MAIN LOOP | |
| #------------------------------------------------------------------------------- | |
| # Trap kill signals (CTRL+C, etc.) and clean-up before exiting. | |
| # It should be obvious, but this will not catch SIGKILL (kill -9) | |
| trap 'echo >&2 "Received kill request, exiting..."; stopLogging; $exit 0' SIGHUP SIGINT SIGTERM | |
| # Get command-line arguments | |
| while getopts "hva:b:c:d:f:o:z:" opt; do | |
| case $opt in | |
| h) usage;; | |
| a) MAILADDR="$OPTARG";; | |
| b) BACKUPDIR="$OPTARG";; | |
| c) CONFDIR="$OPTARG";; | |
| f) CONFIGS="${CONFDIR}/${OPTARG##*/}";; | |
| d) DATABASE="$OPTARG";; # User-specified database | |
| D) DEBUG=0;; | |
| o) LOGTYPE="$OPTARG";; | |
| v) set -x;; # Verbose (a.k.a. Debug) | |
| z) COMP="${OPTARG}";; # Overload default setting | |
| *) usage;; | |
| esac | |
| done | |
| # Read our list of config files into an array we'll loop through. | |
| # We use this particular construction to make sure we can read-in filenames | |
| # with embedded white-space and newline characters. See 'man 1 find' for | |
| # info on the -print0 switch and 'man 1 read' for info on the options used | |
| # with the read statement. | |
| if [[ -z $CONFIGS ]]; then | |
| while IFS= read -r -d '' file; do | |
| CONFIGS[i++]="$file" | |
| done< <(find "$CONFDIR" -type f -name "*.cnf" -print0) # Use output of find statement to feed loop. | |
| fi | |
| for CONFIG in "${CONFIGS[@]}"; do # Loop through config files. | |
| JOBNO=${CONFIG##*/} # Basename of config file | |
| JOBNO=${JOBNO%%\.*} # Strip file extension to get Job No. | |
| sleep 3 | |
| if ! startLogging; then | |
| echo >&2 "[ERROR]: Unable to start logging. Output will be to screen." | |
| fi | |
| if [[ ! -e "${CONFIG}" ]] || [[ ! -r "${CONFIG}" ]]; then | |
| echo >&2 "[ERROR]: ${CONFIG} does not exist or is not readable! Skipping..." | |
| continue # Continue to next item in for loop. | |
| else | |
| # Include our config file. Bring in variables defined in the config file | |
| # as local variables to this script. | |
| . "$CONFIG" | |
| fi | |
| STARTTIME=$(date +%s) | |
| header | |
| # Check if we have a user-supplied database name/list" | |
| if [[ -z $DATABASE ]]; then | |
| # The convention below replaces ',' characters with newline '\n' characters | |
| # but saves us the expense of a sub-shell to do an echo through 'tr' | |
| # After the conversion has been done, each db name then becomes an array | |
| # element. | |
| DBLIST=(${DBLIST//,/$'\x0A'}) | |
| else | |
| DBLIST=(${DATABASE//,/$'\x0A'}) # Allows for list of dbs @ cl. | |
| fi | |
| if [[ ! -e "$KEYFILE" || ! -r "$KEYFILE" ]]; then # Can we read our ssh keyfile? | |
| echo >&2 "[ERROR]: Key file $KEYFILE missing or unreadable." | |
| CODE=1 | |
| ENDTIME=$(date +%s) | |
| footer | |
| stopLogging | |
| continue # Skip to next config file | |
| fi | |
| # Check connectivity to remote host. | |
| if _testssh; then | |
| : # noop | |
| else | |
| echo >&2 "[ERROR]: Connection to $RHOST failed. Skipping to next host." | |
| CODE=1 | |
| ENDTIME=$(date +%s) | |
| footer | |
| stopLogging | |
| continue | |
| fi | |
| #STATUS=$(eval $SSH_CMD ${SSH_OPTS[*]} -p ${SSH_PORT:-22} ${RUNAME}@${RHOST} -i \"${KEYFILE}\" echo ok 2>&1) | |
| #[[ $DEBUG ]] && { echo "SSH STATUS: $STATUS"; } | |
| #if [[ $STATUS == "Permission denied"* ]]; then | |
| # echo >&2 "[ERROR]: Connection to $RHOST is not authorized" | |
| # echo >&2 " Skipping to next host." | |
| # CODE=1 | |
| # ENDTIME=$(date +%s) | |
| # footer | |
| # stopLogging | |
| # continue | |
| #elif [[ ! $STATUS == ok ]]; then | |
| # echo >&2 "[ERROR]: Unable to connect to host $RHOST due to unknown error" | |
| # echo >&2 " Skipping to next host." | |
| # CODE=1 | |
| # ENDTIME=$(date +%s) | |
| # footer | |
| # stopLogging | |
| # continue | |
| #fi | |
| #if [[ $DEBUG ]]; then | |
| # echo "Checking host connectivity..." | |
| # echo "Command: $SSH_CMD $SSH_OPTS -p ${SSH_PORT:-22} ${RUNAME}@${RHOST} -i \"$KEYFILE\" \"/bin/true\" >/dev/null" | |
| #fi | |
| #if ! eval $SSH_CMD $SSH_OPTS -p ${SSH_PORT:-22} ${RUNAME}@${RHOST} -i "$KEYFILE" "/bin/true" >/dev/null; then | |
| # echo >&2 "[ERROR]: Unable to connect to host $RHOST" | |
| # echo >&2 " Skipping to next host." | |
| # CODE=1 | |
| # ENDTIME=$(date +%s) | |
| # footer | |
| # stopLogging | |
| # continue # Skip to next config file | |
| #fi | |
| # This sleep needs to be here to prevent the script from trying to connect | |
| # to the database before the establishment of the port forward has been | |
| # completed. | |
| #sleep 3 | |
| if [[ ${#DBLIST[@]} -eq 1 ]] && [[ "${DBLIST[0]}" = "all" ]]; then # Should we get a list of databases from server? | |
| if ! DBLIST=($(getDatabases)); then # Did we successfully get a list? | |
| echo >&2 "[ERROR]: Unable to retrieve list of databases from $RHOST" | |
| echo >&2 " Skipping to next host." | |
| CODE=1 | |
| ENDTIME=$(date +%s) | |
| footer | |
| stopLogging | |
| continue # Skip to next config file | |
| fi | |
| fi | |
| # Initialize some settings based on chosen compression program. | |
| if ! set_compression; then | |
| echo >&2 "[ERROR]: Setting compression type failed for $RHOST" | |
| echo >&2 " Skipping to next host." | |
| CODE=1 | |
| ENDTIME=$(date +%s) | |
| footer | |
| stopLogging | |
| continue | |
| fi | |
| # Get path to mysqldump binary on remote host. Error and move on if unsuccessful. | |
| if ! MYSQLDUMP=$($SSH_CMD ${SSH_OPTS[*]} -p ${SSH_PORT:-22} -i "$KEYFILE" ${RUNAME}@${RHOST} "which mysqldump"); then | |
| echo >&2 "[ERROR]: Unable to find mysqldump on $RHOST" | |
| echo >&2 " Skipping to next host." | |
| CODE=1 | |
| ENDTIME=$(date +%s) | |
| footer | |
| stopLogging | |
| continue | |
| fi | |
| # Add specified databases to list of excluded databases. | |
| if [[ ${#EXCLUDE[@]} -gt 0 ]]; then | |
| EXCLUDED+=( "${EXCLUDE[@]}" ) | |
| fi | |
| if [[ $DOM -eq 1 ]]; then | |
| if ! doMonthly; then | |
| CODE=1 | |
| echo "Errors were reported during monthly backup." | |
| fi | |
| fi | |
| if [[ $DOW -eq $DOWEEKLY ]]; then | |
| if ! doWeekly; then | |
| CODE=1 | |
| echo "Errors were reported during weekly backup." | |
| fi | |
| fi | |
| if ! doDaily; then | |
| CODE=1 | |
| echo "Errors were reported during daily backup." | |
| fi | |
| ENDTIME=$(date +%s) | |
| footer | |
| stopLogging | |
| unset MUNAME MPASSWD DBLIST RUNAME RHOST RPORT KEYFILE SSH_PORT | |
| done | |
| exit $CODE |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment