#!/usr/bin/env bash # # NAME # zabbix-mysql-dump - Configuration Backup for Zabbix with MySQL # # VERSION # 0.8.2 # # SYNOPSIS # This is a MySQL configuration backup script for Zabbix 1.x, 2.x and 3.0.x. # It does a full backup of all configuration tables, but only a schema # backup of large data tables. # # The script is based on a script by Ricardo Santos # (http://zabbixzone.com/zabbix/backuping-only-the-zabbix-configuration/) # # CONTRIBUTORS # - Ricardo Santos # - Jens Berthold (maxhq) # - Oleksiy Zagorskyi (zalex) # - Petr Jendrejovsky # - Jonathan Bayer # - Andreas Niedermann (dre-) # - Mișu Moldovan (dumol) # - Daniel Schneller (dschneller) # - Ruslan Ohitin (ruslan-ohitin) # - Jonathan Wright (neonardo1) # - msjmeyer # # AUTHOR # Jens Berthold (maxhq), 2016 # # LICENSE # This script is released under the MIT License (see LICENSE.txt) # # DEFAULT VALUES # # DO NOT EDIT THESE VALUES! # Instead, use command line parameters or a config file to specify options. # DUMPDIR="$PWD" DBHOST="127.0.0.1" DBNAME="zabbix" DBUSER="zabbix" DBPASS="" COMPRESSION="gz" QUIET="no" REVERSELOOKUP="yes" GENERATIONSTOKEEP=0 # # SHOW HELP # if [ -z "$1" ]; then cat <<EOF USAGE $(basename $BASH_SOURCE) [options] OPTIONS -h HOST Hostname/IP of MySQL server. Default: $DBHOST -d DATABASE Zabbix database name. Default: $DBNAME -u USER MySQL user to access Zabbix database. Default: $DBUSER -p PASSWORD MySQL user password (specify "-" for a prompt). Default: no password -o DIR Save Zabbix MySQL dumps to DIR. Default: $DUMPDIR -c FILE Use FILE for MySQL options (passed via --defaults-extra-file). PLEASE NOTE: mysqldump needs the database to be specified via command line. So the first "database" options found in the config file is used for mysqldump. -r NUM Rotate backups while keeping up to NUM generations. Uses filename to match. Default: keep all backups -x Compress using xz instead of gz PLEASE NOTE: xz compression will take much longer and consume more CPU time but the resulting backup will be about half the size of the same sql file compressed using gz. Your mileage may vary. -0 Do not compress the sql dump -n Skip reverse lookup of IP address for host. -q Quiet mode: no output except for errors (for batch/crontab use). EXAMPLES $(basename $BASH_SOURCE) -h 1.2.3.4 -d zabbixdb -u zabbix -p test $(basename $BASH_SOURCE) -u zabbix -p - -o /tmp $(basename $BASH_SOURCE) -c /etc/mysql/mysql.cnf $(basename $BASH_SOURCE) -c /etc/mysql/mysql.cnf -d zabbixdb EOF exit 1 fi # # PARSE COMMAND LINE ARGUMENTS # DB_GIVEN=0 while getopts ":h:d:u:p:o:r:c:x0qn" opt; do case $opt in h) DBHOST="$OPTARG" ;; d) DBNAME="$OPTARG"; DB_GIVEN=1 ;; u) DBUSER="$OPTARG" ;; p) DBPASS="$OPTARG" ;; c) CNFFILE="$OPTARG" ;; o) DUMPDIR="$OPTARG" ;; r) GENERATIONSTOKEEP=$(printf '%.0f' "$OPTARG") ;; x) COMPRESSION="xz" ;; 0) COMPRESSION="" ;; n) REVERSELOOKUP="no" ;; q) QUIET="yes" ;; \?) echo "Invalid option: -$OPTARG" >&2; exit 1 ;; :) echo "Option -$OPTARG requires an argument" >&2; exit 1 ;; esac done # Password prompt if [ "$DBPASS" = "-" ]; then read -s -p "Enter MySQL password for user '$DBUSER' (input will be hidden): " DBPASS echo "" fi # Config file validations if [ ! -z "$CNFFILE" ]; then if [ ! -r "$CNFFILE" ]; then echo "ERROR: Cannot read configuration file $CNFFILE" >&2 exit 1 fi # Database name needs special treatment: # For mysqldump it has to be specified on the command line! # Therefore we need to get it from the config file if [ $DB_GIVEN -eq 0 ]; then DBNAME=$(grep -m 1 ^database= "$CNFFILE" | cut -d= -f2) fi fi # # CONSTANTS # SUFFIX=""; test ! -z $COMPRESSION && SUFFIX=".${COMPRESSION}" MYSQL_OPTS=() [ ! -z "$CNFFILE" ] && MYSQL_OPTS=("${MYSQL_OPTS[@]}" --defaults-extra-file="$CNFFILE") [ ! -z "$DBHOST" ] && MYSQL_OPTS=("${MYSQL_OPTS[@]}" -h $DBHOST) [ ! -z "$DBUSER" ] && MYSQL_OPTS=("${MYSQL_OPTS[@]}" -u $DBUSER) [ ! -z "$DBPASS" ] && MYSQL_OPTS=("${MYSQL_OPTS[@]}" -p"$DBPASS") MYSQL_OPTS_BATCH=("${MYSQL_OPTS[@]}" --batch --silent) [ ! -z "$DBNAME" ] && MYSQL_OPTS_BATCH=("${MYSQL_OPTS_BATCH[@]}" -D $DBNAME) # Log file for errors ERRORLOG=$(mktemp) # Host name: try reverse lookup if IP is given DBHOSTNAME="$DBHOST" command -v dig >/dev/null 2>&1 FIND_DIG=$? if [ "$REVERSELOOKUP" == "yes" -a $FIND_DIG -eq 0 ]; then # Try resolving a given host ip newHostname=$(dig +noall +answer -x $DBHOST | sed -r 's/((\S+)\s+)+([^\.]+)\..*/\3/') test \! -z "$newHostname" && DBHOSTNAME="$newHostname" fi # # CONFIG DUMP # if [ "$QUIET" == "no" ]; then cat <<-EOF Configuration: - host: $DBHOST ($DBHOSTNAME) - database: $DBNAME - user: $DBUSER - output: $DUMPDIR EOF fi # # FUNCTIONS # # Returns TRUE if argument 1 is part of the given array (remaining arguments) elementIn () { local e for e in "${@:2}"; do [[ "$e" == "$1" ]] && return 0; done return 1 } # # CHECKS # if [ ! -x /usr/bin/mysqldump ]; then echo "mysqldump not found." >&2 echo "(with Debian, \"apt-get install mysql-client\" will help)" >&2 exit 1 fi # # READ TABLE LIST from __DATA__ section at the end of this script # (http://stackoverflow.com/a/3477269/2983301) # DATA_TABLES=() while read line; do table=$(echo "$line" | cut -d" " -f1) echo "$line" | cut -d" " -f5 | grep -qi "DATA" test $? -eq 0 && DATA_TABLES+=($table) done < <(sed '0,/^__DATA__$/d' "$BASH_SOURCE" | tr -s " ") # paranoid check if [ ${#DATA_TABLES[@]} -lt 5 ]; then echo "ERROR: The number of large data tables configured in this script is less than 5." >&2 exit 1 fi # # BACKUP # # Read table list from database [ "$QUIET" == "no" ] && echo "Fetching list of existing tables..." DB_TABLES=$(mysql "${MYSQL_OPTS_BATCH[@]}" -e "SELECT table_name FROM information_schema.tables WHERE table_schema = '$DBNAME'" 2>$ERRORLOG) if [ $? -ne 0 ]; then echo "ERROR while trying to access database:" 2>&1; cat $ERRORLOG 2>&1; exit 1; fi DB_TABLES=$(echo "$DB_TABLES" | sort) DB_TABLE_NUM=$(echo "$DB_TABLES" | wc -l) # Query Zabbix database version VERSION="" DB_VER=$(mysql "${MYSQL_OPTS_BATCH[@]}" -N -e "select optional from dbversion;" 2>/dev/null) if [ $? -eq 0 ]; then # version string is like: 02030015 re='(.*)([0-9]{2})([0-9]{4})' if [[ $DB_VER =~ $re ]]; then VERSION="_db-${BASH_REMATCH[1]}.$(( ${BASH_REMATCH[2]} + 0 )).$(( ${BASH_REMATCH[3]} + 0 ))" fi fi # Assemble file name DUMPFILENAME_PREFIX="zabbix_cfg_${DBHOSTNAME}" DUMPFILEBASE="${DUMPFILENAME_PREFIX}_$(date +%Y%m%d-%H%M)${VERSION}.sql" DUMPFILE="$DUMPDIR/$DUMPFILEBASE" PROCESSED_DATA_TABLES=() i=0 mkdir -p "${DUMPDIR}" [ "$QUIET" == "no" ] && echo "Starting table backups..." while read table; do # large data tables: only store schema if elementIn "$table" "${DATA_TABLES[@]}"; then dump_opt="--no-data" PROCESSED_DATA_TABLES+=($table) # configuration tables: full dump else dump_opt="--extended-insert=FALSE" fi mysqldump "${MYSQL_OPTS[@]}" \ --routines --opt --single-transaction --skip-lock-tables \ $dump_opt \ $DBNAME --tables ${table} >> "$DUMPFILE" 2>$ERRORLOG if [ $? -ne 0 ]; then echo -e "\nERROR: Could not backup table ${table}:" >&2; cat $ERRORLOG >&2; exit 1; fi if [ "$QUIET" == "no" ]; then # show percentage i=$((i+1)); i_percent=$(($i * 100 / $DB_TABLE_NUM)) if [ $(($i_percent % 12)) -eq 0 ]; then echo -n "${i_percent}%" else if [ $(($i_percent % 2)) -eq 0 ]; then echo -n "."; fi fi fi done <<<"$DB_TABLES" rm $ERRORLOG # # COMPRESS BACKUP # if [ "$QUIET" == "no" ]; then echo -e "\n" echo "For the following large tables only the schema (without data) was stored:" for table in "${PROCESSED_DATA_TABLES[@]}"; do echo " - $table"; done echo echo "Compressing backup file..." fi EXITCODE=0 if [ "$COMPRESSION" == "gz" ]; then gzip -f "$DUMPFILE"; EXITCODE=$?; fi if [ "$COMPRESSION" == "xz" ]; then xz -f "$DUMPFILE"; EXITCODE=$?; fi if [ $EXITCODE -ne 0 ]; then echo -e "\nERROR: Could not compress backup file, see previous messages" >&2 exit 1 fi [ "$QUIET" == "no" ] && echo -e "\nBackup Completed:\n${DUMPFILE}${SUFFIX}" # # ROTATE OLD BACKUPS # if [ $GENERATIONSTOKEEP -gt 0 ]; then [ "$QUIET" == "no" ] && echo "Removing old backups, keeping up to $GENERATIONSTOKEEP" REMOVE_OLD_CMD="cd \"$DUMPDIR\" && ls -t \"${DUMPFILENAME_PREFIX}\"* | /usr/bin/awk \"NR>${GENERATIONSTOKEEP}\" | xargs rm -f " eval ${REMOVE_OLD_CMD} if [ $? -ne 0 ]; then echo "ERROR: Could not rotate old backups" >&2 exit 1 fi fi exit 0 ################################################################################ # List of all known table names and a flag indicating data (=large) tables # __DATA__ acknowledges 1.3.1 - 3.0.3 DATA actions 1.3.1 - 3.0.3 alerts 1.3.1 - 3.0.3 DATA application_discovery 2.5.0 - 3.0.3 application_prototype 2.5.0 - 3.0.3 application_template 2.1.0 - 3.0.3 applications 1.3.1 - 3.0.3 auditlog 1.3.1 - 3.0.3 DATA auditlog_details 1.7 - 3.0.3 DATA autoreg 1.3.1 - 1.3.4 autoreg_host 1.7 - 3.0.3 conditions 1.3.1 - 3.0.3 config 1.3.1 - 3.0.3 dbversion 2.1.0 - 3.0.3 dchecks 1.3.4 - 3.0.3 dhosts 1.3.4 - 3.0.3 drules 1.3.4 - 3.0.3 dservices 1.3.4 - 3.0.3 escalations 1.5.3 - 3.0.3 events 1.3.1 - 3.0.3 DATA expressions 1.7 - 3.0.3 functions 1.3.1 - 3.0.3 globalmacro 1.7 - 3.0.3 globalvars 1.9.6 - 3.0.3 graph_discovery 1.9.0 - 3.0.3 graph_theme 1.7 - 3.0.3 graphs 1.3.1 - 3.0.3 graphs_items 1.3.1 - 3.0.3 group_discovery 2.1.4 - 3.0.3 group_prototype 2.1.4 - 3.0.3 groups 1.3.1 - 3.0.3 help_items 1.3.1 - 2.1.8 history 1.3.1 - 3.0.3 DATA history_log 1.3.1 - 3.0.3 DATA history_str 1.3.1 - 3.0.3 DATA history_str_sync 1.3.1 - 2.2.13 DATA history_sync 1.3.1 - 2.2.13 DATA history_text 1.3.1 - 3.0.3 DATA history_uint 1.3.1 - 3.0.3 DATA history_uint_sync 1.3.1 - 2.2.13 DATA host_discovery 2.1.4 - 3.0.3 host_inventory 1.9.6 - 3.0.3 host_profile 1.9.3 - 1.9.5 hostmacro 1.7 - 3.0.3 hosts 1.3.1 - 3.0.3 hosts_groups 1.3.1 - 3.0.3 hosts_profiles 1.3.1 - 1.9.2 hosts_profiles_ext 1.6 - 1.9.2 hosts_templates 1.3.1 - 3.0.3 housekeeper 1.3.1 - 3.0.3 httpstep 1.3.3 - 3.0.3 httpstepitem 1.3.3 - 3.0.3 httptest 1.3.3 - 3.0.3 httptestitem 1.3.3 - 3.0.3 icon_map 1.9.6 - 3.0.3 icon_mapping 1.9.6 - 3.0.3 ids 1.3.3 - 3.0.3 images 1.3.1 - 3.0.3 interface 1.9.1 - 3.0.3 interface_discovery 2.1.4 - 3.0.3 item_application_prototype 2.5.0 - 3.0.3 item_condition 2.3.0 - 3.0.3 item_discovery 1.9.0 - 3.0.3 items 1.3.1 - 3.0.3 items_applications 1.3.1 - 3.0.3 maintenances 1.7 - 3.0.3 maintenances_groups 1.7 - 3.0.3 maintenances_hosts 1.7 - 3.0.3 maintenances_windows 1.7 - 3.0.3 mappings 1.3.1 - 3.0.3 media 1.3.1 - 3.0.3 media_type 1.3.1 - 3.0.3 node_cksum 1.3.1 - 2.2.13 node_configlog 1.3.1 - 1.4.7 nodes 1.3.1 - 2.2.13 opcommand 1.9.4 - 3.0.3 opcommand_grp 1.9.2 - 3.0.3 opcommand_hst 1.9.2 - 3.0.3 opconditions 1.5.3 - 3.0.3 operations 1.3.4 - 3.0.3 opgroup 1.9.2 - 3.0.3 opinventory 3.0.0 - 3.0.3 opmediatypes 1.7 - 1.8.22 opmessage 1.9.2 - 3.0.3 opmessage_grp 1.9.2 - 3.0.3 opmessage_usr 1.9.2 - 3.0.3 optemplate 1.9.2 - 3.0.3 profiles 1.3.1 - 3.0.3 proxy_autoreg_host 1.7 - 3.0.3 proxy_dhistory 1.5 - 3.0.3 proxy_history 1.5.1 - 3.0.3 regexps 1.7 - 3.0.3 rights 1.3.1 - 3.0.3 screen_user 3.0.0 - 3.0.3 screen_usrgrp 3.0.0 - 3.0.3 screens 1.3.1 - 3.0.3 screens_items 1.3.1 - 3.0.3 scripts 1.5 - 3.0.3 service_alarms 1.3.1 - 3.0.3 services 1.3.1 - 3.0.3 services_links 1.3.1 - 3.0.3 services_times 1.3.1 - 3.0.3 sessions 1.3.1 - 3.0.3 slides 1.3.4 - 3.0.3 slideshow_user 3.0.0 - 3.0.3 slideshow_usrgrp 3.0.0 - 3.0.3 slideshows 1.3.4 - 3.0.3 sysmap_element_url 1.9.0 - 3.0.3 sysmap_url 1.9.0 - 3.0.3 sysmap_user 3.0.0 - 3.0.3 sysmap_usrgrp 3.0.0 - 3.0.3 sysmaps 1.3.1 - 3.0.3 sysmaps_elements 1.3.1 - 3.0.3 sysmaps_link_triggers 1.5 - 3.0.3 sysmaps_links 1.3.1 - 3.0.3 timeperiods 1.7 - 3.0.3 trends 1.3.1 - 3.0.3 DATA trends_uint 1.5 - 3.0.3 DATA trigger_depends 1.3.1 - 3.0.3 trigger_discovery 1.9.0 - 3.0.3 triggers 1.3.1 - 3.0.3 user_history 1.7 - 2.4.8 users 1.3.1 - 3.0.3 users_groups 1.3.1 - 3.0.3 usrgrp 1.3.1 - 3.0.3 valuemaps 1.3.1 - 3.0.3
I agree to the Idomaster Terms and Conditions, Privacy Policy
Description
#!/usr/bin/env bash # # NAME # zabbix-mysql-dump - Configuration Backup for Zabbix with MySQL # # VERSION # 0.8.2 # # SYNOPSIS # This is a MySQL configuration backup script for Zabbix 1.x, 2.x and 3.0.x. # It does a full backup of all configuration tables, but only a schema # backup of large data tables. # # The script is based on a script by Ricardo Santos # (http://zabbixzone.com/zabbix/backuping-only-the-zabbix-configuration/) # # CONTRIBUTORS # - Ricardo Santos # - Jens Berthold (maxhq) # - Oleksiy Zagorskyi (zalex) # - Petr Jendrejovsky # - Jonathan Bayer # - Andreas Niedermann (dre-) # - Mișu Moldovan (dumol) # - Daniel Schneller (dschneller) # - Ruslan Ohitin (ruslan-ohitin) # - Jonathan Wright (neonardo1) # - msjmeyer # # AUTHOR # Jens Berthold (maxhq), 2016 # # LICENSE # This script is released under the MIT License (see LICENSE.txt) # # DEFAULT VALUES # # DO NOT EDIT THESE VALUES! # Instead, use command line parameters or a config file to specify options. # DUMPDIR="$PWD" DBHOST="127.0.0.1" DBNAME="zabbix" DBUSER="zabbix" DBPASS="" COMPRESSION="gz" QUIET="no" REVERSELOOKUP="yes" GENERATIONSTOKEEP=0 # # SHOW HELP # if [ -z "$1" ]; then cat <<EOF USAGE $(basename $BASH_SOURCE) [options] OPTIONS -h HOST Hostname/IP of MySQL server. Default: $DBHOST -d DATABASE Zabbix database name. Default: $DBNAME -u USER MySQL user to access Zabbix database. Default: $DBUSER -p PASSWORD MySQL user password (specify "-" for a prompt). Default: no password -o DIR Save Zabbix MySQL dumps to DIR. Default: $DUMPDIR -c FILE Use FILE for MySQL options (passed via --defaults-extra-file). PLEASE NOTE: mysqldump needs the database to be specified via command line. So the first "database" options found in the config file is used for mysqldump. -r NUM Rotate backups while keeping up to NUM generations. Uses filename to match. Default: keep all backups -x Compress using xz instead of gz PLEASE NOTE: xz compression will take much longer and consume more CPU time but the resulting backup will be about half the size of the same sql file compressed using gz. Your mileage may vary. -0 Do not compress the sql dump -n Skip reverse lookup of IP address for host. -q Quiet mode: no output except for errors (for batch/crontab use). EXAMPLES $(basename $BASH_SOURCE) -h 1.2.3.4 -d zabbixdb -u zabbix -p test $(basename $BASH_SOURCE) -u zabbix -p - -o /tmp $(basename $BASH_SOURCE) -c /etc/mysql/mysql.cnf $(basename $BASH_SOURCE) -c /etc/mysql/mysql.cnf -d zabbixdb EOF exit 1 fi # # PARSE COMMAND LINE ARGUMENTS # DB_GIVEN=0 while getopts ":h:d:u:p:o:r:c:x0qn" opt; do case $opt in h) DBHOST="$OPTARG" ;; d) DBNAME="$OPTARG"; DB_GIVEN=1 ;; u) DBUSER="$OPTARG" ;; p) DBPASS="$OPTARG" ;; c) CNFFILE="$OPTARG" ;; o) DUMPDIR="$OPTARG" ;; r) GENERATIONSTOKEEP=$(printf '%.0f' "$OPTARG") ;; x) COMPRESSION="xz" ;; 0) COMPRESSION="" ;; n) REVERSELOOKUP="no" ;; q) QUIET="yes" ;; \?) echo "Invalid option: -$OPTARG" >&2; exit 1 ;; :) echo "Option -$OPTARG requires an argument" >&2; exit 1 ;; esac done # Password prompt if [ "$DBPASS" = "-" ]; then read -s -p "Enter MySQL password for user '$DBUSER' (input will be hidden): " DBPASS echo "" fi # Config file validations if [ ! -z "$CNFFILE" ]; then if [ ! -r "$CNFFILE" ]; then echo "ERROR: Cannot read configuration file $CNFFILE" >&2 exit 1 fi # Database name needs special treatment: # For mysqldump it has to be specified on the command line! # Therefore we need to get it from the config file if [ $DB_GIVEN -eq 0 ]; then DBNAME=$(grep -m 1 ^database= "$CNFFILE" | cut -d= -f2) fi fi # # CONSTANTS # SUFFIX=""; test ! -z $COMPRESSION && SUFFIX=".${COMPRESSION}" MYSQL_OPTS=() [ ! -z "$CNFFILE" ] && MYSQL_OPTS=("${MYSQL_OPTS[@]}" --defaults-extra-file="$CNFFILE") [ ! -z "$DBHOST" ] && MYSQL_OPTS=("${MYSQL_OPTS[@]}" -h $DBHOST) [ ! -z "$DBUSER" ] && MYSQL_OPTS=("${MYSQL_OPTS[@]}" -u $DBUSER) [ ! -z "$DBPASS" ] && MYSQL_OPTS=("${MYSQL_OPTS[@]}" -p"$DBPASS") MYSQL_OPTS_BATCH=("${MYSQL_OPTS[@]}" --batch --silent) [ ! -z "$DBNAME" ] && MYSQL_OPTS_BATCH=("${MYSQL_OPTS_BATCH[@]}" -D $DBNAME) # Log file for errors ERRORLOG=$(mktemp) # Host name: try reverse lookup if IP is given DBHOSTNAME="$DBHOST" command -v dig >/dev/null 2>&1 FIND_DIG=$? if [ "$REVERSELOOKUP" == "yes" -a $FIND_DIG -eq 0 ]; then # Try resolving a given host ip newHostname=$(dig +noall +answer -x $DBHOST | sed -r 's/((\S+)\s+)+([^\.]+)\..*/\3/') test \! -z "$newHostname" && DBHOSTNAME="$newHostname" fi # # CONFIG DUMP # if [ "$QUIET" == "no" ]; then cat <<-EOF Configuration: - host: $DBHOST ($DBHOSTNAME) - database: $DBNAME - user: $DBUSER - output: $DUMPDIR EOF fi # # FUNCTIONS # # Returns TRUE if argument 1 is part of the given array (remaining arguments) elementIn () { local e for e in "${@:2}"; do [[ "$e" == "$1" ]] && return 0; done return 1 } # # CHECKS # if [ ! -x /usr/bin/mysqldump ]; then echo "mysqldump not found." >&2 echo "(with Debian, \"apt-get install mysql-client\" will help)" >&2 exit 1 fi # # READ TABLE LIST from __DATA__ section at the end of this script # (http://stackoverflow.com/a/3477269/2983301) # DATA_TABLES=() while read line; do table=$(echo "$line" | cut -d" " -f1) echo "$line" | cut -d" " -f5 | grep -qi "DATA" test $? -eq 0 && DATA_TABLES+=($table) done < <(sed '0,/^__DATA__$/d' "$BASH_SOURCE" | tr -s " ") # paranoid check if [ ${#DATA_TABLES[@]} -lt 5 ]; then echo "ERROR: The number of large data tables configured in this script is less than 5." >&2 exit 1 fi # # BACKUP # # Read table list from database [ "$QUIET" == "no" ] && echo "Fetching list of existing tables..." DB_TABLES=$(mysql "${MYSQL_OPTS_BATCH[@]}" -e "SELECT table_name FROM information_schema.tables WHERE table_schema = '$DBNAME'" 2>$ERRORLOG) if [ $? -ne 0 ]; then echo "ERROR while trying to access database:" 2>&1; cat $ERRORLOG 2>&1; exit 1; fi DB_TABLES=$(echo "$DB_TABLES" | sort) DB_TABLE_NUM=$(echo "$DB_TABLES" | wc -l) # Query Zabbix database version VERSION="" DB_VER=$(mysql "${MYSQL_OPTS_BATCH[@]}" -N -e "select optional from dbversion;" 2>/dev/null) if [ $? -eq 0 ]; then # version string is like: 02030015 re='(.*)([0-9]{2})([0-9]{4})' if [[ $DB_VER =~ $re ]]; then VERSION="_db-${BASH_REMATCH[1]}.$(( ${BASH_REMATCH[2]} + 0 )).$(( ${BASH_REMATCH[3]} + 0 ))" fi fi # Assemble file name DUMPFILENAME_PREFIX="zabbix_cfg_${DBHOSTNAME}" DUMPFILEBASE="${DUMPFILENAME_PREFIX}_$(date +%Y%m%d-%H%M)${VERSION}.sql" DUMPFILE="$DUMPDIR/$DUMPFILEBASE" PROCESSED_DATA_TABLES=() i=0 mkdir -p "${DUMPDIR}" [ "$QUIET" == "no" ] && echo "Starting table backups..." while read table; do # large data tables: only store schema if elementIn "$table" "${DATA_TABLES[@]}"; then dump_opt="--no-data" PROCESSED_DATA_TABLES+=($table) # configuration tables: full dump else dump_opt="--extended-insert=FALSE" fi mysqldump "${MYSQL_OPTS[@]}" \ --routines --opt --single-transaction --skip-lock-tables \ $dump_opt \ $DBNAME --tables ${table} >> "$DUMPFILE" 2>$ERRORLOG if [ $? -ne 0 ]; then echo -e "\nERROR: Could not backup table ${table}:" >&2; cat $ERRORLOG >&2; exit 1; fi if [ "$QUIET" == "no" ]; then # show percentage i=$((i+1)); i_percent=$(($i * 100 / $DB_TABLE_NUM)) if [ $(($i_percent % 12)) -eq 0 ]; then echo -n "${i_percent}%" else if [ $(($i_percent % 2)) -eq 0 ]; then echo -n "."; fi fi fi done <<<"$DB_TABLES" rm $ERRORLOG # # COMPRESS BACKUP # if [ "$QUIET" == "no" ]; then echo -e "\n" echo "For the following large tables only the schema (without data) was stored:" for table in "${PROCESSED_DATA_TABLES[@]}"; do echo " - $table"; done echo echo "Compressing backup file..." fi EXITCODE=0 if [ "$COMPRESSION" == "gz" ]; then gzip -f "$DUMPFILE"; EXITCODE=$?; fi if [ "$COMPRESSION" == "xz" ]; then xz -f "$DUMPFILE"; EXITCODE=$?; fi if [ $EXITCODE -ne 0 ]; then echo -e "\nERROR: Could not compress backup file, see previous messages" >&2 exit 1 fi [ "$QUIET" == "no" ] && echo -e "\nBackup Completed:\n${DUMPFILE}${SUFFIX}" # # ROTATE OLD BACKUPS # if [ $GENERATIONSTOKEEP -gt 0 ]; then [ "$QUIET" == "no" ] && echo "Removing old backups, keeping up to $GENERATIONSTOKEEP" REMOVE_OLD_CMD="cd \"$DUMPDIR\" && ls -t \"${DUMPFILENAME_PREFIX}\"* | /usr/bin/awk \"NR>${GENERATIONSTOKEEP}\" | xargs rm -f " eval ${REMOVE_OLD_CMD} if [ $? -ne 0 ]; then echo "ERROR: Could not rotate old backups" >&2 exit 1 fi fi exit 0 ################################################################################ # List of all known table names and a flag indicating data (=large) tables # __DATA__ acknowledges 1.3.1 - 3.0.3 DATA actions 1.3.1 - 3.0.3 alerts 1.3.1 - 3.0.3 DATA application_discovery 2.5.0 - 3.0.3 application_prototype 2.5.0 - 3.0.3 application_template 2.1.0 - 3.0.3 applications 1.3.1 - 3.0.3 auditlog 1.3.1 - 3.0.3 DATA auditlog_details 1.7 - 3.0.3 DATA autoreg 1.3.1 - 1.3.4 autoreg_host 1.7 - 3.0.3 conditions 1.3.1 - 3.0.3 config 1.3.1 - 3.0.3 dbversion 2.1.0 - 3.0.3 dchecks 1.3.4 - 3.0.3 dhosts 1.3.4 - 3.0.3 drules 1.3.4 - 3.0.3 dservices 1.3.4 - 3.0.3 escalations 1.5.3 - 3.0.3 events 1.3.1 - 3.0.3 DATA expressions 1.7 - 3.0.3 functions 1.3.1 - 3.0.3 globalmacro 1.7 - 3.0.3 globalvars 1.9.6 - 3.0.3 graph_discovery 1.9.0 - 3.0.3 graph_theme 1.7 - 3.0.3 graphs 1.3.1 - 3.0.3 graphs_items 1.3.1 - 3.0.3 group_discovery 2.1.4 - 3.0.3 group_prototype 2.1.4 - 3.0.3 groups 1.3.1 - 3.0.3 help_items 1.3.1 - 2.1.8 history 1.3.1 - 3.0.3 DATA history_log 1.3.1 - 3.0.3 DATA history_str 1.3.1 - 3.0.3 DATA history_str_sync 1.3.1 - 2.2.13 DATA history_sync 1.3.1 - 2.2.13 DATA history_text 1.3.1 - 3.0.3 DATA history_uint 1.3.1 - 3.0.3 DATA history_uint_sync 1.3.1 - 2.2.13 DATA host_discovery 2.1.4 - 3.0.3 host_inventory 1.9.6 - 3.0.3 host_profile 1.9.3 - 1.9.5 hostmacro 1.7 - 3.0.3 hosts 1.3.1 - 3.0.3 hosts_groups 1.3.1 - 3.0.3 hosts_profiles 1.3.1 - 1.9.2 hosts_profiles_ext 1.6 - 1.9.2 hosts_templates 1.3.1 - 3.0.3 housekeeper 1.3.1 - 3.0.3 httpstep 1.3.3 - 3.0.3 httpstepitem 1.3.3 - 3.0.3 httptest 1.3.3 - 3.0.3 httptestitem 1.3.3 - 3.0.3 icon_map 1.9.6 - 3.0.3 icon_mapping 1.9.6 - 3.0.3 ids 1.3.3 - 3.0.3 images 1.3.1 - 3.0.3 interface 1.9.1 - 3.0.3 interface_discovery 2.1.4 - 3.0.3 item_application_prototype 2.5.0 - 3.0.3 item_condition 2.3.0 - 3.0.3 item_discovery 1.9.0 - 3.0.3 items 1.3.1 - 3.0.3 items_applications 1.3.1 - 3.0.3 maintenances 1.7 - 3.0.3 maintenances_groups 1.7 - 3.0.3 maintenances_hosts 1.7 - 3.0.3 maintenances_windows 1.7 - 3.0.3 mappings 1.3.1 - 3.0.3 media 1.3.1 - 3.0.3 media_type 1.3.1 - 3.0.3 node_cksum 1.3.1 - 2.2.13 node_configlog 1.3.1 - 1.4.7 nodes 1.3.1 - 2.2.13 opcommand 1.9.4 - 3.0.3 opcommand_grp 1.9.2 - 3.0.3 opcommand_hst 1.9.2 - 3.0.3 opconditions 1.5.3 - 3.0.3 operations 1.3.4 - 3.0.3 opgroup 1.9.2 - 3.0.3 opinventory 3.0.0 - 3.0.3 opmediatypes 1.7 - 1.8.22 opmessage 1.9.2 - 3.0.3 opmessage_grp 1.9.2 - 3.0.3 opmessage_usr 1.9.2 - 3.0.3 optemplate 1.9.2 - 3.0.3 profiles 1.3.1 - 3.0.3 proxy_autoreg_host 1.7 - 3.0.3 proxy_dhistory 1.5 - 3.0.3 proxy_history 1.5.1 - 3.0.3 regexps 1.7 - 3.0.3 rights 1.3.1 - 3.0.3 screen_user 3.0.0 - 3.0.3 screen_usrgrp 3.0.0 - 3.0.3 screens 1.3.1 - 3.0.3 screens_items 1.3.1 - 3.0.3 scripts 1.5 - 3.0.3 service_alarms 1.3.1 - 3.0.3 services 1.3.1 - 3.0.3 services_links 1.3.1 - 3.0.3 services_times 1.3.1 - 3.0.3 sessions 1.3.1 - 3.0.3 slides 1.3.4 - 3.0.3 slideshow_user 3.0.0 - 3.0.3 slideshow_usrgrp 3.0.0 - 3.0.3 slideshows 1.3.4 - 3.0.3 sysmap_element_url 1.9.0 - 3.0.3 sysmap_url 1.9.0 - 3.0.3 sysmap_user 3.0.0 - 3.0.3 sysmap_usrgrp 3.0.0 - 3.0.3 sysmaps 1.3.1 - 3.0.3 sysmaps_elements 1.3.1 - 3.0.3 sysmaps_link_triggers 1.5 - 3.0.3 sysmaps_links 1.3.1 - 3.0.3 timeperiods 1.7 - 3.0.3 trends 1.3.1 - 3.0.3 DATA trends_uint 1.5 - 3.0.3 DATA trigger_depends 1.3.1 - 3.0.3 trigger_discovery 1.9.0 - 3.0.3 triggers 1.3.1 - 3.0.3 user_history 1.7 - 2.4.8 users 1.3.1 - 3.0.3 users_groups 1.3.1 - 3.0.3 usrgrp 1.3.1 - 3.0.3 valuemaps 1.3.1 - 3.0.3