Zabbix. Upgrading the database to primary keys

Starting with Zabbix 6.0, primary keys are used for all tables in new installations, but since I upgraded Zabbix server to version 6.4, I had to manually update the tables after the upgrade.

Before updating, first of all, open the menu “Report” – “System Information” and make sure that the keys are not used, this will be indicated by the entry – “Database history tables use primary key – No”, if the keys are used, then this entry simply will not be.

The database I had was on mysql 8.0.32-0ubuntu0.22.04.2.

I installed screen and zabbix-sql-scripts as I need the history_pk_prepare.sql script:

apt install zabbix-sql-scripts screen

Stopped Zabbix server:

service zabbix-server stop

Let’s create a session called zabbix, for example, using screen, so that if during the execution of sql, the connection with the server breaks, the process will continue to run:

screen -S zabbix

Then if anything, you can connect to the session like this:

screen -r zabbix

Now let’s execute SQL queries from the history_pk_prepare.sql file:

mysql -uzabbix -p<password> zabbix < /usr/share/zabbix-sql-scripts/mysql/history_pk_prepare.sql

If anything, here is the contents of the history_pk_prepare.sql file, as we can see, the existing history tables are renamed and empty new ones are created:

RENAME TABLE history TO history_old;
CREATE TABLE `history` (
        `itemid` bigint unsigned NOT NULL,
        `clock` integer DEFAULT '0' NOT NULL,
        `value` DOUBLE PRECISION DEFAULT '0.0000' NOT NULL,
        `ns` integer DEFAULT '0' NOT NULL,
        PRIMARY KEY (itemid,clock,ns)
) ENGINE=InnoDB;

RENAME TABLE history_uint TO history_uint_old;
CREATE TABLE `history_uint` (
        `itemid` bigint unsigned NOT NULL,
        `clock` integer DEFAULT '0' NOT NULL,
        `value` bigint unsigned DEFAULT '0' NOT NULL,
        `ns` integer DEFAULT '0' NOT NULL,
        PRIMARY KEY (itemid,clock,ns)
) ENGINE=InnoDB;

RENAME TABLE history_str TO history_str_old;
CREATE TABLE `history_str` (
        `itemid` bigint unsigned NOT NULL,
        `clock` integer DEFAULT '0' NOT NULL,
        `value` varchar(255) DEFAULT '' NOT NULL,
        `ns` integer DEFAULT '0' NOT NULL,
        PRIMARY KEY (itemid,clock,ns)
) ENGINE=InnoDB;

RENAME TABLE history_log TO history_log_old;
CREATE TABLE `history_log` (
        `itemid` bigint unsigned NOT NULL,
        `clock` integer DEFAULT '0' NOT NULL,
        `timestamp` integer DEFAULT '0' NOT NULL,
        `source` varchar(64) DEFAULT '' NOT NULL,
        `severity` integer DEFAULT '0' NOT NULL,
        `value` text NOT NULL,
        `logeventid` integer DEFAULT '0' NOT NULL,
        `ns` integer DEFAULT '0' NOT NULL,
        PRIMARY KEY (itemid,clock,ns)
) ENGINE=InnoDB;

RENAME TABLE history_text TO history_text_old;
CREATE TABLE `history_text` (
        `itemid` bigint unsigned NOT NULL,
        `clock` integer DEFAULT '0' NOT NULL,
        `value` text NOT NULL,
        `ns` integer DEFAULT '0' NOT NULL,
        PRIMARY KEY (itemid,clock,ns)
) ENGINE=InnoDB;

Now let’s connect to the mysql server and select the zabbix database:

mysql
use zabbix;

Let’s remove the max_execution_time limit so that the execution of the sql query does not stop and copy the data from the old tables to the new ones:

SHOW VARIABLES LIKE 'MAX_EXECUTION_TIME';
SET @@max_execution_time=0;
INSERT IGNORE INTO history SELECT * FROM history_old;
INSERT IGNORE INTO history_uint SELECT * FROM history_uint_old;
INSERT IGNORE INTO history_str SELECT * FROM history_str_old;
INSERT IGNORE INTO history_log SELECT * FROM history_log_old;
INSERT IGNORE INTO history_text SELECT * FROM history_text_old;

If anything, here is an example of how long it took me to execute these sql queries on a server with a mirrored SSD raid and an Intel® Xeon® E3-1230 processor (as we can see, the history table is the longest – one and a half minutes, in which there are almost 8 million records):

INSERT IGNORE INTO history SELECT * FROM history_old;
Query OK, 7856643 rows affected (1 min 31.30 sec)
Records: 7856643  Duplicates: 0  Warnings: 0

 INSERT IGNORE INTO history_uint SELECT * FROM history_uint_old;
Query OK, 2617069 rows affected (34.44 sec)
Records: 2617069  Duplicates: 0  Warnings: 0

INSERT IGNORE INTO history_str SELECT * FROM history_str_old;
Query OK, 12282 rows affected (1.08 sec)
Records: 12282  Duplicates: 0  Warnings: 0

 INSERT IGNORE INTO history_log SELECT * FROM history_log_old;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

 INSERT IGNORE INTO history_text SELECT * FROM history_text_old;
Query OK, 605 rows affected (0.94 sec)
Records: 605  Duplicates: 0  Warnings: 0

Now you can start Zabbix server:

service zabbix-server start

At the end of the transfer of data to new tables, delete the old ones:

DROP TABLE history_old;
DROP TABLE history_uint_old;
DROP TABLE history_str_old;
DROP TABLE history_log_old;
DROP TABLE history_text_old;

See my other articles about Zabbix

Zabbix. Upgrading the database to primary keys

Like this:

Похожее

Join the Conversation

Leave a ReplyCancel reply