Backuping only the Zabbix Configuration

Posted by Ricardo Santos in backup, database, mysql, scripts, zabbix on May 30th, 2011 | 23 comments

This script is a simple way to backup all configuration tables (eg. templates, hostgroups, hosts, triggers…) without the history data.

If you need do a full backup (history included) I recommend you this post: http://zabbixzone.com/zabbix/backuping-full-database/

As the result is very small (around 30 MB), is possible run this backup many times per day.

https://github.com/xsbr/zabbixzone/blob/master/zabbix-mysql-backupconf.sh

#!/bin/bash
#
# zabbix-mysql-backupconf.sh
# v0.2 - 20111105
#
# Configuration Backup for Zabbix 1.8 w/MySQL
#
# Author: Ricardo Santos (rsantos at gmail.com)
# http://zabbixzone.com
#
# Thanks for suggestions from:
# - Oleksiy Zagorskyi (zalex)
# - Petr Jendrejovsky
#

# mysql config
DBHOST="localhost"
DBNAME="zabbix"
DBUSER="zabbix"
DBPASS="YOURMYSQLPASSWORDHERE"

# some tools
MYSQLDUMP="`which mysqldump`"
GZIP="`which gzip`"
DATEBIN="`which date`"
MKDIRBIN="`which mkdir`"

# target path
MAINDIR="/var/lib/zabbix/backupconf"
DUMPDIR="${MAINDIR}/`${DATEBIN} +%Y%m%d%H%M`"
${MKDIRBIN} -p ${DUMPDIR}

# configuration tables
CONFTABLES=( actions applications autoreg_host conditions config dchecks dhosts \
drules dservices escalations expressions functions globalmacro graph_theme \
graphs graphs_items groups help_items hostmacro hosts hosts_groups \
hosts_profiles hosts_profiles_ext hosts_templates housekeeper httpstep \
httpstepitem httptest httptestitem ids images items items_applications \
maintenances maintenances_groups maintenances_hosts maintenances_windows \
mappings media media_type node_cksum nodes opconditions operations \
opmediatypes profiles proxy_autoreg_host proxy_dhistory proxy_history regexps \
rights screens screens_items scripts service_alarms services services_links \
services_times sessions slides slideshows sysmaps sysmaps_elements \
sysmaps_link_triggers sysmaps_links timeperiods trigger_depends triggers \
user_history users users_groups usrgrp valuemaps )

# tables with large data
DATATABLES=( acknowledges alerts auditlog_details auditlog events \
history history_log history_str history_str_sync history_sync history_text \
history_uint history_uint_sync trends trends_uint )

# CONFTABLES
for table in ${CONFTABLES[*]}; do
        DUMPFILE="${DUMPDIR}/${table}.sql"
        echo "Backuping table ${table}"
        ${MYSQLDUMP} -R --opt --extended-insert=FALSE \
                -h ${DBHOST} -u ${DBUSER} -p${DBPASS} ${DBNAME} --tables ${table} >${DUMPFILE}
        ${GZIP} -f ${DUMPFILE}
done

# DATATABLES
for table in ${DATATABLES[*]}; do
        DUMPFILE="${DUMPDIR}/${table}.sql"
        echo "Backuping schema table ${table}"
        ${MYSQLDUMP} -R --opt --no-data	\
                -h ${DBHOST} -u ${DBUSER} -p${DBPASS} ${DBNAME} --tables ${table} >${DUMPFILE}
        ${GZIP} -f ${DUMPFILE}
done

echo
echo "Backup Completed - ${DUMPDIR}"

#!/bin/bash

# zabbix-mysql-backupconf.sh

# v0.2 - 20111105

# Configuration Backup for Zabbix 1.8 w/MySQL

# Author: Ricardo Santos (rsantos at gmail.com)

# http://zabbixzone.com

# Thanks for suggestions from:

# - Oleksiy Zagorskyi (zalex)

# - Petr Jendrejovsky

# mysql config

DBHOST="localhost"

DBNAME="zabbix"

DBUSER="zabbix"

DBPASS="YOURMYSQLPASSWORDHERE"

# some tools

MYSQLDUMP="`which mysqldump`"

GZIP="`which gzip`"

DATEBIN="`which date`"

MKDIRBIN="`which mkdir`"

# target path

MAINDIR="/var/lib/zabbix/backupconf"

DUMPDIR="${MAINDIR}/`${DATEBIN} +%Y%m%d%H%M`"

${MKDIRBIN} -p ${DUMPDIR}

# configuration tables

