Upgrade rather large db (+200G) from 5.4 to 6.0

[lbm]

I want to upgrade from 5.4 to 6.0, which also includes some major db changes.

My history tables, are rather large so doing the post-upgrade work, takes a very long time.
Im especially talking about the 2 changes here:

Dumping an importing, especially the txt history table, will take ~ 1 day, maybe somewhat more, maybe somewhat less.

https://www.zabbix.com/documentation/current/en/manual/appendix/install/db_primary_keys#mysql

Fixing the charset+collation, ~ 400+ columns, seems to stall around ~ 170-200 colums left after ~ 30 minutes.

https://www.zabbix.com/documentation/current/en/manual/appendix/install/db_charset_coll

So, what is the best upgrade approach here without having zabbix down for ~24hours ish ? I could ofc, whipe the historical data, but its not very optimal.

 SELECT table_name AS "Table", ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)" FROM information_schema.TABLES WHERE table_schema = "zabbixdb" and table_name like '%hist%' ORDER BY (data_length + index_length) DESC;
+------------------------+-----------+
| Table | Size (MB) |
+------------------------+-----------+
| history_text | 198299.23 |
| history | 24207.36 |
| history_uint | 11006.19 |
| history_str | 6.38 |
| proxy_dhistory | 0.05 |
| proxy_history | 0.03 |
| history_log | 0.03 |
| lld_override_ophistory | 0.02 |
+------------------------+-----------+
8 rows in set (0.01 sec)

[LenR]

I'm contemplating our 5.0 to 6.0 upgrade, we have to re-host for the OS upgrade. I've thought about cloning the DB to the new host, while letting the old host continue to be the active monitoring host, convert the database, then switch to the new host. I'd only lose the history for the interval of the conversion. I haven't tested yet, my test platform is so much slower than production, the DB restore takes 24 hours. (xtrabackup, mysqldump might be "forever")

[lbm]

Yes its possible. But the history missing would for me likely be a couple of days, unless I can somehow speedup the alter tables / backup restore process.

I wished the layout was somewhat different for the history tables, so you could leave them out, and then later backfill the data, while zabbix is online.