Migrating Zabbix DB to new server.

[surfrock66]

This isn't a question, but my experiences and pitfalls on migrating zabbix's DB to a new server. This was a small environment, with the server and DB on the same host, but performance is hurting so I want to split the DB off onto its own box. This was a bigger headache than it should have been, so I wanted to document my experiences. The DB restore took almost 24 hours, so my procedure to do that could have been done with more SQL grace by doing a restore then a differential replication, but I could handle a day-long outage as this isn't super critical.

1. I did a secure install of mysql on a new server, and had it ready to go. On Ubuntu, don't forget to bind it to 0.0.0.0 as it's 127.0.0.1 by default.
2. On the source server, I did a mysqldump of the ENTIRE database.
   1. mysqldump -u root -p --all-databases > zabbix.mysqldump.2025.07.16.sql
3. I then stopped the zabbix server and the mysql server, knowing there'd be a gap; maybe there's a better way but I didn't mind a gap. I disabled the mysql service.
4. I paused the cronjob for my partitioning script, the one from here https://blog.zabbix.com/partitioning...cedures/13531/
5. I copied the backup over and restored it; this took ~24 hours in my case
   1. mysql -u root -p < zabbix.mysqldump.2025.07.16.sql
6. Assuming your database user was 'zabbix'@'localhost' our database needs permissions changes, and what I found was the connection attempts to come from the fqdn of the zabbix server. Alter the credentials with the following:
   1. RENAME USER 'zabbix'@'localhost' TO 'zabbix'@'zabbix-server-hostname.subdomain.domain.tld';
7. This is a weird one. I got errors in my logs in the below format; turns out some stored procedures require the principle set at the time of install to be hard-coded, meaning you ALSO have to create a user with the same username at localhost. I thought you could just leave the original user and add the @'newfqdn' one, but it did not work until I did it this way.
   1. Error: [Z3005] query failed: [1449] The user specified as a definer ('zabbix'@'localhost') does not exist [update triggers set state=0,error='' where triggerid=31778;
   2. Resolution:
      1. CREATE USER 'zabbix'@'localhost' identified by '<samepassword>';
      2. grant all privileges on zabbix.* to 'zabbix'@'localhost';
      3. flush privileges;
8. Update your /etc/zabbix/zabbix_server.conf with the new DB connection.
9. Update your partitioning script; This is what mine looks like (You may want to manually run it as a test once updated):
   1. # initializing some variables
      my $db_schema;
      my $db_host = 'sql-hostname.subdomain.domain.tld';
      my $db_port = '3306';
      my $dsn = 'DBI:mysql:'.$db_schema.';host='.$db_host.';port=' .$db_port;
      my $db_user_name = 'zabbix';
      my $db_password = '<complexpassword>';
      my $curr_tz = 'America/Los_Angeles';
10. The zabbix web server has a different configuration for SQL than the actual monitoring server; open /etc/zabbix/web/zabbix.conf.php and update the database config, specifically the SERVER and PORT.
11. Re-enable the partitioning cronjob
12. Start the zabbix server.

Voila, you're moved!