File mysql-backupscript of Package mysql-backupscript

Overview Repositories Revisions Requests Users Attributes Meta

File mysql-backupscript of Package mysql-backupscript

#!/bin/bash 
# Simple backupscript for MySQL databases
# Author: Lars Vogdt
# BSD3 Clause License
#

PATH=/bin:/usr/bin
MAILX='/usr/bin/mail'
MYSQL_OPTS='--defaults-extra-file=/root/.my.cnf'
MYSQL_CHECK='/usr/bin/mariadb-check'
MYSQLADMIN='/usr/bin/mariadb-admin'
MYSQLDUMP='/usr/bin/mariadb-dump'
MYSQL='/usr/bin/mariadb'
SEND_NSCA_CONFIG='/etc/send_nsca.cfg'
SEND_NSCA_BIN='/usr/bin/send_nsca'
USE_NSCA='no'
USE_EMAIL='yes'
SEND_NSCA_HOSTNAME=$(hostname -s)
NAGIOSHOST='localhost'
NAGIOS_SERVICE_NAME='MySQL backup'
DISTCONFIG='/etc/sysconfig/mysql-backupscript'
##################################################
# Default/Fallback values
# Don't change them here! Use $DISCONFIG instead
##################################################
BACKUPDIR='/root/backup/mysql'
DUMP_OPTIONS="--add-drop-database \
--add-drop-table \
--add-drop-trigger \
--apply-slave-statements \
--default-character-set=utf8mb4 \
--dump-date \
--events \
--extended-insert \
--flush-logs \
--flush-privileges \
--include-master-host-port \
--master-data=2 \
--opt \
--quick \
--quote-names \
--routines \
--single-transaction"
FQHOSTNAME=`hostname -f`
HOST=$(hostname -s 2>/dev/null)
LOGNAME='mysql-backupscript'
LOGFILE="/var/log/${LOGNAME}.log"
EMAIL='root@localhost'
START_BACKUP='yes'
FORCE_BACKUP='no'
OPTIMIZE_DB='no'
DEBUG='no'
RETENTION=14
COMPRESS_EXE='/usr/bin/xz'
COMPRESS_AFTER_UNLOCK='no'
MYSQL_SCRIPT_BEFORE_DUMP=''
MYSQL_SCRIPT_AFTER_DUMP=''
##################################################
umask 027
unset LANG;

cleanup_and_exit(){
    test -n "$TMPFILE" -a -f "$TMPFILE" && rm "$TMPFILE"
    exit $1
}

LOG(){
    local MESSAGE="$1"
    local LOG_DATE=$(date "+%b %d %H:%M:%S")
    if [ -z "$LOGFILE" ]; then
        HANDLE_MESSAGE "ERROR: LOGFILE is not defined" 1
        cleanup_and_exit 1
    fi
    if [ ! -d "$LOGDIR" ]; then
        mkdir -p "$LOGDIR" || exit 1
        echo "$LOG_DATE $HOST $LOGNAME[$$]: function LOG created $LOGDIR" > "$LOGFILE"
    fi
    echo "$LOG_DATE $HOST $LOGNAME[$$]: $MESSAGE" >> $LOGFILE || DEBUG="yes"
    if [ "$DEBUG" = "yes" ]; then
        echo "DEBUG:    $MESSAGE"
    fi    
}

SEND_NSCA(){
    local MESSAGE="$1"
    local EXIT_CODE="$2"
    echo -e "$SEND_NSCA_HOSTNAME\t$NAGIOS_SERVICE_NAME\t$EXIT_CODE\t$MESSAGE" | $SEND_NSCA_BIN -H $NAGIOSHOST -c $SEND_NSCA_CONFIG 1>/dev/null
}

SEND_EMAIL(){
    local MESSAGE="$1"
    local EXIT_CODE="$2"
    case $EXIT_CODE in 
        0) MESSAGE="OK: $MESSAGE" ;;
        1) MESSAGE="ERROR: $MESSAGE" ;;
        2) MESSAGE="WARNING: $MESSAGE" ;;
        3) MESSAGE="UNKNOWN: $MESSAGE" ;;
    esac
    echo "$MESSAGE" | $MAILX -s "[$LOGNAME] on $FQHOSTNAME" $EMAIL
}

