Upgrade Zabbix 7.0.5 to 7.4 - Database Backup

[jburysom]

Hello,

Prior to upgrading to Zabbix 7.4 it is recommended to backup the database. The documentation does not provide the steps to backup the database. I'm using MySQL v8.0.37. I'm not a DB admin, how do I accomplish this?

- Is this as simple as performing one of the following?
- Also, do all application processes need to be shutdown prior to performing the database backup?

mysqldump -u zabbix -p --databases zabbix > zabbix_dump_file.sql
mysqldump -u zabbix -p --all-databases > zabbix_dump_file.sql

Thanks in advance for any feedback!

=-=-=-=-=-=-=-=-=-=
2 Back up database
Back up your existing Zabbix database to safeguard against upgrade failures (for example, disk space issues, power loss, or unexpected problems).

[cyber]

It would be hard to get consistent backup, if an app is still writing to DB.. So yes, shut it down beforehand, you have to do it anyway, if you upgrade. And yes. doing a backup is as you say, it shoudl be quite enough to do backup of Zabbix DB and leave others out.. As you say, you are not a DBA, I would suggest to play around in dev/test env first and learn how to dump and restore from that dump...

[tim.mooney]

Either of those are OK for a backup, but if you're not familiar with MySQL, then the first one is a little safer if you end up having to do a restore. The second one backs up all databases, including the internal "mysql" database, and writes everything to the same file. If you needed to do a restore from that file, it would restore both the 'zabbix' database and the 'mysql' database. If you're restoring to the exact same system, that's fine, but the 'mysql' backup should not be considered "portable" between different versions of MySQL.

mysqldump automatically includes the "--opt" flag, which is a bundle of a few other useful options. Check the man page for more info.

For backing up the 'zabbix' database specifically, I recommend you include a couple other options, beyond the ones you show above:

--skip-lock-tables --flush-logs --single-transaction

'--skip-lock-tables' followed by '--single-transaction' go together, and they rely on the fact that all the tables in your zabbix database are supposed to be InnoDB (or one of its aliases). The Zabbix database creation document requires that you use InnoDB, See the mysqldump man page and read the description of '--lock-tables' (you're disabling that default option by specifying '--skip-lock-tables') and '--single-transaction'. For a database that's all InnoDB, those should always be used.

'--flush-logs' is just a nicety. It's not required, but it's useful to include.

As far as application processes, zabbix-server and the web front end should both be shut down. The database should be running (mysqldump won't work if it's not), but you don't want zabbix actively writing new data to it while you're doing the backup. You don't need to shut down anything else on the system, you just want the processes that access the database (especially zabbix-server) to be offline.

You can do database backups with everything running -- at least nightly backups of your database are a good idea, and you don't need to take zabbix offline for that. You would still be able to restore from the nightly backups, you just wouldn't have any of the data that zabbix wrote to the database after the backup transaction (see '--single-transaction') started. For a backup that's meant to be a fall-back position in case of a failed upgrade, it's just a little safer to have the database quiescent. You have to shut zabbix-server down to do the upgrade anyway, so just do the backup after you've done the shutdown.