Moving Zabbix Database

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 --user and --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: create database, create user, grant all privileges.

Username syntax for remote access (instead of or in addition to 'zabbix'@'localhost'): 'zabbix'@'10.10.10.0/255.255.255.0'

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

Edit /etc/zabbix/zabbix_server.conf: Set DBHost to the new database server address.

Restart Zabbix server: sudo systemctl restart zabbix-server

Edit /etc/zabbix/web/zabbix.conf.php: Set $DB['SERVER'] to the new database server address.

Restart Apache: 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.

9 Comments

Add a Comment
  1. Thanks for this! This one works for me when I migrate a version 5 zabbix appliance running in CentOS to version 6 package running in RHEL 8.

  2. I’m setting up a new Ubuntu server (22.04) on the cloud and was requested to have the latest Zabbix version (6.2) with PHP 8 and MariaDB; old Zabbix server is on prem with version 5.0.X and PHP 7.2.0 with an older MariaDB version, would this cause any issues, or should it work the same by following this guide? appreciate your insights, thank you!

    1. Hi, I don’t see any reason why it wouldn’t work the same.

  3. Hi, I have a 135GB DB size and need to migrate from MySQL EC2 to Aurora RDS. Is there a suggested way to do this? I’m trying with mysqldump, and split the dump (46GB compressed) into several parts to avoid any network error that would cancel the whole restoration.

    1. As long as Aurora RDS promises the same features and compatibility with a Zabbix-supported MySQL version, I would assume you are just fine using the usual tools. I don’t know what you mean with splitting the data. Since your data is already in AWS (in EC2), you can just try importing it after exporting with mysqldump.

  4. Thanks for thism I want to migrate my database from 4.2 version running on 10.1.40-mariadb in RHEL 7 environment to a Zabbix 6.2 running on 8.0-mysql in an Ubuntu 20.04. Will it work the same way as it did for you?.It would be very helpful to know it.

  5. I am currently working on moving physical zabbix server 4.2 version, that is running on Centos 7 to a Zabbix 6.2 , that is running on a Ubuntu 20.04 (focal). The 4.2 version is using mariadb and the 6.2 version is using mysql. will the procedure, still work

    1. My guess is: yes. Just try it out with a copy of the database, following the Zabbix documentation regarding the Zabbix upgrades (= you need to read all upgrade notes between 4.2 and 6.2).

  6. Thank you for this. Couldn’t figure out my problem until I saw this and the php change.

Leave a Reply