使用event为Zabbix定期新增与删除分区

Posted on 2016-12-09 | In MySQL | | Views:

应用场景

上一篇文章介绍的Zabbix数据表使用TokuDB引擎及分区表，同时需要每个月为这些表新建一个分区，及删除历史分区。由于MySQL不会自动新建及删除分区，所以需要额外创建一个定时任务，来进行表分区的管理。这里采用MySQL的event来做。

实现的思路是：定时任务每个月执行一次，在执行的时候，创建下个月的分区，及删除大于保留时长的历史分区，并将操作记录到log表里，便于查询历史操作记录。

实现方式

相关表结构

总共创建两个表：

partition_management：将需要进行分区管理的表信息、历史数据保留时长存储在该表
partition_logs：记录表分区操作记录

USE `zabbix`;

-- Table: partition_management
DROP TABLE IF EXISTS `partition_management`;
CREATE TABLE `partition_management` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `schema_name` VARCHAR(64) NOT NULL COMMENT '库名',
  `table_name` VARCHAR(64) NOT NULL COMMENT '表名',
  `keep_period` INT(10) UNSIGNED NOT NULL COMMENT '历史数据保留时长(单位:月)',
  `is_del_partition` TINYINT(4) NOT NULL DEFAULT '0' COMMENT '是否删除历史分区(0:不删除, 1:删除)',
  `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` DATETIME DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `un_partition_management_1` (`schema_name`,`table_name`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='分区管理表';

-- Table: partition_logs
DROP TABLE IF EXISTS `partition_logs`;
CREATE TABLE `partition_logs` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `schema_name` VARCHAR(64) NOT NULL COMMENT '库名',
  `table_name` VARCHAR(64) NOT NULL COMMENT '表名',
  `maintenance_type` ENUM('ADD','DROP') NOT NULL COMMENT '分区维护类型',
  `partition_name` VARCHAR(200) NOT NULL COMMENT '分区名称',
  `execute_sql` VARCHAR(200) NOT NULL COMMENT '执行的SQL',
  `message` VARCHAR(100) NOT NULL COMMENT '备注信息',
  `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` DATETIME DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `ix_partition_logs_1` (`schema_name`,`table_name`,`partition_name`),
  KEY `ix_partition_logs_2` (`create_time`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='表分区定时任务执行记录';

-- Initialization data
INSERT  INTO `partition_management`(`schema_name`,`table_name`,`keep_period`,`is_del_partition`) VALUES ('zabbix','history',6,1);
INSERT  INTO `partition_management`(`schema_name`,`table_name`,`keep_period`,`is_del_partition`) VALUES ('zabbix','history_log',6,1);
INSERT  INTO `partition_management`(`schema_name`,`table_name`,`keep_period`,`is_del_partition`) VALUES ('zabbix','history_str',6,1);
INSERT  INTO `partition_management`(`schema_name`,`table_name`,`keep_period`,`is_del_partition`) VALUES ('zabbix','history_text',6,1);
INSERT  INTO `partition_management`(`schema_name`,`table_name`,`keep_period`,`is_del_partition`) VALUES ('zabbix','history_uint',6,1);
INSERT  INTO `partition_management`(`schema_name`,`table_name`,`keep_period`,`is_del_partition`) VALUES ('zabbix','trends',24,1);
INSERT  INTO `partition_management`(`schema_name`,`table_name`,`keep_period`,`is_del_partition`) VALUES ('zabbix','trends_uint',24,1);

相关存储过程

总共创建以下3个存储过程：

prc_partition_create：新建表分区
prc_partition_drop：删除表分区
prc_partition_maintenance：调用以上两个存储过程，进行表分区的新建与删除

-- Procedure: prc_partition_create
DELIMITER $$

USE zabbix$$

DROP PROCEDURE IF EXISTS prc_partition_create$$
CREATE DEFINER = 'root'@'localhost' PROCEDURE prc_partition_create(
    IN i_schema_name VARCHAR(64)    -- 分区表所在的库名
    ,IN i_table_name VARCHAR(64)    -- 分区表名
    ,IN i_partition_name VARCHAR(64)    -- 当前分区名称
    ,IN i_partition_value VARCHAR(50)    -- 当前分区值范围
)
    COMMENT '新建表分区'
BEGIN
    DECLARE v_partition_count INT DEFAULT 0;
    DECLARE v_is_maxvalue INT DEFAULT 0;
    
    SET @maintenance_type = 'ADD';    -- 进行表分区操作的类型
    
    /*
      校验是否存在大于当前分区范围值的分区，
      如果存在，则不创建该分区。
    */
    SELECT COUNT(1) INTO v_partition_count
    FROM information_schema.partitions
    WHERE table_schema = i_schema_name
    AND table_name = i_table_name
    AND partition_description <> 'MAXVALUE'
    AND partition_description >= i_partition_value;
    
    
    -- 创建分区，并将相关信息记录到日志表
    IF v_partition_count = 0 THEN
        SET @message = '';
        
        -- 判断是否存在MAXVALUE分区，并使用相应的SQL添加分区
        SELECT COUNT(1) INTO v_is_maxvalue
        FROM information_schema.partitions
        WHERE table_schema = i_schema_name
        AND table_name = i_table_name
        AND partition_description = 'MAXVALUE';
        
        IF v_is_maxvalue = 1 THEN
            SET @execute_sql = CONCAT('ALTER TABLE ', i_schema_name, '.', i_table_name
                ,' REORGANIZE PARTITION pmore INTO (PARTITION ', i_partition_name, ' VALUES LESS THAN (', i_partition_value, ')'
                ,', PARTITION pmore VALUES LESS THAN MAXVALUE);');
        ELSE
            SET @execute_sql = CONCAT('ALTER TABLE ', i_schema_name, '.', i_table_name
                ,' ADD PARTITION (PARTITION ', i_partition_name, ' VALUES LESS THAN (', i_partition_value, '));');
        END IF;
        
        INSERT INTO partition_logs(schema_name, table_name, partition_name, maintenance_type, execute_sql, message)
            VALUES(i_schema_name, i_table_name, i_partition_name, @maintenance_type, @execute_sql, @message);
        
        PREPARE execute_sql FROM @execute_sql;
        EXECUTE execute_sql;
        DEALLOCATE PREPARE execute_sql;
        
    ELSE
        SET @message = CONCAT('Partition value greater than or equal to the given value(', IFNULL(i_partition_value, 'NULL'), ') already exists.');
        SET @execute_sql = '';
        
        INSERT INTO partition_logs(schema_name, table_name, partition_name, maintenance_type, execute_sql, message)
            VALUES(i_schema_name, i_table_name, i_partition_name, @maintenance_type, @execute_sql, @message);
    END IF;
    
END$$

DELIMITER ;


-- Procedure: prc_partition_drop
DELIMITER $$

USE zabbix$$

DROP PROCEDURE IF EXISTS prc_partition_drop$$
CREATE DEFINER = 'root'@'localhost' PROCEDURE prc_partition_drop(
    IN i_schema_name VARCHAR(64)    -- 分区表所在的库名
    ,IN i_table_name VARCHAR(64)    -- 分区表名
    ,IN i_del_below_part_value VARCHAR(50)    -- 删除小于此分区值的分区(不包含此分区值)
)
    COMMENT '删除历史表分区'
BEGIN
    DECLARE v_drop_part_name VARCHAR(64);
    DECLARE v_done INT DEFAULT FALSE;
    
    -- 获取小于给定分区值的所有分区名称
    DECLARE cur_get_partition_names CURSOR FOR
        SELECT partition_name
        FROM information_schema.partitions
        WHERE table_schema = i_schema_name
        AND table_name = i_table_name
        AND partition_description <> 'MAXVALUE'
        AND partition_description < i_del_below_part_value;
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;
    
    SET @maintenance_type = 'DROP';    -- 进行表分区操作的类型
    SET @alter_header = CONCAT('ALTER TABLE ', i_schema_name, '.', i_table_name, ' DROP PARTITION ');
    SET @drop_partitions = '';
    SET @message = '';
    SET @execute_sql = '';
    
    
    -- 将所有需要删除的分区拼接起来
    OPEN cur_get_partition_names;
    loop_1: LOOP
        FETCH cur_get_partition_names INTO v_drop_part_name;
        
        IF v_done THEN
            LEAVE loop_1;
        END IF;
        
        SET @drop_partitions = IF(@drop_partitions = '', v_drop_part_name, CONCAT(@drop_partitions, ',', v_drop_part_name));
    END LOOP loop_1;
    
    
    -- 删除小于给定分区值的所有分区，并将相关信息记录到日志表
    IF @drop_partitions != '' THEN
        SET @execute_sql = CONCAT(@alter_header, @drop_partitions, ';');
        
        INSERT INTO partition_logs(schema_name, table_name, partition_name, maintenance_type, execute_sql, message)
            VALUES(i_schema_name, i_table_name, @drop_partitions, @maintenance_type, @execute_sql, @message);
        
        PREPARE execute_sql FROM @execute_sql;
        EXECUTE execute_sql;
        DEALLOCATE PREPARE execute_sql;
        
    ELSE
        SET @message = CONCAT('No partition less than the given value(', IFNULL(i_del_below_part_value, 'NULL'), ') exists.');
        
        INSERT INTO partition_logs(schema_name, table_name, partition_name, maintenance_type, execute_sql, message)
            VALUES(i_schema_name, i_table_name, @drop_partitions, @maintenance_type, @execute_sql, @message);
    END IF;
    
END$$

DELIMITER ;


-- Procedure: prc_partition_maintenance
DELIMITER $$

USE zabbix$$

DROP PROCEDURE IF EXISTS prc_partition_maintenance$$
CREATE DEFINER = 'root'@'localhost' PROCEDURE prc_partition_maintenance(
    IN i_current_year_month VARCHAR(10)    -- 执行添加、删除分区的年月，如：'201611'
)
  -- DETERMINISTIC
  COMMENT '管理表分区（新建分区及删除历史分区）'
BEGIN
    DECLARE v_schema_name VARCHAR(64);    -- 分区表所在的库名
    DECLARE v_table_name VARCHAR(64);    -- 分区表名
    DECLARE v_partition_type VARCHAR(18);    -- 分区类型
    DECLARE v_keep_period INT;    -- 分区表需要保留的历史数据时长（月）
    DECLARE v_del_partition TINYINT;    -- 是否删除历史分区
    DECLARE v_done INT DEFAULT FALSE;
    
    -- 获取所有的表名，对应的库名，及相应的分区详情
    DECLARE cur_get_partition_details CURSOR FOR
        SELECT DISTINCT schema_name
            ,table_name
            ,keep_period
            ,is_del_partition
        FROM partition_management
        ORDER BY schema_name, table_name;
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;
    
    -- 如果没有指定执行添加、删除分区的年月，则默认为当前年月
    IF i_current_year_month IS NULL OR i_current_year_month = '' THEN
        SET i_current_year_month = DATE_FORMAT(CURRENT_DATE(), '%Y%m');
    END IF;
    
    SET @current_part_name = CONCAT('p', i_current_year_month);
    SET @less_than_date = CONCAT(PERIOD_ADD(i_current_year_month, 1), '01');    -- 当前分区的值范围对应的日期（1号）
    
    
    -- 读取分区管理表数据，并进行新分区创建和历史分区删除
    OPEN cur_get_partition_details;
    
    loop_1: LOOP
        FETCH cur_get_partition_details INTO v_schema_name, v_table_name, v_keep_period, v_del_partition;
        
        IF v_done THEN
            LEAVE loop_1;
        END IF;
        
        
        -- 获取当前表的分区类型，如果为RANGE，则需要将日期转换为整数（UNIX_TIMESTAMP）。Zabbix表使用的此种分区类型。
        -- 如果分区类型为RANGE COLUMNS，则不需要转换日期格式。日志库的表使用的此种分区类型。
        SELECT DISTINCT partition_method INTO v_partition_type
        FROM information_schema.partitions
        WHERE table_schema = v_schema_name
        AND table_name = v_table_name;
        
        SET @del_below_part_date = DATE_ADD(@less_than_date, INTERVAL -v_keep_period MONTH);    -- 需要删除的分区的值对应的日期（删除小于此值的分区）
        
        IF v_partition_type = 'RANGE' THEN
            SET @less_than_value = UNIX_TIMESTAMP(@less_than_date);    -- 当前分区的值范围
            SET @del_below_part_value = UNIX_TIMESTAMP(@del_below_part_date);    -- 删除小于此分区值的分区
        ELSEIF v_partition_type = 'RANGE COLUMNS' THEN
            SET @less_than_value = CONCAT("'", DATE_FORMAT(@less_than_date, '%Y-%m-%d %H:%i:%s'), "'");
            SET @del_below_part_value = CONCAT("'", DATE_FORMAT(@del_below_part_date, '%Y-%m-%d %H:%i:%s'), "'");
        END IF;
        
        
        -- 新建分区。如果需要删除历史分区，则进行删除
        CALL prc_partition_create(v_schema_name, v_table_name, @current_part_name, @less_than_value);
        
        IF v_del_partition = 1 THEN
            CALL prc_partition_drop(v_schema_name, v_table_name, @del_below_part_value);
        END IF;
        
    END LOOP loop_1;
    CLOSE cur_get_partition_details;
END$$

DELIMITER ;

相关定时任务

创建一个event(event_partition_maintenance)，每月执行一次，通过调用存储过程prc_partition_maintenance，来达到管理分区的目的。

-- Event: event_partition_maintenance
DELIMITER $$

USE zabbix$$

DROP EVENT IF EXISTS event_partition_maintenance$$
CREATE DEFINER='root'@'localhost' EVENT event_partition_maintenance
    ON SCHEDULE EVERY 1 MONTH STARTS '2016-12-28 20:00:00'
    ON COMPLETION PRESERVE
    ENABLE
    COMMENT '定期执行Procedure(prc_partition_maintenance)，管理表分区'
DO
BEGIN
    -- 每月执行该任务，创建下一个月的分区表
    DECLARE v_current_year_month VARCHAR(10);
    DECLARE v_next_year_month VARCHAR(10);
    
    SET v_current_year_month = DATE_FORMAT(CURRENT_DATE(), '%Y%m');
    SET v_next_year_month = PERIOD_ADD(v_current_year_month, 1);
    
    CALL prc_partition_maintenance(v_next_year_month);
END$$

DELIMITER ;

脚本共享

以上所有脚本的完整文件，可以从以下GitHub源获取：
https://github.com/tyoungcn/mysql_operations/tree/master/zabbix_auto_partition

参考文档

处理Zabbix历史数据库办法二——使用MySQL表分区：
http://john88wang.blog.51cto.com/2165294/1771557

Post author: Tyoung
Post link: https://tyoung.me/2016/12/create_and_drop_partitions_for_zabbix_tables_on_schedule_by_mysql_event/
Copyright Notice: All articles in this blog are licensed under CC BY-NC-SA 4.0 unless stating additionally.