Zabbix partitions

backup-zabbix-history.sh

#!/bin/bash

db=zabbix

for f in history history_log history_str history_text history_uint
do
    mysqldump --no-create-info --lock-tables $db $f > `date +"%Y-%m-%d"`_$f.sql
done

for f in acknowledges alerts auditlog events service_alarms trends trends_unit
do
    mysqldump --no-create-info --lock-tables $db $f > `date +"%Y-%m-%d"`_$f.sql
done

crontab

#minute hour    mday    month   wday    who     command
#
0       6       */3     *       *       mysql   mysql -B zabbix -e "CALL createZabbixPartitions ();" 

zabbix-partitions.sql

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

truncate history;
truncate history_log;
truncate history_str;
truncate history_text;
truncate history_uint;

truncate acknowledges;
truncate alerts;
truncate auditlog;
truncate events;
truncate service_alarms;
truncate trends;
truncate trends_uint;

ALTER TABLE `acknowledges` DROP PRIMARY KEY, ADD KEY `acknowledgedid` (`acknowledgeid`);
ALTER TABLE `alerts` DROP PRIMARY KEY, ADD KEY `alertid` (`alertid`);
ALTER TABLE `auditlog` DROP PRIMARY KEY, ADD KEY `auditid` (`auditid`);
ALTER TABLE `events` DROP PRIMARY KEY, ADD KEY `eventid` (`eventid`);
ALTER TABLE `service_alarms` DROP PRIMARY KEY, ADD KEY `servicealarmid` (`servicealarmid`);
ALTER TABLE `history_log` DROP PRIMARY KEY, ADD PRIMARY KEY (`itemid`,`id`,`clock`);
ALTER TABLE `history_log` DROP KEY `history_log_2`;
ALTER TABLE `history_text` DROP PRIMARY KEY, ADD PRIMARY KEY (`itemid`,`id`,`clock`);
ALTER TABLE `history_text` DROP KEY `history_text_2`;

ALTER TABLE `acknowledges` DROP FOREIGN KEY `c_acknowledges_1`, DROP FOREIGN KEY `c_acknowledges_2`;
ALTER TABLE `alerts` DROP FOREIGN KEY `c_alerts_1`, DROP FOREIGN KEY `c_alerts_2`, DROP FOREIGN KEY `c_alerts_3`, DROP FOREIGN KEY `c_alerts_4`;
ALTER TABLE `auditlog` DROP FOREIGN KEY `c_auditlog_1`;
ALTER TABLE `service_alarms` DROP FOREIGN KEY `c_service_alarms_1`;
ALTER TABLE `auditlog_details` DROP FOREIGN KEY `c_auditlog_details_1`;

ALTER TABLE `zabbix`.`acknowledges` PARTITION BY RANGE(clock) (
    PARTITION p201410 VALUES LESS THAN (UNIX_TIMESTAMP("2014-11-01 00:00:00")));
ALTER TABLE `zabbix`.`alerts` PARTITION BY RANGE(clock) (
    PARTITION p201410 VALUES LESS THAN (UNIX_TIMESTAMP("2014-11-01 00:00:00")));
ALTER TABLE `zabbix`.`auditlog` PARTITION BY RANGE(clock) (
    PARTITION p201410 VALUES LESS THAN (UNIX_TIMESTAMP("2014-11-01 00:00:00")));
ALTER TABLE `zabbix`.`events` PARTITION BY RANGE(clock) (
    PARTITION p201410 VALUES LESS THAN (UNIX_TIMESTAMP("2014-11-01 00:00:00")));
ALTER TABLE `zabbix`.`service_alarms` PARTITION BY RANGE(clock) (
    PARTITION p201410 VALUES LESS THAN (UNIX_TIMESTAMP("2014-11-01 00:00:00")));
ALTER TABLE `zabbix`.`trends` PARTITION BY RANGE(clock) (
    PARTITION p201410 VALUES LESS THAN (UNIX_TIMESTAMP("2014-11-01 00:00:00")));
ALTER TABLE `zabbix`.`trends_uint` PARTITION BY RANGE(clock) (
    PARTITION p201410 VALUES LESS THAN (UNIX_TIMESTAMP("2014-11-01 00:00:00")));

ALTER TABLE `zabbix`.`history` PARTITION BY RANGE(clock) (
    PARTITION p20141025 VALUES LESS THAN (UNIX_TIMESTAMP("2014-10-25 00:00:00")));
ALTER TABLE `zabbix`.`history_log` PARTITION BY RANGE(clock) (
    PARTITION p20141025 VALUES LESS THAN (UNIX_TIMESTAMP("2014-10-25 00:00:00")));
