Backup Zabbix database

[nicolasfo]

Hello,

I was looking for a way to backup all Zabbix database.
I found this howto based on Percona XtraBackup.

The script asks for a my.cnf file. This file doesn't exists on my system except in /etc/modutils/mysql/my.cnf. I don't think it is this file wich is used by MySQL (never used this file before).

I stopped MySQL server by myself (service MySQL stop) I'm unable to restart it, I must restart the server to make Zabbix works again.

I don't either have any /etc/mysql directory too...

So my question are :

• How MySQL server is installed during Zabbix server installation is installed ?
• Where is the file the script asking to work ?
• How MySQL server is started during server boot ?

Thanks

Nicolas

[kloczek]

Hello,

I was looking for a way to backup all Zabbix database.
I found this howto based on Percona XtraBackup.

The script asks for a my.cnf file. This file doesn't exists on my system except in /etc/modutils/mysql/my.cnf. I don't think it is this file wich is used by MySQL (never used this file before).

I stopped MySQL server by myself (service MySQL stop) I'm unable to restart it, I must restart the server to make Zabbix works again.

I don't either have any /etc/mysql directory too...

So my question are :

• How MySQL server is installed during Zabbix server installation is installed ?
• Where is the file the script asking to work ?
• How MySQL server is started during server boot ?

If you have you database on ZFS you feel free to adapt my of-site backup method which I'm using on zabbix DB slave:

# cat /etc/opt/backup_mysql_slave 
SNAPSHOT_DATE=$(date +%F)
mysql -e "SLAVE STOP;" 2>&1 > /dev/null
zfs snapshot -r rpool/VARSHARE/mysql@backup-$SNAPSHOT_DATE 2>&1 > /dev/null
mysql zabbix -e "SLAVE START;" 2>&1 > /dev/null
zfs send -r rpool/VARSHARE/mysql@backup-$SNAPSHOT_DATE | pixz -5 > /net/<some_nfs_server>/backup/mysql/zabbix-db-slave/mysql-$SNAPSHOT_DATE.xz
find /net/<some_nfs_server>/backup/mysql/zabbix-db-slave -atime 6 | xargs rm -rf
zfs destroy rpool/VARSHARE/mysql@backup-$SNAPSHOT_DATE

It takes only 1-2s to stop slave -> create snapshot -> start slave -> backup snapshot data generated by zfs send.

Any backup method using VFS layer has huge drawback: doing backup trashes all cached in memory data almost instantly hurting database performance.
On using zfs send even ZFS ARC data are polluted much less than by any other possible method. Effectively during above backup zfs misses/hits ratio stays almost the same.

PS. With full respect .. Percona XtraBackup it is a toy. It may work in case some small databases but this tools has its own (big) limitations.