HANDLE_MESSAGE(){
    local MESSAGE="$1"
    local EXIT_CODE="$2"
    case "$USE_NSCA" in
        [Yy][Ee][Ss]) SEND_NSCA "$MESSAGE" "$EXIT_CODE" ;;
    esac
    case "$USE_EMAIL" in
        [Yy][Ee][Ss]) SEND_EMAIL "$MESSAGE" "$EXIT_CODE" ;;
    esac
}

test_mysql_alive(){
    local tmpfile="$1"
    if [ -x "$MYSQLADMIN" ]; then
        $MYSQLADMIN $MYSQL_OPTS ping 1>/dev/null 2>>"$tmpfile"
        if [ -s "$tmpfile" ]; then
            HANDLE_MESSAGE "$(cat $tmpfile)" 1
            LOG "$(cat "$tmpfile")"
            cleanup_and_exit 1
        fi
    elif [ -x "$MYSQL" ]; then
        $MYSQL $MYSQL_OPTS -e "SHOW DATABASES;" >/dev/null 2>>"$tmpfile"
        if [ $? != 0 ]; then
            HANDLE_MESSAGE "$(cat $tmpfile)" 1
            LOG "$(cat "$tmpfile")"
            cleanup_and_exit 1
        fi
    else
        HANDLE_MESSAGE "Could neither execute $MYSQLADMIN nor $MYSQL" 1
        LOG "ERROR: Could neither execute $MYSQLADMIN nor $MYSQL"
        cleanup_and_exit 1
    fi
    if [ ! -x "$MYSQLDUMP" ]; then
        HANDLE_MESSAGE "Could not execute $MYSQLDUMP" 1
        LOG "ERROR: Could not execute $MYSQLDUMP"
        cleanup_and_exit 1
    fi
}

optimize() {
    if [ -x "$MYSQL_CHECK" ]; then
        LOG "Starting automatic repair and optimization of the databases/tables"
        $MYSQL_CHECK     \
            $MYSQL_OPTS \
            --all-databases \
            --skip-databases=lost+found \
            --compress \
            --auto-repair \
            --optimize  1>/dev/null 2>"$TMPFILE"
        # The query cache is deprecated as of MySQL 5.7.20, and is removed in MySQL 8.0
        # Anyway: optimizing it when it's there should not harm...
        $MYSQL $MYSQL_OPTS -e "FLUSH QUERY CACHE;" 2>>"$TMPFILE"
    fi
}

print_help(){
    echo "Usage: $(basename $0) [-c <configfile>]"
    echo 
    echo "       -d              : print debug output"
    echo "       -f              : force backup (incl. remove backups in same directory)"
    echo "       -h              : print this message"
    echo "       -c <configfile> : use the given config file instead of $DISTCONFIG"
    echo
    echo " The target of this script is to create backups of your mysql databases"
    echo " on a regular daily bases."
    echo " Please find the configuration details in $DISTCONFIG - if you want to"
    echo " test something, the option -f might be useful to use another file containing"
    echo " the configuration."
    echo
    echo " PLEASE NOTE: "
    echo "  Please have a look at /usr/share/doc/packages/mysql-backupscript/README.SUSE"
    echo "  as this contains information for password protected databases and further"
    echo "  details."
    exit 0
}

create_dir(){
    local directory="$1"
    mkdir -p "$directory" 2>"$TMPFILE" || { 
        HANDLE_MESSAGE "$(cat $TMPFILE)" 1
        LOG "$(cat "$TMPFILE")"
        cleanup_and_exit 1
    }
}

function compress_dump() {
  db_sql="$1"
  if [ -x "$COMPRESS_EXE" ]; then
    $COMPRESS_EXE "$db_sql"
  else
    LOG "Not compressing $db.sql as $COMPRESS_EXE is not executable"
  fi
}

