Zabbix MySQL database migration issues

[justIT]

Hello,
I have been working on migrating an old Zabbix 5.4 server (hosted on Ubuntu 20.04) to the Zabbix 7.0 LTS appliance image. The new server was easy to setup and I was able to transfer data from my /etc/zabbix and /etc/php. I first ran bzip2 -dk "/var/lib/mysql/zabbix.bz2" | mysql -u root zabbix -p
and got the old DB imported into the appliance.

I don't have the right DB version since the front end gives me an error confirming the output of this command: SELECT * FROM dbversion;
+-------------+-----------+----------+
| dbversionid | mandatory | optional |
+-------------+-----------+----------+
| 1 | 6010048 | 6010048 |
+-------------+-----------+----------+


I did not see any action items in this document so I went onto Primary Key migration steps: https://www.zabbix.com/documentation...rade_notes_700

The output from this Primary key migration command ran successfully:
//https://www.zabbix.com/documentation/current/en/manual/appendix/install/db_primary_keys#post-migration
mysql -uroot -p zabbix < /usr/share/zabbix-sql-scripts/mysql/option-patches/history_upgrade_prepare.sql

The result from these csv export and import command were 0 rows affected:
LOAD DATA INFILE '/var/lib/mysql/migrate/history_log.csv' IGNORE INTO TABLE history_log FIELDS TERMINATED BY ',' ESCAPED BY '"' LINES TERMINATED BY '\n';
Query OK, 0 rows affected (0.00 sec)
Records: 0 Deleted: 0 Skipped: 0 Warnings: 0


Thanks for any help!

[cyber]

This does not feel right... First you copied datafiles from one version of DB to another? I really hope it works OK without running any upgrade db commands... (https://dev.mysql.com/doc/refman/8.4/en/upgrading.html)
DB version starting with 601... These patches should be for v6.2 ?? You said you have 5.4...
0 rows affected? Probably did not do anything then... You have to verify, did preparing script actually create and rename any tables... And was there anything in files after output (SELECT * INTO OUTFILE... ) etc ...

[justIT]

Sorry I left out a few steps from what I tried in my upgrade efforts, I should have just copied the history command. First, I cloned a snapshot of my production 5.4 server (running ubuntu 20.04) and attempt to upgrade it to the latest zabbix 7 via these commands

179 rm -Rf /etc/apt/sources.list.d/zabbix.list
180 wget https://repo.zabbix.com/zabbix/7.0/d...bian12_all.deb
181 wget https://repo.zabbix.com/zabbix/7.0/u...u24.04_all.deb
182 apt update
183 rm zabbix-release_latest+debian12_all.deb
184 apt install --only-upgrade zabbix-server-mysql zabbix-frontend-php zabbix-agent
185 apt install zabbix-apache-conf
186 systemctl start zabbix-server


I then imported the DB into a fresh zabbix appliance image (a third VM). Would you suggest I re-clone production 5.4, upgrade zabbix-mysql via "apt install --only-upgrade zabbix-server-mysql", and then import the DB into the appliance?

[cyber]

Appliance comes with already preinstalled DB, I don't think you should just overwrite DB files with something from other version.... Only in case you are very familiar with Mysql upgrades and know what you are doing... I have no experience with that, so I cannot really comment...

[tim.mooney]

I agree with what cyber has said.

I could be wrong, but my understanding is that the "appliance" is not meant like some other VM appliances that you get from vendors like VMWare or Cisco. I believe the Zabbix VM is more a "learning environment" or easy place to experiment with Zabbix. I don't think it's sized or designed to be a production server for someone's enterprise. You can change the CPU and RAM parameters for the VM and grow the filesystem where the database sits, but it's still not tuned for widespread use.

Regarding moving a MySQL database: unless you know that both the source host and the destination host are using very similar versions of MySQL, I wouldn't assume moving the binary files is a good idea. There is a mysql_upgrade command (and a corresponding mariadb-upgrade for MariaDB) that will adjust the format for a database when moving between versions of MySQL (or MariaDB), but I've only ever used it for step-wise upgrades. I've never used it if there's a big version jump. In those cases, it's almost always safer to do a SQL export using mysqldump and then restore the database from the SQL text. I can't tell from your description if that's what you're doing to move the database or if you're trying to move the binary files.

[justIT]

Thanks for the advice, I was planning to make the appliance our production device because I thought it would get better support than apt installing zabbix server on top of the latest Ubuntu. So now it is the question of what how I follow the zabbix upgrade procedure from 5.4 to 7.0. I tried this before, but the result wan an out of date DB for the new 7.0 server.

[cyber]

Export data from old instance (I think you can do it also with all the "create commands"), set up new instance with new versions etc, but do not create new DB schema. import OLD schema with al the data. At this point you have new server and old DB... Now, when you start up your NEW server first time, it should do all the upgrade procedures... Also primary keys should be added after upgrade, there are instructions for that in docs... https://www.zabbix.com/documentation...b_primary_keys

[justIT]

Thanks! I hope to get to the database export and upgrade next week.

[justIT]

As I wanted to re-import the dump file into another Zabbix VM, I was stumped stumped by permissions. Make sure you chmod the /var/lib/mysql/zabbix directory before importing the DB. Don't make this noob mistake like me.

[tim.mooney]

As I wanted to re-import the dump file into another Zabbix VM, I was stumped stumped by permissions. Make sure you chmod the /var/lib/mysql/zabbix directory before importing the DB. Don't make this noob mistake like me.

Hang on, you shouldn't need to (and actually shouldn't ever) chmod the permissions on any of the stuff under /var/lib/mysql. Treat /var/lib/mysql like a black box, or maybe something that's very expensive: you can look, but don't touch.

If your MySQL/MariaDB/Percona database is correctly configured, then you've set the config option

innodb_file_per_table = 1

That option is specified in the Zabbix database documentation when you're setting up a new MySQL/MariaDB/Percona instance.

After file-per-table is set, when you create a database, like 'zabbix', it creates a directory under the mysql 'datadir'

MariaDB [(none)]> SHOW VARIABLES like '%datadir%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+

Note that if you create a database like zabbix before you set file-per-table, you have a problem, and you should start over. Instead of separate table and index files in a 'zabbix' directory, everything is probably going to end up in one giant file. It's no bueno.

So your /var/lib/mysql/zabbix/ directory is part of the structure of your database. Everything you CREATE when connected to the 'zabbix' database, whether it's part of a blank database setup to create the TABLES and VIEWs, or whether you're importing a msyqldump from an earlier version of Zabbix, should be created under that directory.

You don't want to modify permissions on the directory container, and under no situation that I've ever encountered would you need to modify those permissions.

If you're importing a mysqldump from an older version, you should be doing that while you're connected to the database as the 'zabbix' user. Assuming you followed the Zabbix install instructions, an early step in the setup created the 'zabbix' user and GRANTed the necessary permissions do stuff in the zabbix database, which means the /var/lib/mysql/zabbix/ directory. Note that you could also import the mysqldump as the MySQL 'root' user, but if you do that you're going to end up with incorrect ownership or permissions on the tables and structures. Do the import as the 'zabbix' MySQL user, and assuming you set up that MySQL user with the correct permissions at the start, then everything will be fine.

[justIT]

Thanks for the advice. It was imported as the zabbix user. I used chown mysql on the /var/lib/mysql/zabbix directory and then ran chmod 750 on it. The /var/lib/mysql/zabbix directory is an array of .idb file, and the permissions are -rw-r----- (640).