Backup Zabbix database itself

[tlacroix]

Hi everyone,

Out of curiosiry, how do you guys do backup the database of Zabbix itself ?

I am currently running the basic mysqldump command that works perfectly, however when the dump starts, i receive a hundred of mails as some tables may be locked (i guess)
Anyone already used hotcopy ? With which tool ?
I also think of a cluster database, where the slave would be backed-up ?

Interrested in your feedbacks!

Thanks!

[Ivo Palli]

I personally stop the server for the 2 minutes it takes to dump the database:

# Stop the server
killall zabbix_server

# Take the MySQL variables out of the conf and make them variables
eval $(grep "^ *DB" /usr/local/etc/zabbix_server.conf | sed -e 's/=/="/' -e 's/ *$/"/')

# Dump the database
/usr/bin/mysqldump -u$DBUser -p$DBPassword $DBName > zabbix_server.sql

# Start Zabbix server again
/usr/local/sbin/zabbix_server

[tlacroix]

Hi, and thank you for this feedback

I used to do it the same way before, however everytime i received a lot of alerts from zabbix, as hosts were not reachable (even with the zabbix_server stopped)
So I assumed stopping the server was not enough

[Singularity]

Some tables will be locked during db backup, query execution will becomes extremely slow when lots of data is to be fetched and items to be processed and data written to db by zabbix server. So yes, it is much better to stop zabbix service when creating a db backup.

[LenR]

I use xtrabkup https://www.percona.com/software/mys...ona-xtrabackup

[tlacroix]

Some tables will be locked during db backup, query execution will becomes extremely slow when lots of data is to be fetched and items to be processed and data written to db by zabbix server. So yes, it is much better to stop zabbix service when creating a db backup.

I already do!
However I still have thousand of mails
I'll add a timer before i do the mysqldump process, until i have time to test percona tool

Thanks !

[tim.mooney]

The xtrabkup tool that LenR pointed to is definitely worth a look, but you can probably improve your mysqldump performance too.

What options are you using with mysqldump? Since your Zabbix database is InnoDB (it is, correct?! It should be, if not you have other problems to fix first), you should be using "--skip-lock-tables" AND "--single-transaction", among other options. There are a bunch to consider, and they're positional, so the order you specify them matters. Be sure you read the caveats about "--opt" and positional parameters in the mysqldump man page.

[tlacroix]

Thank you for the info, tim !
Indeed with --skip-lock-tables" AND "--single-transaction i don't have any sort of alert that raise up
I'll have a look at --opt

I'll aslo have a deeper look at xtrabackup