Script for making quick back-ups of Zabbix

[frater]

Most of my servers are running Plesk and on one of them I have installed Zabbix. As we all know the Zabbix database can grow significantly and on my specific setup this can become a problem when Plesk is creating back-ups

I like the comfort of keeping the history of all the items, but if I would lose last year it would not be such a big deal for me (speaking for myself here).

I want Zabbix to detect problems before they become big problems and I don't want Zabbix to create problems...


On a few occasions it did when it was creating a database so big it was killing the performance of the server. Especially on mine which has an SSD for the system and a 2TB RAID 1 for all the data.

Only recently I had my first failed upgrade (to Zabbix 3.0) and there I was with a migrated database and the need to go back to 2.6
I had a huge back-up of Zabbix, but it took ages to import.
I finally broke it of and just head / tail on that huge SQL-file to exclude the history data and I had all significant data back in 15 minutes.
I wanted to make sure this would never happen again.

I decided to create 2 instances of MySQL on this Plesk server.
This means the Zabbix database doesn't get a back-up from Plesk anymore, which is what I want.
For the time being I placed this 2nd MySQL instance on the SSD, but it's quite easy to create symbolic links for trends_uint.ibd, trends.ibd, history_uint.ibd, history.ibd and events.ibd so that data can reside on the RAID.

I will then have the speed of SSD and enough room on the RAID1

If I regularly make a back-up of the Zabbix database into the primary MySQL-instance I will always have a back-up of my Zabbix, albeit without history.

For this I have created a script which I like to share.

I'm not giving any explanation how I created the 2nd MySQL instance.
It was a lot of trial and error, but I finally got there...
I could do it faster now, but I would still have a lot of work to write it down...

I have its data and socket in /var/lib/mysql2/

When I build zabbix from source I am always extracting it in /opt, so there is where the script is looking for schema.sql

The script only needs an amazing few seconds to run.....


# cat /usr/local/sbin/copydb2to1

#!/bin/sh

ZABBIXCONF=/etc/zabbix/zabbix_server.conf
ZABBIXBACKUP=/data/var/zabbixbackup
ZABBIXDISTRO=/opt


[ -f ${ZABBIXCONF}   ] || exit 1
[ -d ${ZABBIXBACKUP} ] || mkdir -p ${ZABBIXBACKUP}

if ! cd ${ZABBIXBACKUP} ; then
  exit 1
else

  if [ ! -e ${ZABBIXBACKUP}/schema.sql ] ; then
    SCHEMA="`find ${ZABBIXDISTRO} -name schema.sql | grep 'zabbix.*mysql' | xargs -I{} stat -c '%Y %n' {} | sort -rn | head -n1 | awk '{print $2}'`"
    if [ -n "${SCHEMA}" ] ; then
      MYSQLDIR="`dirname "${SCHEMA}"`"
      ln -s ${MYSQLDIR}/schema.sql
      ln -s ${MYSQLDIR}/data.sql
      ln -s ${MYSQLDIR}/images.sql
    fi

  fi
fi

[ -e schema.sql ] || exit 1

# Get the credentials and databasename from zabbix_server.conf
grep '^DB' ${ZABBIXCONF} >${ZABBIXBACKUP}/DBconf
. ${ZABBIXBACKUP}/DBconf

[ -z "${DBName}" ] && exit 1


echo "Extracting Zabbix database from 2nd MySQL-server and importing in the 1st MySQL-server without its history"

BACKUPFOLDER=${ZABBIXBACKUP}/`date +%Y.%m.%d..%H.%M`
SQLFILE=${BACKUPFOLDER}/${DBName}.sql.gz

LOGFILE="${BACKUPFOLDER}/`basename $0 | tr -cd 'A-Za-z0-9'`.log"

DATADIR1="/var/lib/mysql"
SOCKETFILE1="$DATADIR1/mysql.sock"
CREDENTIALS1="-uadmin -p`cat /etc/psa/.psa.shadow`"

DATADIR2="/var/lib/mysql2"
SOCKETFILE2="$DATADIR2/mysql.sock"
CREDENTIALS2="-uroot -ppassword"


IGNORETABLES="--ignore-table=${DBName}.acknowledges \
        --ignore-table=${DBName}.alerts \
        --ignore-table=${DBName}.auditlog \
        --ignore-table=${DBName}.auditlog_details \
        --ignore-table=${DBName}.escalations \
        --ignore-table=${DBName}.events \
        --ignore-table=${DBName}.history \
        --ignore-table=${DBName}.history_log \
        --ignore-table=${DBName}.history_str \
        --ignore-table=${DBName}.history_str_sync \
        --ignore-table=${DBName}.history_sync \
        --ignore-table=${DBName}.history_text \
        --ignore-table=${DBName}.history_uint \
        --ignore-table=${DBName}.history_uint_sync \
        --ignore-table=${DBName}.trends \
        --ignore-table=${DBName}.trends_uint "

mkdir -p ${BACKUPFOLDER}

echo -e "\nExport the Zabbix database from secondary MySQL without history to ${SQLFILE}\n" | tee -a ${LOGFILE}
mysqldump -S${SOCKETFILE2} ${IGNORETABLES} ${CREDENTIALS2} ${DBName} | gzip >${SQLFILE}

echo -e "\nCreate fresh database ${DBName} for user ${DBUser}@localhost on primary MySQL\n"  | tee -a ${LOGFILE}
echo "DROP database IF EXISTS ${DBName};"  >${ZABBIXBACKUP}/newDB
echo "CREATE database ${DBName} character set utf8 collate utf8_bin;"  >>${ZABBIXBACKUP}/newDB
echo "grant all privileges on ${DBName}.* to ${DBUser}@localhost identified by '${DBPassword}';"  >>${ZABBIXBACKUP}/newDB

mysql -S${SOCKETFILE1} ${CREDENTIALS1} < ${ZABBIXBACKUP}/newDB   2>&1 | tee -a ${LOGFILE}

echo -e "\n\nCreate fresh skeleton\n\n" | tee -a ${LOGFILE}
mysql -S${SOCKETFILE1} ${CREDENTIALS1} ${DBName}  < ${ZABBIXBACKUP}/schema.sql 2>&1 | tee -a ${LOGFILE}
mysql -S${SOCKETFILE1} ${CREDENTIALS1} ${DBName}  < ${ZABBIXBACKUP}/data.sql   2>&1 | tee -a ${LOGFILE}
mysql -S${SOCKETFILE1} ${CREDENTIALS1} ${DBName}  < ${ZABBIXBACKUP}/images.sql 2>&1 | tee -a ${LOGFILE}

echo -e "\n\nImport the Zabbix-file without history\n\n"  | tee -a ${LOGFILE}
gunzip < ${SQLFILE} | mysql -S${SOCKETFILE1} ${CREDENTIALS1} ${DBName}  2>&1 | tee -a ${LOGFILE}

[mushero]

Confused on your goal here - you can do a fast no-data backup by just excluding the history files and maybe a few things like events - haven't done it in a while but I think it was a few 10s of MB and a few minutes at most.

Can import it or push to S3 for safety, etc.

And you can later migrate/load your history files whenever you want - our last big migration was this way and took a week to load the history; no problem.

Steve