Backuping only the Zabbix Configuration

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



23 Responses to “ “Backuping only the Zabbix Configuration”

  1. 0siris says:

    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/

  2. 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).

  3. Oleksiy Zagorskyi says:

    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

  4. Vinício Zanchettin says:

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

  5. fjrial says:

    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

  6. Araceli Calabresi says:

    Hi Ricardo!

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

    Thanks for sharing.
    Araceli

  7. Jens Berthold says:

    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).

  8. 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}

  9. Jens Berthold says:

    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

  10. cm.labs says:

    Very nice Script.

    Thanks


    Anacondaweb.com

  11. Marcel says:

    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

  12. toanlv says:

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

  13. Tobias says:

    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

  14. Tobias says:

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

  15. Daniel says:

    Hy,

    how is it possible to restore all this files again?

    br
    Daniel

    • 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

  16. MG says:

    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’)) “

Leave a Reply

*