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

应用场景

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

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

实现方式

相关表结构

总共创建两个表:

  • partition_management:将需要进行分区管理的表信息、历史数据保留时长存储在该表
  • partition_logs:记录表分区操作记录
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
USE `zabbix`;
-- Table: partition_management
DROP TABLE IF EXISTS `partition_management`;
CREATE TABLE `partition_management` (
  `id` INT(10UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `schema_name` VARCHAR(64NOT NULL COMMENT '库名',
  `table_name` VARCHAR(64NOT NULL COMMENT '表名',
  `keep_period` INT(10UNSIGNED NOT NULL COMMENT '历史数据保留时长(单位:月)',
  `is_del_partition` TINYINT(4NOT 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(10UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `schema_name` VARCHAR(64NOT NULL COMMENT '库名',
  `table_name` VARCHAR(64NOT NULL COMMENT '表名',
  `maintenance_type` ENUM('ADD','DROP'NOT NULL COMMENT '分区维护类型',
  `partition_name` VARCHAR(200NOT NULL COMMENT '分区名称',
  `execute_sql` VARCHAR(200NOT NULL COMMENT '执行的SQL',
  `message` VARCHAR(100NOT 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:调用以上两个存储过程,进行表分区的新建与删除
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
-- 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(1INTO 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(1INTO 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,来达到管理分区的目的。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- 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

参考文档