ALTER TABLE `zabbix`.`history_str` PARTITION BY RANGE(clock) (
    PARTITION p20141025 VALUES LESS THAN (UNIX_TIMESTAMP("2014-10-25 00:00:00")));
ALTER TABLE `zabbix`.`history_text` PARTITION BY RANGE(clock) (
    PARTITION p20141025 VALUES LESS THAN (UNIX_TIMESTAMP("2014-10-25 00:00:00")));
ALTER TABLE `zabbix`.`history_uint` PARTITION BY RANGE(clock) (
    PARTITION p20141025 VALUES LESS THAN (UNIX_TIMESTAMP("2014-10-25 00:00:00")));


DELIMITER //
DROP PROCEDURE IF EXISTS `zabbix`.`createZabbixPartitions` //
CREATE PROCEDURE `zabbix`.`createZabbixPartitions` ()
BEGIN
    CALL zabbix.createDayPartitions("zabbix", "history");
    CALL zabbix.createDayPartitions("zabbix", "history_log");
    CALL zabbix.createDayPartitions("zabbix", "history_str");
    CALL zabbix.createDayPartitions("zabbix", "history_text");
    CALL zabbix.createDayPartitions("zabbix", "history_uint");
    CALL zabbix.createMonthPartitions("zabbix", "acknowledges");
    CALL zabbix.createMonthPartitions("zabbix", "alerts");
    CALL zabbix.createMonthPartitions("zabbix", "auditlog");
    CALL zabbix.createMonthPartitions("zabbix", "events");
    CALL zabbix.createMonthPartitions("zabbix", "service_alarms");
    CALL zabbix.createMonthPartitions("zabbix", "trends");
    CALL zabbix.createMonthPartitions("zabbix", "trends_uint");
    CALL zabbix.dropDayPartitions("zabbix", "history");
    CALL zabbix.dropDayPartitions("zabbix", "history_log");
    CALL zabbix.dropDayPartitions("zabbix", "history_str");
    CALL zabbix.dropDayPartitions("zabbix", "history_text");
    CALL zabbix.dropDayPartitions("zabbix", "history_uint");
    CALL zabbix.dropMonthPartitions("zabbix", "acknowledges");
    CALL zabbix.dropMonthPartitions("zabbix", "alerts");
    CALL zabbix.dropMonthPartitions("zabbix", "auditlog");
    CALL zabbix.dropMonthPartitions("zabbix", "events");
    CALL zabbix.dropMonthPartitions("zabbix", "service_alarms");
    CALL zabbix.dropMonthPartitions("zabbix", "trends");
    CALL zabbix.dropMonthPartitions("zabbix", "trends_uint");
END //

DROP PROCEDURE IF EXISTS `zabbix`.`createDayPartitions` //
CREATE PROCEDURE `zabbix`.`createDayPartitions` (schemaName varchar(64), tableName varchar(64))
BEGIN
    DECLARE nextClock timestamp;
    DECLARE partitionName varchar(16);
    DECLARE clock int;
    SET @totalDays = 7;
    SET @i = 1;
    createloop: LOOP
        SET nextClock = DATE_ADD(NOW(), INTERVAL @i DAY);
        SET partitionName = DATE_FORMAT(nextClock, 'p%Y%m%d');
        SET clock = UNIX_TIMESTAMP(DATE_FORMAT(DATE_ADD(nextClock, INTERVAL 1 DAY),'%Y-%m-%d 00:00:00'));
        CALL zabbix.createPartition(schemaName, tableName, partitionName, clock);
        SET @i=@i+1;
        IF @i > @totalDays THEN
            LEAVE createloop;
        END IF;
    END LOOP;
END //

DROP PROCEDURE IF EXISTS `zabbix`.`createMonthPartitions` //
CREATE PROCEDURE `zabbix`.`createMonthPartitions` (schemaName varchar(64), tableName varchar(64))
BEGIN
    DECLARE nextClock timestamp;
    DECLARE partitionName varchar(16);
    DECLARE clock int;
    DECLARE currentMonth date;
    SET currentMonth = DATE(NOW()) - INTERVAL DAY(NOW()) DAY + INTERVAL 1 DAY;
    SET @totalMonth = 3;
    SET @i = 1;
    createloop: LOOP
        SET nextClock = DATE_ADD(currentMonth, INTERVAL @i MONTH);
        SET partitionName = DATE_FORMAT(nextClock, 'p%Y%m');
        SET clock = UNIX_TIMESTAMP(DATE_FORMAT(DATE_ADD(nextClock, INTERVAL 1 MONTH),'%Y-%m-%d 00:00:00'));
        CALL zabbix.createPartition(schemaName, tableName, partitionName, clock);
        SET @i=@i+1;
        IF @i > @totalMonth THEN
            LEAVE createloop;
        END IF;
    END LOOP;
