In the series “why wouldn’t I put this out public instead of just saving it to my own notes”: Moving the Zabbix database to a new database server.
- Old server: Debian Linux 9, running MariaDB 10.1.48
- New server: Debian Linux 11, running MariaDB 10.5.12 (from Debian packaging)
- Zabbix (5.0.19 LTS) frontend is running on Apache
Step 1: Dumping the current database
# mysqldump --single-transaction zabbix | gzip > /mnt/zbx-dump.sql.gz
No need to stop Zabbix for this. Add
--host and similar options if not running locally as root.
Copy the (huge) file to the new database server.
Step 2: Preparing the new database
Use the usual mysql commands to create the new database and correct user and permissions on the new server:
grant all privileges.
Username syntax for remote access (instead of or in addition to
Use the password you are using in Zabbix server configurations.
Do not import any SQL schema at this point, we want an empty database to start with.
Step 3: Importing the database on the new server
$ zcat zbx-dump.sql.gz | mysql -u zabbix -p zabbix
This can take a lot of time. (Example case: a 50 GB database was imported in about two hours.)
Step 4: Reconfiguring Zabbix to use the new database
DBHost to the new database server address.
Restart Zabbix server:
sudo systemctl restart zabbix-server
$DB['SERVER'] to the new database server address.
sudo systemctl restart apache2
Note: If you didn’t use the old (existing) database user password in step 3, you need to change also the passwords in the configuration files in this step.
Step 5: Testing
See the Zabbix frontend (GUI) that everything looks normal and performant. See the Zabbix server log (
/var/log/zabbix/zabbix_server.log) for anything strange.
Note that there is no data in the new database between steps 1 and 4, this is expected, so you will see some gap in the graphs and event lists.
If you face unexpected problems, check the Zabbix server log or MariaDB log. If nothing else helps, roll back by reverting the changes made in the previous step. The existing data in the old database is still there.
If the troubleshooting takes a long time and you want to minimize the interval for the lost data in Zabbix, you may want to start from step 1 again. In that case remember to drop the new
zabbix database on the new server first (otherwise you will get error while creating the database in step 4).
Step 6: Set the database partitioning maintenance
If the database was partitioned (see for example this Zabbix blog post), the table partitioning is there but the system used for creating new partitions is not. Re-implement the system scripts on the new database server (cron job or similar), or, if the stored procedures are used, you can set them up again (see the Zabbix blog post), or copy from the old database:
# mysqldump --single-transaction --routines --no-data --no-create-info zabbix > /mnt/zabbix-partitioning.sql
Copy the file to the new server. Then import it on the new server:
# cat zabbix-partitioning.sql | mysql zabbix
Recreate the partitioning event, for example:
# mysql (Paste this) DELIMITER $$ CREATE EVENT `e_zbx_part_mgmt` ON SCHEDULE EVERY 1 DAY STARTS '2021-12-30 00:00:00' ON COMPLETION PRESERVE ENABLE COMMENT 'Creating and dropping partitions' DO BEGIN CALL zabbix.drop_partitions('zabbix'); CALL zabbix.create_next_partitions('zabbix'); END$$ DELIMITER ;
Step 7: Monitoring
Finally check that all the necessary monitoring is in place, like the generic MariaDB monitoring and the partition monitoring.