#!/usr/bin/env bash
#
# NAME
# zabbix-mysql-dump - Configuration Backup for Zabbix with MySQL
#
# 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)
#
# HISTORY
# 0.8.1 (2016-07-11)
# ENH: Added Zabbix 3.0.x tables to list (added & tested by Ruslan Ohitin)
#
# 0.8.0 (2016-01-22)
# FIX: Only invoke `dig` if available
# ENH: Option -c to use a MySQL config ("options") file (suggested by Daniel Schneller)
# ENH: Option -r to rotate backup files (Daniel Schneller)
# ENH: Add database version to filename if available
# ENH: Add quiet mode. IP reverse lookup optional (Daniel Schneller)
# ENH: Bash related fixes (Misu Moldovan)
# CHG: Default output directory is now $PWD instead of script dir
#
# 0.7.1 (2015-01-27)
# NEW: Parsing of commandline arguments implemented
# ENH: Try reverse lookup of IPs and include hostname/IP in filename
# REV: Stop if database password is wrong
#
# 0.7.0 (2014-10-02)
# ENH: Complete overhaul to make script work with lots of Zabbix versions
#
# 0.6.0 (2014-09-15)
# REV: Updated the table list for use with zabbix v2.2.3
#
# 0.5.0 (2013-05-13)
# NEW: Added table list comparison between database and script
#
# 0.4.0 (2012-03-02)
# REV: Incorporated mysqldump options (suggested by Jonathan Bayer)
#
# 0.3.0 (2012-02-06)
# ENH: Backup of Zabbix 1.9.x / 2.0.0, removed unnecessary use of
# variables (DATEBIN etc) for commands that use to be in $PATH
#
# 0.2.0 (2011-11-05)
#
# 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=""
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
-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:qn" 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") ;;
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
#
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)
# 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>&1)
if [ $? -ne 0 ]; then echo -e "ERROR while trying to access database:\n$DB_TABLES" 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"
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"
#
# 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
gzip -f "$DUMPFILE"
if [ $? -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}.gz"
#
# 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