END //


DROP PROCEDURE IF EXISTS `zabbix`.`dropMonthPartitions` //
CREATE PROCEDURE `zabbix`.`dropMonthPartitions` (schemaName varchar(64), tableName varchar(64))
BEGIN
    DECLARE oldClock timestamp;
    DECLARE partitionName varchar(16);
    DECLARE clock int;
    DECLARE currentMonth date;
    SET currentMonth = DATE(NOW()) - INTERVAL DAY(NOW()) DAY + INTERVAL 1 DAY;
    SET @minMonth = 12;
    SET @maxMonth = @minMonth+3;
    SET @i = @maxMonth;
    droploop: LOOP
        SET oldClock = DATE_SUB(NOW(), INTERVAL @i MONTH);
        SET partitionName = DATE_FORMAT(oldClock, 'p%Y%m');
        CALL zabbix.dropPartition(schemaName, tableName, partitionName);
        SET @i=@i-1;
        IF @i <= @minMonth THEN
            LEAVE droploop;
        END IF;
    END LOOP;
END //


DROP PROCEDURE IF EXISTS `zabbix`.`dropDayPartitions` //
CREATE PROCEDURE `zabbix`.`dropDayPartitions` (schemaName varchar(64), tableName varchar(64))
BEGIN
    DECLARE oldClock timestamp;
    DECLARE partitionName varchar(16);
    DECLARE clock int;
    SET @minDays = 7;
    SET @maxDays = @minDays+4;
    SET @i = @maxDays;
    droploop: LOOP
        SET oldClock = DATE_SUB(NOW(), INTERVAL @i DAY);
        SET partitionName = DATE_FORMAT(oldClock, 'p%Y%m%d');
        CALL zabbix.dropPartition(schemaName, tableName, partitionName);
        SET @i=@i-1;
        IF @i <= @minDays THEN
            LEAVE droploop;
        END IF;
    END LOOP;
END //


DROP PROCEDURE IF EXISTS `zabbix`.`createPartition` //
CREATE PROCEDURE `zabbix`.`createPartition` (schemaName varchar(64), tableName varchar(64), partitionName varchar(64), clock int)
BEGIN
    DECLARE retRows int;
    SELECT COUNT(1) INTO retRows
        FROM `information_schema`.`partitions`
        WHERE `table_schema` = schemaName AND `table_name` = tableName AND `partition_name` = partitionName;

    IF retRows = 0 THEN
        SET @sql = CONCAT('ALTER TABLE `', schemaName, '`.`', tableName, '`',
            ' ADD PARTITION (PARTITION ', partitionName, ' VALUES LESS THAN (', clock, '));');
        PREPARE STMT FROM @sql;
        EXECUTE STMT;
        DEALLOCATE PREPARE STMT;
    END IF;
END //

DROP PROCEDURE IF EXISTS `zabbix`.`dropPartition` //
CREATE PROCEDURE `zabbix`.`dropPartition` (schemaName varchar(64), tableName varchar(64), partitionName varchar(64))
BEGIN
    DECLARE retRows int;
    SELECT COUNT(1) INTO retRows
        FROM `information_schema`.`partitions`
        WHERE `table_schema` = schemaName AND `table_name` = tableName AND `partition_name` = partitionName;

    IF retRows = 1 THEN
        SET @sql = CONCAT('ALTER TABLE `', schemaName, '`.`', tableName, '`',
            ' DROP PARTITION ', partitionName, ';');
        PREPARE STMT FROM @sql;
        EXECUTE STMT;
        DEALLOCATE PREPARE STMT;
    END IF;
END //

CREATE EVENT IF NOT EXISTS `zabbix`.`createPartitionsEvent`
    ON SCHEDULE EVERY 1 DAY
    STARTS '2013-09-28 04:00:00'
    ON COMPLETION PRESERVE
    ENABLE
    COMMENT 'Rotate Zabbix Partitions'
    DO BEGIN
        CALL zabbix.createZabbixPartitions();
    END //

DELIMITER ;

/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;

(!) use screen utility

1. stop zabbix server
2. backup: bash backup-zabbix-history.sh
3. patch database: cat ~/zabbix-partitions.sql | mysql zabbix
4. create partitions for current date mysql -B zabbix -e "CALL createZabbixPartitions ();"
5. push zabbix data back: pv *.sql | mysql zabbix. Replace pv to cat if you dont need to track process.
6. start zabbix
7. add crontab task from crontab file