MySQL monthly partitions Zabbix

CREATE TABLE history_partitioned LIKE history;

ALTER TABLE `history_partitioned` PARTITION BY RANGE(clock)
(
PARTITION p2020_01 VALUES LESS THAN (UNIX_TIMESTAMP("2020-02-01 00:00:00")) ENGINE=InnoDB,
PARTITION p2020_02 VALUES LESS THAN (UNIX_TIMESTAMP("2020-03-01 00:00:00")) ENGINE=InnoDB,
PARTITION p2020_03 VALUES LESS THAN (UNIX_TIMESTAMP("2020-04-01 00:00:00")) ENGINE=InnoDB,
PARTITION p2020_04 VALUES LESS THAN (UNIX_TIMESTAMP("2020-05-01 00:00:00")) ENGINE=InnoDB,
PARTITION p2020_05 VALUES LESS THAN (UNIX_TIMESTAMP("2020-06-01 00:00:00")) ENGINE=InnoDB,
PARTITION p2020_06 VALUES LESS THAN (UNIX_TIMESTAMP("2020-07-01 00:00:00")) ENGINE=InnoDB,
PARTITION p2020_07 VALUES LESS THAN (UNIX_TIMESTAMP("2020-08-01 00:00:00")) ENGINE=InnoDB,
PARTITION p2020_08 VALUES LESS THAN (UNIX_TIMESTAMP("2020-09-01 00:00:00")) ENGINE=InnoDB,
PARTITION p2020_09 VALUES LESS THAN (UNIX_TIMESTAMP("2020-10-01 00:00:00")) ENGINE=InnoDB,
PARTITION p2020_10 VALUES LESS THAN (UNIX_TIMESTAMP("2020-11-01 00:00:00")) ENGINE=InnoDB,
PARTITION p2020_11 VALUES LESS THAN (UNIX_TIMESTAMP("2020-12-01 00:00:00")) ENGINE=InnoDB,
PARTITION p2020_12 VALUES LESS THAN (UNIX_TIMESTAMP("2021-01-01 00:00:00")) ENGINE=InnoDB
);

Set active data to be writened in partitioned table:

RENAME TABLE history TO history_old; RENAME TABLE history_partitioned TO history;

Move data:

SET SESSION SQL_LOG_BIN=0; INSERT INTO history (SELECT * FROM history_old);

Drop table:

DROP TABLE history_old;

Optimize partitions:

RENAME TABLE history_uint TO history_uint_old;
CREATE TABLE history_uint LIKE history_uint_old;
ALTER TABLE history_uint_old REBUILD PARTITION p2020_06,p2020_07,p2020_08,p2020_09;
RENAME TABLE history_uint TO history_uint_tmp;
RENAME TABLE history_uint_old TO history_uint;
INSERT IGNORE INTO history_uint SELECT * FROM history_uint_tmp;
TRUNCATE TABLE history_uint_tmp;

Restore back from archive:

mysql zabbix -e "create table history_old like history;"

Inset into _old:

echo "SET SESSION SQL_LOG_BIN=0;" | gzip | zcat - history_old*gz.2020-09* | mysql zabbix

Move data:

SET SESSION SQL_LOG_BIN=0; INSERT IGNORE INTO history (SELECT * FROM history_old);

Truncate:

TRUNCATE TABLE history_old;

for archive in $(ls -1 history_log_old.sql.gz*) ; do echo $archive;  done
for archive in $(ls -1 history_log_old.sql.gz*) ; do echo $archive; time zcat $archive | sed "s|history_log_old|history_log|" | mysql zabbix; done
for archive in $(ls -1 history_str_old.sql.gz*) ; do echo $archive; time zcat $archive | sed "s|history_str_old|history_str|" | mysql zabbix; done
for archive in $(ls -1 history_text_old.sql.gz*) ; do echo $archive; time zcat $archive | sed "s|history_text_old|history_text|" | mysql zabbix; done

Reverse order:

for archive in $(ls -1 history_uint_old.sql.gz* | tac) ; do echo $archive; time zcat $archive | mysql zabbix; done