CONFTABLES=( actions applications autoreg_host conditions config dchecks dhosts \

drules dservices escalations expressions functions globalmacro graph_theme \

graphs graphs_items groups help_items hostmacro hosts hosts_groups \

hosts_profiles hosts_profiles_ext hosts_templates housekeeper httpstep \

httpstepitem httptest httptestitem ids images items items_applications \

maintenances maintenances_groups maintenances_hosts maintenances_windows \

mappings media media_type node_cksum nodes opconditions operations \

opmediatypes profiles proxy_autoreg_host proxy_dhistory proxy_history regexps \

rights screens screens_items scripts service_alarms services services_links \

services_times sessions slides slideshows sysmaps sysmaps_elements \

sysmaps_link_triggers sysmaps_links timeperiods trigger_depends triggers \

user_history users users_groups usrgrp valuemaps )

# tables with large data

DATATABLES=( acknowledges alerts auditlog_details auditlog events \

history history_log history_str history_str_sync history_sync history_text \

history_uint history_uint_sync trends trends_uint )

# CONFTABLES

for table in ${CONFTABLES[*]}; do

DUMPFILE="${DUMPDIR}/${table}.sql"

echo "Backuping table ${table}"

${MYSQLDUMP} -R --opt --extended-insert=FALSE \

-h ${DBHOST} -u ${DBUSER} -p${DBPASS} ${DBNAME} --tables ${table} >${DUMPFILE}

${GZIP} -f ${DUMPFILE}

done

# DATATABLES

for table in ${DATATABLES[*]}; do

DUMPFILE="${DUMPDIR}/${table}.sql"

echo "Backuping schema table ${table}"

${MYSQLDUMP} -R --opt --no-data \

-h ${DBHOST} -u ${DBUSER} -p${DBPASS} ${DBNAME} --tables ${table} >${DUMPFILE}

${GZIP} -f ${DUMPFILE}

done

echo

echo "Backup Completed - ${DUMPDIR}"

23 Responses to “ “Backuping only the Zabbix Configuration”

0siris says:

May 31, 2011 at 10:10 am

Hi there, nice script!
Did you ever consider doing the backup with Percona Xtrabackup? It should be able to do incremental backups, quite nice software (so is Percona Server I think), see http://www.percona.com/software/percona-xtrabackup/

Reply
- Ricardo Santos says:
  
  May 31, 2011 at 11:09 am
  
  Hi 0siris,
  
  Thanks for the feedback.
  
  I’ll talk about Xtrabackup in the next post. I’m using it and is really amazing.
  
  Reply
Petr Jendrejovský says:

June 23, 2011 at 5:09 am

Great! Thank you!

Just one note – it would be better to use /bin/bash or something similar, because with simple /bin/sh it’s not working on all systems (for example Ubuntu 10.04).

Reply
- Oleksiy Zagorskyi says:
  
  November 5, 2011 at 1:30 pm
  
  I vote for this change.
  
  Reply
Oleksiy Zagorskyi says:

November 5, 2011 at 1:28 pm

Ricardo,
The table “actions” is not data table, it’s configuration table !!!
>>>>> I think you have to fix the script. <<<<<

Would be better try to find the binaries instead of "hardcode" their location. IMO.

Would be better to use option "–no-data" instead of "-d" because it's more clear and well known. IMO.

—
zalex

Reply
- Ricardo Santos says:
  
  November 5, 2011 at 3:04 pm
  
  Zalex,
  
  thanks for your suggestion.
  
  I’ll put this script and others in a public repository like github or lauchpad to become more easy for people contributing.
  
  Reply
- Ricardo Santos says:
  
  November 6, 2011 at 12:14 am
  
  Now in GitHub – https://github.com/xsbr/zabbixzone/blob/master/zabbix-mysql-backupconf.sh
  
  Reply
Vinício Zanchettin says:

January 3, 2012 at 2:00 pm

I used this script and it works very well.
Thanks!

Reply
fjrial says:

January 25, 2012 at 6:30 am

This approach has a problem:

mysqldump does a read lock on tables, so dumping large tables is neither impossible without stoping zabbix services..

I configured my mysql server with master-server replication, so this way, I can stop anyway the slave, dump the entire db, and start again the slave without stoping zabbix services.

Anyways, thanks for sharing

Reply
Araceli Calabresi says:

February 1, 2012 at 12:00 pm

Hi Ricardo!

This is a nice script. Do you know if someone tried to use it with Oracle11GR2?

Thanks for sharing.
Araceli

Reply
- Ricardo Santos says:
  
  February 6, 2012 at 1:50 pm
  
  Hi Araceli,
  
  No, this script is only for MySQL.
  
  But the simple way to do it is backup all schemas and the tables defined in “CONFTABLES” variable.
  
  Reply
Jens Berthold says:

February 6, 2012 at 1:30 pm

Great script!

I think changing your commands to something like
MYSQLDUMP=”which mysqldump”
doesn’t make much sense, as the “which” command just searches the $PATH – what is also done when you directly call the command without any path.