trap cleanup_and_exit 0 1 2 3 7 13 15

while getopts 'dhfc:'  OPTION ; do
    case $OPTION in
        d) DEBUG='yes'
        ;;
        f) START_BACKUP='yes'
           FORCE_BACKUP='yes'
        ;;
        h) print_help
        ;;
        c) DISTCONFIG="$OPTARG"
        ;;
    esac
done
shift $(( OPTIND - 1 ))

for binary in "$MYSQL_CHECK" "$MYSQLDUMP" "$MYSQL" "$COMPRESS_EXE" ; do
    if [ ! -x "$binary" ]; then
        LOG "ERROR: bnary $binary can not be executed"
        HANDLE_MESSAGE "Binary: $binary can not be executed" 1
    fi
done
case $USE_NSCA in 
    [Yy][Ee][Ss]) 
        if [ ! -x "$SEND_NSCA_BIN" ]; then 
            LOG "ERROR: $SEND_NSCA_BIN can not be executed"
            cleanup_and_exit 1
        fi
        if [ ! -r "$SEND_NSCA_CONFIG" ]; then
            LOG "ERROR: can not read $SEND_NSCA_CONFIG" 
            cleanup_and_exit 1
        fi
    ;;
esac
case "$USE_EMAIL" in
    [Yy][Ee][Ss]) 
        if [ ! -x "$MAILX" ]; then
            LOG "ERROR: $MAILX can not be executed"
        fi
    ;;
esac

# source our config
if [ -f "$DISTCONFIG" ]; then
    . "$DISTCONFIG"
    LOGDIR=$(dirname "$LOGFILE")
else
    echo "$DISTCONFIG not found - using defaults" >&2
    LOGDIR=$(dirname "$LOGFILE")
    LOG "$DISTCONFIG not found - using defaults"
fi

