Backing up?

[louis-m]

We are using Zabbix and it's a superb piece of engineering.
Only thing that concerns me is the backup. Our database is fairly hefty even though we try to only keep 30 days worth of stats.
I'm not 100% confident we could recover from a disaster and if this was to happen and we couldn't recover in a respectable period ie not from scratch etc, I think we would lose our ability to continue using Zabbix.
We do have NFS, SFTP etc with plenty of online storage to backup to.
We're not so bothered about the recovery of the stats etc but more of the config, templates, customisations etc.
So a bare metal with a new zabbix install and then a restore from a mysql dump would be acceptable as long as everything came back bar the stats etc
Does anybody have any tips on this? eg what to backup eg my.cnf, zabbix-server.conf, zabbix database minus ??? tables?
Any pointers using the latest Zabbix would be appreciated and go a long way to elevating our confidence should the worst occur.

[kloczek]

Zabbix uses SQL92 compliant database engines lie MySQL, PostgreSQL, Oracle DB, DB2, sqlite.
On making the backup of the zabbix database must be used normal those engines backup techniques. Ask your DB specialist(s) how to make your DB backup and how to restore it in case of any faults.

[louis-m]

Hi Kloczek,
I did see your reply on the other thread and thank you for your response. Unfortunately, we don't have any DB specialists. We're merely running Zabbix to check on various bits on our infrastructure and we have a basic understanding of Linux etc
What we're after is the confidence that we can do a restore from a backup that we've taken should the worst happen. We're not too bothered about the downtime eg we can do a bare metal and we keep the my.cnf, zabbix-server.conf and have a mysqldump of the zabbix database that was taken a month ago. Would that be enough to restore? Ids there anything else that would need backed up?
I suppose the thing to do is to try the restore on a test system to see what we get.

[kloczek]

Env without monitoring is exposed to not handling issues which could be detected by monitoring so minimise downtime is quite important. The scale of the env is not relevant.
The issue is that with the simpler backup techniques backup window is longer.
Zabbix data have a tendency to only grow (because even in const size env sometimes we are adding more things to monitor as the cost of such modifications is very small.
IMO cheapest technique of making backups is use slave DB.
How can it be used?
1) the slave DB is the natural bit which protects DB in disaster scenarios.
In case of crash scenario, all that needs to be done is promote the slave as new master with move VIP of the master to slave DB host or with change DB backend addresses in server and web frontend, or with changes in the middle like the proxy or load balancer.
2) it provides the predictable and non-invasive platform for any DB operations maintenance. Time to time is good to call OPTIMISE query across all tables. If someone started from the scale where history/trends/events tables are not partitioned such partitioning could be done without stopping master DB on slave then promote the partitioned slave as the new master and recreate slave on the previous master.
3) slave can be used to make the full off-site backups by simple locking for write master DB -> flush buffers -> check slave does it have the same binary counter position as master -> stop syncing data from the master -> unlock master -> do the backup on slave -> stat syncing again data from master on slave. This could be done even without snapshot capable storage.

In above scenario skills and knowledge, the bar is relatively low because:
1) of all what is really is necessary to learn is DB operations related to use master-slave setup
2) typical conservation operations like tables optimisations automatically forces to be fluent on jugglery between master and slave
3) slave can be used as well as the perfect platform to test zabbix upgrades between those versions where DB scheme is changing. All that is necessary to do is detach slave start the new upgraded server process on some system with attaching slave DB to this upgraded zabbix server. If nothing will fail this gives confidence that the same process on the master will not fail by for example some inconsistencies in DB. In such scenario, It is possible as well say very precisely how long upgrade will take. If any obstacles/error will appear it is possible to hold the whole process until DB issue on the master will be not solved first.
4) slave could be used as well to fire any heavy selects which someone wants to use to collect some additional data by obtaining them straight from DB content. All without performance impact on master DB used by prod zabbix server.
5) in case any architecture, hardware, OS layers upgrades slave is as well perfect platform to securely and easily introducing those changes without instant impact on the master. Any of those changes can be first tested and proven that they are OK then after promoting changed/upgraded slave as the new master the same changes/upgrades can be repeated on second DB node.
6) slave provides the perfect platform to roll back changes as well≤. Even if some upgrades/changes have been proven that they are OK by using the first slave usually it is not possible to make full prod tests on such changed/upgraded slave because usually in such scenarios over upgraded slave will be no full flow of the prod data/queries. In other words, by this, it is some possibility that even tested upgrade/change on slave may fail on switching as the new master. If it will happen all that is needed is to roll back is stop server process -> switch back to prev master DB -> start server process -> resync data from zabbix proxies.
7) promoting the current slave as new master can be automated in form some playbook used by orchestration software like console or other. Such playbook can even take care of automatic recreate new slave on the system where it was the prev master. With such wrappings using master->smave setup can be done by way less skilled Ops guys.

In other words, there are many not obvious benefits of use master->slave DB setup and above are not all. Only one of them will be securing zabbix DB data by creating off-site backup.