Export/import the configuration/data from Zabbix server to another (6.4 -> 7)

[Samarch]

Hello,

We’d like to export/import the configuration/date from Zabbix server to another.
Instead of upgrading the old server we installed a new server, and we’d like to transfer the whole config/data from the old server to the new one.

The old server is Zabbix 6.4
The new one is 7

Both are running on Ubuntu

What is the right way to do this?

Thanks

[Markku]

Take a dump of the database and import that in the new database server.

Example of dump (with MariaDB or MySQL):

mysqldump -u zabbix -p --extended-insert --single-transaction zabbix | gzip > /mnt/zabbix-dump.gz

The new database and the new zabbix user with permissions must be first created (but don't import any Zabbix SQL schema!).

Example of import on the new server:

zcat zabbix-dump.gz | mysql -u zabbix -p zabbix

This process can take a long time. Be sure to have the new DB server optimized to not slow things down. Some hints:

innodb_buffer_pool_size: https://mariadb.com/kb/en/innodb-sys...ffer_pool_size (this is important!)
innodb_io_capacity: https://mariadb.com/kb/en/innodb-sys...db_io_capacity
Keep binary logging disabled (default) unless you have database replication: https://mariadb.com/kb/en/replicatio...ables/#log_bin

After the database has been imported to the new server, you can then point your Zabbix 7 server to that new database and it will upgrade it automatically from 6.4 to 7.0.

Markku

[Markku]

Take a dump of the database and import that in the new database server.

Example of dump (with MariaDB or MySQL):

mysqldump -u zabbix -p --extended-insert --single-transaction zabbix | gzip > /mnt/zabbix-dump.gz

The new database and the new zabbix user with permissions must be first created (but don't import any Zabbix SQL schema!).

Example of import on the new server:

zcat zabbix-dump.gz | mysql -u zabbix -p zabbix

This process can take a long time. Be sure to have the new DB server optimized to not slow things down. Some hints:

innodb_buffer_pool_size: https://mariadb.com/kb/en/innodb-sys...ffer_pool_size (this is important!)
innodb_io_capacity: https://mariadb.com/kb/en/innodb-sys...db_io_capacity
Keep binary logging disabled (default) unless you have database replication: https://mariadb.com/kb/en/replicatio...ables/#log_bin

After the database has been imported to the new server, you can then point your Zabbix 7 server to that new database and it will upgrade it automatically from 6.4 to 7.0.

Markku

[Samarch]

Hello

Thanks

[Samarch]

Hello

So, I stopped the zabbix service and I ran this command
mysql -u zabbixusr -p zabbixdb < zabbix6.4.sql

And I get this error
ERROR 1419 (HY000) at line 1252: You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

I edit /etc/mysql/my.cnf and added
[mysqld]
log-bin-trust-function-creators = 1

+ systemctl reload apache2

And I still have the same error
ERROR 1419 (HY000) at line 1252: You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

Any idea ?

[Markku]

Restarting Apache does not affect database configurations, you need to restart the database service.

Or remove that from the configuration file and do

sudo mysql
mysql> set global log_bin_trust_function_creators = 1;
mysql> quit;

and put it back to 0 after the import.

Markku

[Samarch]

OK,

Now a little bit further : )

It succeeded to import the DB
But it fails to upgrade the DB

I have this message on the web interface

Database error

• The Zabbix database version does not match current requirements. Your database version: 6030083. Required version: 7000000. Please contact your system administrator.

And in the zabbix_server.log

2131:20240722:100241.938 Zabbix Server stopped. Zabbix 7.0.0 (revision 49955f1fb5c).
2147:20240722:100251.976 Starting Zabbix Server. Zabbix 7.0.0 (revision 49955f1fb5c).
2147:20240722:100251.976 ****** Enabled features ******
2147:20240722:100251.976 SNMP monitoring: YES
2147:20240722:100251.976 IPMI monitoring: YES
2147:20240722:100251.976 Web monitoring: YES
2147:20240722:100251.976 VMware monitoring: YES
2147:20240722:100251.976 SMTP authentication: YES
2147:20240722:100251.976 ODBC: YES
2147:20240722:100251.976 SSH support: YES
2147:20240722:100251.976 IPv6 support: YES
2147:20240722:100251.976 TLS support: YES
2147:20240722:100251.976 ******************************
2147:20240722:100251.976 using configuration file: /etc/zabbix/zabbix_server.conf
2147:20240722:100251.994 current database version (mandatory/optional): 06030083/06030083
2147:20240722:100251.994 required mandatory version: 07000000
2147:20240722:100251.994 mandatory patches were found
2147:20240722:100251.995 starting automatic database upgrade
2147:20240722:100251.996 [Z3005] query failed: [1050] Table 'scim_group' already exists [create table scim_group (
`scim_groupid` bigint unsigned not null,
`name` varchar(64) default '' not null,
primary key (scim_groupid)
) engine=innodb]
2147:20240722:100251.996 database upgrade failed on patch 06030084, exiting in 10 seconds

Thanks
Samarch

[Markku]

Ok, so you had actually "6.3" installed that is a release candidate version of 6.4, maybe that messes up with the database upgrades.

I see two options:
1. Install first 6.4.17 and see if it is able to upgrade the database successfully, then upgrade to 7.0
2. Start "fixing" the database differences according to the errors you get. This takes some effort and patience.

Markku

[Samarch]

So,

It's weird, I thought I saw 6.4 written somewhere, anyway
we don't have so much clients,
we'll insert them by hand, it ll be easier.

Thanks a lot

[sysadm101]

Samarch whats the Ubuntu OS version you are running?

Any idea about Zabbix-server-pgsql package not supported for Ubuntu 20.04?

Thanks

[Markku]

Any idea about Zabbix-server-pgsql package not supported for Ubuntu 20.04?

Sure, maybe because Zabbix 7.0 requires newer libraries than are available with Ubuntu 20.04.

Markku