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

Ensure that the new database server has been properly configured. For example:

• innodb_buffer_pool_size (this is important for performance!)
• bind_address
• event_scheduler (if using partitioning and database events for it)

Use the usual MySQL/MariaDB commands from Zabbix documentation to create the new database and correct user and permissions on the new server. 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 Zabbix-provided 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.