How to merge DB backup with current DB

[automiketic]

I am running Zabbix 6.2 (will be updating to 6.4 in a couple of weeks), and accidentally purged several items from about 50 hosts yesterday. I went to replace a template and didn't realize using the replace option would remove all templates and had the clear history option selected. I did make a db backup about 2 weeks ago and I'm hoping I can merge that into the current db to at least get back all the history on the items minus the time between that backup and today. I am using MariaDB, what is the best way to accomplish this?

[tim.mooney]

I've never tried to do this and I don't know that I would attempt it unless I were desperate, but here's some general suggestions.

Assuming you did the MariaDB backup using 'mysqldump', you'll need the parts of the SQL dump file that are related to the history* tables. There are 5 history-related tables, with some of the tables being specific to items that collect data of a specific type. For example, if none of the items you accidentally purged were of "Type of information": "log" then you wouldn't need to restore anything from the history_log table. If your accidentally-purged items where all some type of numeric data, then you'll likely only need to worry about restoring data from the "history" and "history_uint" tables.

Once you've identified which tables would have contained the history info you want back, you want to spend a little time getting familiar with the layout of those tables, so that you know how to identify what parts of the dump you need to reload.

Here's an example from my (5.0.32) environment. Yours might be slightly different since you're on a much later version, but it will be close. In this case, assume I need to bring back some data into the history_uint table. To get familiar with that table, I did:

MariaDB [zabbix]> describe history_uint;
+--------+---------------------+------+-----+---------+-------+
| Field  | Type                | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| itemid | bigint(20) unsigned | NO   | MUL | NULL    |       |
| clock  | int(11)             | NO   |     | 0       |       |
| value  | bigint(20) unsigned | NO   |     | 0       |       |
| ns     | int(11)             | NO   |     | 0       |       |
+--------+---------------------+------+-----+---------+-------+
4 rows in set (0.028 sec)

MariaDB [zabbix]> select * from history_uint order by clock limit 20;
+--------+------------+-------------+-----------+
| itemid | clock      | value       | ns        |
+--------+------------+-------------+-----------+
|  26783 | 1675550543 | 15760834560 | 151996713 |
|  26784 | 1675550544 | 35318984704 | 176579161 |
|  26785 | 1675550545 |           1 | 200046322 |
|  26787 | 1675550547 |         598 | 294435184 |
|  27509 | 1675550549 |         107 | 347308153 |
|  27510 | 1675550550 |           0 | 365925812 |
|  27511 | 1675550551 | 17034174464 | 376424413 |
|  27512 | 1675550552 |           1 | 402770004 |
|  26783 | 1675550603 | 15745511424 | 275336585 |
|  26784 | 1675550604 | 35307868160 | 280371148 |
|  26785 | 1675550605 |           1 | 288389132 |
|  26787 | 1675550607 |         596 | 380228228 |
|  27509 | 1675550609 |         107 | 341490471 |
|  27510 | 1675550610 |           0 | 340655051 |
|  27511 | 1675550611 | 17034174464 | 346164050 |
|  27512 | 1675550612 |           1 | 361229954 |
|  26783 | 1675550663 | 15756070912 | 394951563 |
|  26784 | 1675550664 | 35316883456 | 408012345 |
|  26785 | 1675550665 |           1 | 422225098 |
|  26787 | 1675550667 |         597 | 529912230 |
+--------+------------+-------------+-----------+
20 rows in set (2.506 sec)

The 'clock' values are UNIX epoch seconds for when the reading was taken, so you can use perl or awk or GNU date to convert that back to a specific time. The 'ns' is (I think, I would have to confirm to be sure) an additional nanoseconds, to further differentiate the time beyond second-level granularity for the "clock" reading.

Knowing that, you need to figure out the date and time range that you want to restore, convert that to UNIX epoch seconds, and use that value to restrict what records get re-imported based upon their "clock" value.

For example, you said you accidentally purged the values on May 2nd, so you want to find the "clock" value for that, something like:

$ date --date='2023-05-02 16:00:00 UTC' +%s
1683043200

You can use your local time zone, adjust the time of the day, etc, to get a more specific value for 'clock', but the general idea is that you want records with a value in the clock column that are lower than 1683043200.

You could use that as your only determining factor for identifying records in the dump to restore, but you can further reduce what gets re-imported. The 'itemid' column maps the data to a particular item, but to know which itemids you want back, you're going to have to spend some time with the 'items' table. You can 'describe items;' to get a list of all the columns, but you're mainly going to need to use the 'name' column to identify all the possible itemids that were deleted. If you apply an item named '% used on C:' to 50 hosts, you'll have at least 50 different itemids in your items table. You can use those itemids to further select which records from your mysqldump you need to include in the restore.

From there, it's a matter of using some tool you're comfortable with to filter out as many of the records that don't match your time criteria and/or itemid criteria from the portion of the mysqldump file. You're creating a new SQL file that contains only the INSERT statements for the clock times and itemids that you want to re-import.

Once you have that new file, the hard part is done. You just source the SQL file, and the INSERT statements will re-recreate the missing records.

mysql -u zabbix --database=zabbix -p  --execute 'source /path/to/your/filtered-file.sql;'

Hopefully that helps!

If there are parts I glossed over that are not clear to you, follow-up and ask.