case "$START_BACKUP" in
  [Yy]*)
    DATE=$(date "+%Y%m%d")
    BEGIN_SECONDS=$(date +%s)
    TMPFILE=$(mktemp /tmp/mysql-backupscript-XXXXXX)
    if ! test -d "$BACKUPDIR" -a "$FORCE_BACKUP" != 'yes' ; then
        case "$CREATE_BACKUPDIR" in 
            [Nn][Oo])
                HANDLE_MESSAGE "$BACKUPDIR does not exist. Exiting as CREATE_BACKUPDIR is set to 'no' in $DISTCONFIG" 1
                LOG "ERROR: $BACKUPDIR does not exist. Exiting as CREATE_BACKUPDIR is set to 'no' in $DISTCONFIG"
                cleanup_and_exit 1
            ;;
            [Ss][Kk][Ii][Pp])
                cleanup_and_exit 0
            ;;
            *)
                create_dir "$BACKUPDIR"
            ;;
        esac
    fi
    if [ -d "$BACKUPDIR/$DATE" ]; then
        if [ "$FORCE_BACKUP" == 'yes' ]; then
            LOG "$BACKUPDIR/$DATE already exists - cleaning up, as option --force is used"
            rm -rf "$BACKUPDIR/$DATE"
            create_dir "$BACKUPDIR/$DATE"
        else
            HANDLE_MESSAGE "$BACKUPDIR/$DATE exists"
            LOG "$BACKUPDIR/$DATE already exists - aborting backup"
            cleanup_and_exit 1
        fi
    else
        create_dir "$BACKUPDIR/$DATE"
    fi
    pushd "$BACKUPDIR/$DATE" 1>/dev/null
    if [ -r /etc/my.cnf ]; then
        LOG "Creating backup of my.cnf"
        test -f /etc/my.cnf && cp /etc/my.cnf .
        if [ -d /etc/my.cnf.d/ ]; then
            CONFIG_FILES="$(ls /etc/my.cnf.d/*.cnf 2>/dev/null)"
            if [ -n "$CONFIG_FILES" ]; then
               LOG "Backing up config files in /etc/my.cnf.d/"
               create_dir "$BACKUPDIR/$DATE/my.cnf.d"
               for file in $CONFIG_FILES; do
                   cp "$file" "$BACKUPDIR/$DATE/my.cnf.d/"
               done
            fi
        fi
    fi
    test_mysql_alive "$TMPFILE"
    if [ -n "$MYSQL_SCRIPT_BEFORE_DUMP" ]; then
        "$MYSQL_SCRIPT_BEFORE_DUMP"
    fi
    $MYSQL $MYSQL_OPTS -e "FLUSH LOGS;" 2>"$TMPFILE" 1>&2
    ALL_DATABASES=`$MYSQL $MYSQL_OPTS -e "SHOW DATABASES;" 2>>"$TMPFILE" | tail -n +2 | grep -v information_schema | grep -v performance_schema`
    # start the real backup
    for db in $ALL_DATABASES ; do
        if [ -s "$TMPFILE" ]; then
            HANDLE_MESSAGE "MySQL: $(cat $TMPFILE)" 1
            LOG "$(cat "$TMPFILE")"
            cleanup_and_exit 1
        fi
        LOG "Creating backup of $db"
        echo "-- " >> "$db.sql"
        echo "-- $db backup from $FQHOSTNAME on $DATE" >> "$db.sql"
        echo "-- " >> "$db.sql"
        echo "" >> "$db.sql"
        $MYSQLDUMP $MYSQL_OPTS $DUMP_OPTIONS --databases "$db" >> "$db.sql"
        if [ "$COMPRESS_AFTER_UNLOCK" != "yes" ]; then
            compress_dump "$db.sql"
        fi
        chmod 640 $db.sql*
    done
    if [ -n "$MYSQL_SCRIPT_AFTER_DUMP" ]; then
        "$MYSQL_SCRIPT_AFTER_DUMP"
    fi
    for db in $ALL_DATABASES ; do
        if [ "$COMPRESS_AFTER_UNLOCK" = "yes" ]; then
            compress_dump "$db.sql"
        fi
        chmod 640 $db.sql*
    done
    case "$OPTIMIZE_DB" in
        [Yy][Ee][Ss])
            optimize
        ;;
    esac
    if [ -s "$TMPFILE" ]; then
        HANDLE_MESSAGE "MySQL: $(cat $TMPFILE)" 1
        LOG "$(cat "$TMPFILE")"
        cleanup_and_exit 1
    fi
    popd 1>/dev/null
    chmod 750 "$BACKUPDIR/$DATE"
    if [ -n "$RETENTION" -a "$RETENTION" -gt 0 ]; then
        LOG "removing backups older than $RETENTION days"
        find "$BACKUPDIR" -ctime +$RETENTION -print0 | xargs -0 rm -rf {} | grep -v "No such file or directory" 
    fi
    END_SECONDS=$(date +%s)
    RUNTIME_RAW_SECONDS=$[$END_SECONDS-$BEGIN_SECONDS]
    RUNTIME_HOURS=$[$RUNTIME_RAW_SECONDS/3600]
    RUNTIME_MINUTES=$[$[$RUNTIME_RAW_SECONDS%3600]/60]
    RUNTIME_SECONDS=$[$[$RUNTIME_RAW_SECONDS%3600]%60]
    LOG "backup finished. Duration (hh:mm:ss): $RUNTIME_HOURS:$RUNTIME_MINUTES:$RUNTIME_SECONDS"
    case $USE_NSCA in
       [Yy][Ee][Ss])
           SEND_NSCA "Finished: $(date); Duration (hh:mm:ss): $RUNTIME_HOURS:$RUNTIME_MINUTES:$RUNTIME_SECONDS | time=$RUNTIME_RAW_SECONDS;" 0
       ;;
    esac
    cleanup_and_exit 0
  ;;
  *)
    cleanup_and_exit 0
  ;;
esac
cleanup_and_exit 0

Places

File mysql-backupscript of Package mysql-backupscript

Places