While we can dump a whole database in a single file, it isn't always the best solution. There might be cases when restoring only the configuration data would be useful:
- The first is when testing a Zabbix upgrade on a less powerful system than the Zabbix server.
- When attempting to recover from a disastrous event, it would be useful to restore configuration only and resume monitoring as quickly as possible. If needed, history and trend data can be restored later in small portions to avoid overloading the database.
Usually, data tables, such as the ones holding history, trend, and event information, will be much bigger than the configuration tables. Restoring the data tables would take much longer or even be impossible on a test system. We could split all of the tables into configuration and data ones, but it's likely even more simple to back each table up separately and deal with the desired tables when restoring. An example command to do so is as follows:
$ for table in $(mysql -N -e "show tables;" zabbix); do mysqldump --add-locks --extended-insert --single-transaction --quick zabbix $table | bzip2 > zabbix_database_backup_$table.bz2; done
Note that, in this case, we're not performing the backup for the whole database in a single transaction, and changes to the configuration could lead to inconsistencies across the tables. It's a good idea to schedule such a backup at a time when configuration changes would be unlikely.
If the consistency of the configuration tables is a likely problem, we could instead back up the configuration tables in a single transaction and the tables that hold collected and recorded information separately:
$ mysqldump --add-locks --extended-insert --single-transaction zabbix --ignore-table=zabbix.history --ignore-table=zabbix.history_uint --ignore-table=zabbix.history_text --ignore-table=zabbix.history_str --ignore-table=zabbix.history_log --ignore-table=zabbix.trends --ignore-table=zabbix.trends_uint --ignore-table=zabbix.events --ignore-table=zabbix.alerts --ignore-table=zabbix.auditlog --ignore-table=zabbix.auditlog_details --ignore-table=zabbix.acknowledges | bzip2 > zabbix_database_backup_config_tables.bz2 $ mysqldump --add-locks --extended-insert --single-transaction zabbix history history_uint history_text history_str history_log trends trends_uint events alerts auditlog auditlog_details acknowledges | bzip2 > zabbix_database_backup_data_tables.bz2
Note that the configuration and data table distinction is a bit fuzzy in Zabbix, and several configuration tables still hold runtime information.