#!/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 and 2.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)
#
# HISTORY
# 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
#
# 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>&1)
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 - 2.4.0 DATA
actions 1.3.1 - 2.4.0
alerts 1.3.1 - 2.4.0 DATA
application_template 2.1.0 - 2.4.0
applications 1.3.1 - 2.4.0
auditlog 1.3.1 - 2.4.0 DATA
auditlog_details 1.7 - 2.4.0 DATA
autoreg 1.3.1 - 1.3.4
autoreg_host 1.7 - 2.4.0
conditions 1.3.1 - 2.4.0
config 1.3.1 - 2.4.0
dbversion 2.1.0 - 2.4.0
dchecks 1.3.4 - 2.4.0
dhosts 1.3.4 - 2.4.0
drules 1.3.4 - 2.4.0
dservices 1.3.4 - 2.4.0
escalations 1.5.3 - 2.4.0
events 1.3.1 - 2.4.0 DATA
expressions 1.7 - 2.4.0
functions 1.3.1 - 2.4.0
globalmacro 1.7 - 2.4.0
globalvars 1.9.6 - 2.4.0
graph_discovery 1.9.0 - 2.4.0
graph_theme 1.7 - 2.4.0
graphs 1.3.1 - 2.4.0
graphs_items 1.3.1 - 2.4.0
group_discovery 2.1.4 - 2.4.0
group_prototype 2.1.4 - 2.4.0
groups 1.3.1 - 2.4.0
help_items 1.3.1 - 2.1.8
history 1.3.1 - 2.4.0 DATA
history_log 1.3.1 - 2.4.0 DATA
history_str 1.3.1 - 2.4.0 DATA
history_str_sync 1.3.1 - 2.2.6 DATA
history_sync 1.3.1 - 2.2.6 DATA
history_text 1.3.1 - 2.4.0 DATA
history_uint 1.3.1 - 2.4.0 DATA
history_uint_sync 1.3.1 - 2.2.6 DATA
host_discovery 2.1.4 - 2.4.0
host_inventory 1.9.6 - 2.4.0
host_profile 1.9.3 - 1.9.5
hostmacro 1.7 - 2.4.0
hosts 1.3.1 - 2.4.0
hosts_groups 1.3.1 - 2.4.0
hosts_profiles 1.3.1 - 1.9.2
hosts_profiles_ext 1.6 - 1.9.2
hosts_templates 1.3.1 - 2.4.0
housekeeper 1.3.1 - 2.4.0
httpstep 1.3.3 - 2.4.0
httpstepitem 1.3.3 - 2.4.0
httptest 1.3.3 - 2.4.0
httptestitem 1.3.3 - 2.4.0
icon_map 1.9.6 - 2.4.0
icon_mapping 1.9.6 - 2.4.0
ids 1.3.3 - 2.4.0
images 1.3.1 - 2.4.0
interface 1.9.1 - 2.4.0
interface_discovery 2.1.4 - 2.4.0
item_condition 2.3.0 - 2.4.0
item_discovery 1.9.0 - 2.4.0
items 1.3.1 - 2.4.0
items_applications 1.3.1 - 2.4.0
maintenances 1.7 - 2.4.0
maintenances_groups 1.7 - 2.4.0
maintenances_hosts 1.7 - 2.4.0
maintenances_windows 1.7 - 2.4.0
mappings 1.3.1 - 2.4.0
media 1.3.1 - 2.4.0
media_type 1.3.1 - 2.4.0
node_cksum 1.3.1 - 2.2.6
node_configlog 1.3.1 - 1.4.7
nodes 1.3.1 - 2.2.6
opcommand 1.9.4 - 2.4.0
opcommand_grp 1.9.2 - 2.4.0
opcommand_hst 1.9.2 - 2.4.0
opconditions 1.5.3 - 2.4.0
operations 1.3.4 - 2.4.0
opgroup 1.9.2 - 2.4.0
opmediatypes 1.7 - 1.8.21
opmessage 1.9.2 - 2.4.0
opmessage_grp 1.9.2 - 2.4.0
opmessage_usr 1.9.2 - 2.4.0
optemplate 1.9.2 - 2.4.0
profiles 1.3.1 - 2.4.0
proxy_autoreg_host 1.7 - 2.4.0
proxy_dhistory 1.5 - 2.4.0
proxy_history 1.5.1 - 2.4.0
regexps 1.7 - 2.4.0
rights 1.3.1 - 2.4.0
screens 1.3.1 - 2.4.0
screens_items 1.3.1 - 2.4.0
scripts 1.5 - 2.4.0
service_alarms 1.3.1 - 2.4.0
services 1.3.1 - 2.4.0
services_links 1.3.1 - 2.4.0
services_times 1.3.1 - 2.4.0
sessions 1.3.1 - 2.4.0
slides 1.3.4 - 2.4.0
slideshows 1.3.4 - 2.4.0
sysmap_element_url 1.9.0 - 2.4.0
sysmap_url 1.9.0 - 2.4.0
sysmaps 1.3.1 - 2.4.0
sysmaps_elements 1.3.1 - 2.4.0
sysmaps_link_triggers 1.5 - 2.4.0
sysmaps_links 1.3.1 - 2.4.0
timeperiods 1.7 - 2.4.0
trends 1.3.1 - 2.4.0 DATA
trends_uint 1.5 - 2.4.0 DATA
trigger_depends 1.3.1 - 2.4.0
trigger_discovery 1.9.0 - 2.4.0
triggers 1.3.1 - 2.4.0
user_history 1.7 - 2.4.0
users 1.3.1 - 2.4.0
users_groups 1.3.1 - 2.4.0
usrgrp 1.3.1 - 2.4.0
valuemaps 1.3.1 - 2.4.0