So you can either just write
MYSQLDUMP=”mysqldump” or completely omit the variable and directly use the command (without path).

Reply
Jonathan Bayer says:

February 21, 2012 at 6:16 pm

Couple of comments:

First, because all of the zabbix tables are InnoDb, you should use the following options to mysqldump:
–opt –single-transaction –skip-lock-tables
instead of just –opt

Second, you dump each table to a different file. This makes it a pain to restore an entire system. Why not have an option for a single file or not? The following code incorporates these two ideas:

SINGLEFILE=yes
if [ “$SINGLEFILE” = “yes” ]; then
DUMPFILE=”${DUMPDIR}/zbx-conf-bkup-${DATEBIN} +%Y%m%d%H%M.sql”
>$DUMPFILE
fi
# CONFTABLES
for table in ${CONFTABLES[*]}; do
[ “$SINGLEFILE” != “yes” ] && DUMPFILE=”${DUMPDIR}/${table}.sql”
echo “Backuping table ${table}”
${MYSQLDUMP} -R –opt –single-transaction –skip-lock-tables –extended-insert=FALSE \
-h ${DBHOST} -u ${DBUSER} -p${DBPASS} ${DBNAME} –tables ${table} >>${DUMPFILE}
[ “$SINGLEFILE” != “yes” ] && ${GZIP} -f ${DUMPFILE}
done

# DATATABLES
for table in ${DATATABLES[*]}; do
[ “$SINGLEFILE” != “yes” ] && DUMPFILE=”${DUMPDIR}/${table}.sql”
echo “Backuping schema table ${table}”
${MYSQLDUMP} -R –opt –single-transaction –skip-lock-tables –no-data \
-h ${DBHOST} -u ${DBUSER} -p${DBPASS} ${DBNAME} –tables ${table} >>${DUMPFILE}
[ “$SINGLEFILE” != “yes” ] && ${GZIP} -f ${DUMPFILE}
done
[ “$SINGLEFILE” = “yes” ] && ${GZIP} -f ${DUMPFILE}

Reply
Jens Berthold says:

March 16, 2012 at 12:05 pm

I created a new version for Zabbix 1.9.x / 2.0.0 including Jonathan Bayers suggestions:
https://github.com/maxhq/zabbixzone/blob/patch-1/zabbix-mysql-backupconf.sh

Reply
cm.labs says:

March 21, 2012 at 6:22 pm

Very nice Script.

Thanks

—
Anacondaweb.com

Reply
Marcel says:

May 16, 2012 at 5:54 pm

Hello,

I’m a new to this and want to backup the complete zabbix configuration, I think this script is doing this. but…. after running this script I have a file, how to restore this file into mysql?

Thanks

Reply
toanlv says:

May 25, 2012 at 5:32 am

Hi!
After running this script I have too many file (.sql.gz), how to restore all file into mysql?
Thanks!

Reply
Tobias says:

July 4, 2012 at 11:30 am

Hi there!

Your script worked perfectly, thanks for all the work and updates. But after the schema change that comes with the migration to Zabbix 2.0 some changes are required, some tables are gone, some were added.
Are you already working on this, can I help?

Thanks!

Tobias

Reply
Tobias says:

July 4, 2012 at 11:34 am

Ehh, nvmd… just read the comments properly – disregard my comments 😉

Reply
Daniel says:

August 6, 2012 at 5:04 am

Hy,

how is it possible to restore all this files again?

br
Daniel

Reply
- Ricardo Santos says:
  
  August 20, 2012 at 10:54 am
  
  Daniel,
  
  you can restore all tables, but it will only restore the configuration, not history data.
  
  Concatenate all files into one:
  zcat *.sql.gz >alltables.sql
  
  Restore it:
  mysql -u root -p zabbix
  
  Reply
MG says:

November 5, 2012 at 2:12 pm

Hi, Tested this out and everything appears to restore ok, but then get DB errors when trying to do anything – new host, delete, clone etc. Any ideas please??

Errors e.g. Error in query [DELETE FROM hosts WHERE (hostid IN (‘10131’)) ] [Lock wait timeout exceeded; try restarting transaction]
SQL statement execution has failed “DELETE FROM hosts WHERE (hostid IN (‘10131’)) “

Reply
- Ricardo Santos says:
  
  November 18, 2012 at 7:02 am
  
  There is some process locking this row in hosts table. Try to see ‘SHOW FULL PROCESSLIST’
  
  Reply

About Me

Ricardo Santos has over 10 years of experience as Linux Systems Administrator (RedHat and variants). Since 2009 uses Zabbix for Monitoring and Capacity. Recently became a Certified Zabbix Specialist by Zabbix SIA.

Backuping only the Zabbix Configuration

23 Responses to “ “Backuping only the Zabbix Configuration”

Leave a Reply

About Me

Categories

Related Links

Communities

Social Networks