zabbix MySQL 大表修改分区表方案

使用到MySQL login-path 免密登录数据库

mysql_config_editor set --login-path=zabbixlogin --host=localhost --user=root --password
mysql_config_editor print --all
mysql --login-path=zabbixlogin  # 测试是否可以登录

一、停掉zabbix服务

二、重建表为分区表–预计 15 min（含 buffer）

处理表使用的脚本

#!/bin/bash
# 生成 1年 分区
cat >/tmp/part1year.sql<<part1
set session group_concat_max_len=65536;
SELECT GROUP_CONCAT(partitions order by date_column SEPARATOR ',\n') partitions_text
FROM (
SELECT date_column,CONCAT("PARTITION ",DATE_FORMAT(date_column, 'p%Y%m%d%H00')," VALUES LESS THAN (",UNIX_TIMESTAMP(date_column),") ENGINE = InnoDB") partitions
FROM (
    SELECT DATE_SUB(CURDATE(), INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY) AS date_column
    FROM (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a
    CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b
    CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS c
) AS subquery
WHERE date_column >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR)) A;
part1
echo -e $(mysql --login-path=zabbixlogin -N </tmp/part1year.sql) >/tmp/part1year.txt

# 生成 30天 分区
cat >/tmp/part30day.sql<<part30
set session group_concat_max_len=65536;
SELECT GROUP_CONCAT(partitions order by date_column SEPARATOR ',\n') partitions_text
FROM (
SELECT date_column,CONCAT("PARTITION ",DATE_FORMAT(date_column, 'p%Y%m%d%H00')," VALUES LESS THAN (",UNIX_TIMESTAMP(date_column),") ENGINE = InnoDB") partitions
FROM (
    SELECT DATE_SUB(CURDATE(), INTERVAL (a.a + (10 * b.a)) DAY) AS date_column
    FROM (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a
    CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b
) AS subquery
WHERE date_column >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)) A;
part30
echo -e $(mysql --login-path=zabbixlogin -N </tmp/part30day.sql) >/tmp/part30day.txt

# 定义备份表后缀
bakmark=$(date +'%y%m%d')

# 生成指定表的 create 语句
table_change_part_sql() {
    tbs_name=$1
    mysql --login-path=zabbixlogin -N -e "show create table ${tbs_name};" zabbix >/tmp/${tbs_name}.txt
    if [ "$tbs_name" == "trends" ] || [ "$tbs_name" == "trends_uint" ];then
        partfile=/tmp/part1year.txt
    else
        partfile=/tmp/part30day.txt
    fi
    awk '{$1="";print $0}' /tmp/${tbs_name}.txt > /tmp/${tbs_name}.tmp
    echo -e $(cat /tmp/${tbs_name}.tmp) | grep -v PARTITION >/tmp/${tbs_name}.sql
    echo "PARTITION BY RANGE (\`clock\`)
(">>/tmp/${tbs_name}.sql
    cat ${partfile} >>/tmp/${tbs_name}.sql
    echo ");" >>/tmp/${tbs_name}.sql
    echo -e "#### $(date +'%F %T') : Table ${tbs_name} create SQL:\n$(cat /tmp/${tbs_name}.sql)"
}

# 指定 7 表操作
for tbs in history history_log history_str history_text history_uint trends trends_uint;do
    echo "### $(date +'%F %T') : Begin Table ${tbs}"
    table_change_part_sql ${tbs}
    mysql --login-path=zabbixlogin -N -e "RENAME TABLE ${tbs} TO ${tbs}_${bakmark};" zabbix
    if [ $? -ne 0 ]; then
        echo "### $(date +'%F %T') : RENAME TABLE ${tbs} TO ${tbs}_${bakmark};, Failed"
        exit 1
    else
        echo "### $(date +'%F %T') : RENAME TABLE ${tbs} TO ${tbs}_${bakmark};, successful"
    fi
    mysql --login-path=zabbixlogin -N zabbix </tmp/${tbs_name}.sql
    if [ $? -ne 0 ]; then
        echo "### $(date +'%F %T') : CREATE TABLE ${tbs} ;, Failed"
        exit 2
    else
        echo "### $(date +'%F %T') : CREATE TABLE ${tbs} ;, successful"
    fi
    mysql --login-path=zabbixlogin -N -e "ANALYZE TABLE ${tbs};" zabbix
    rm -rf /tmp/${tbs_name}.*
done

# 备份表还原脚本
# >/tmp/CREATE.txt
# for tbs in history history_log history_str history_text history_uint trends trends_uint;do
#     bakmark=$(date +'%y%m%d')
#     mysql --login-path=zabbixlogin -N -e "RENAME TABLE ${tbs}_${bakmark} TO ${tbs};" zabbix
#     mysql --login-path=zabbixlogin -N -e "show create table ${tbs};" zabbix >>/tmp/CREATE.txt
# done

三、启动zabbix服务

四、创建分区维护存储过程及event(预计时间 10 min)

    mysql -uroot -p -N zabbix -c </tmp/zabbix_partition.sql

/tmp/zabbix_partition.sql 脚本内容

DELIMITER $$
-- 厂商提供分区按天创建存储过程
DROP PROCEDURE IF EXISTS `partition_create`;
CREATE PROCEDURE `partition_create`(SCHEMANAME VARCHAR(64), TABLENAME VARCHAR(64), PARTITIONNAME VARCHAR(64), CLOCK INT)
BEGIN
    /*
       SCHEMANAME = The DB schema in which to make changes
       TABLENAME = The table with partitions to potentially delete
       PARTITIONNAME = The name of the partition to create
    */
    /*
       Verify that the partition does not already exist
    */
 
    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
        /*
           1. Print a message indicating that a partition was created.
           2. Create the SQL to create the partition.
           3. Execute the SQL from #2.
        */
        SELECT CONCAT( "partition_create(", SCHEMANAME, ",", TABLENAME, ",", PARTITIONNAME, ",", CLOCK, ")" ) AS msg;
        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$$
DELIMITER ;

DELIMITER $$
-- 厂商提供分区按天删除存储过程
DROP PROCEDURE IF EXISTS `partition_drop`;
CREATE PROCEDURE `partition_drop`(SCHEMANAME VARCHAR(64), TABLENAME VARCHAR(64), DELETE_BELOW_PARTITION_DATE BIGINT)
BEGIN
    /*
       SCHEMANAME = The DB schema in which to make changes
       TABLENAME = The table with partitions to potentially delete
       DELETE_BELOW_PARTITION_DATE = Delete any partitions with names that are dates older than this one (yyyy-mm-dd)
    */
    DECLARE done INT DEFAULT FALSE;
    DECLARE drop_part_name VARCHAR(16);
 
    /*
       Get a list of all the partitions that are older than the date
       in DELETE_BELOW_PARTITION_DATE.  All partitions are prefixed with
       a "p", so use SUBSTRING TO get rid of that character.
    */
    DECLARE myCursor CURSOR FOR
        SELECT partition_name
        FROM information_schema.partitions
        WHERE table_schema = SCHEMANAME AND TABLE_NAME = TABLENAME AND CAST(SUBSTRING(partition_name FROM 2) AS UNSIGNED) < DELETE_BELOW_PARTITION_DATE;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
 
    /*
       Create the basics for when we need to drop the partition.  Also, create
       @drop_partitions to hold a comma-delimited list of all partitions that
       should be deleted.
    */
    SET @alter_header = CONCAT("ALTER TABLE ", SCHEMANAME, ".", TABLENAME, " DROP PARTITION ");
    SET @drop_partitions = "";
 
    /*
       Start looping through all the partitions that are too old.
    */
    OPEN myCursor;
    read_loop: LOOP
        FETCH myCursor INTO drop_part_name;
        IF done THEN
            LEAVE read_loop;
        END IF;
        SET @drop_partitions = IF(@drop_partitions = "", drop_part_name, CONCAT(@drop_partitions, ",", drop_part_name));
    END LOOP;
    IF @drop_partitions != "" THEN
        /*
           1. Build the SQL to drop all the necessary partitions.
           2. Run the SQL to drop the partitions.
           3. Print out the table partitions that were deleted.
        */
        SET @full_sql = CONCAT(@alter_header, @drop_partitions, ";");
        PREPARE STMT FROM @full_sql;
        EXECUTE STMT;
        DEALLOCATE PREPARE STMT;
 
        SELECT CONCAT(SCHEMANAME, ".", TABLENAME) AS `table`, @drop_partitions AS `partitions_deleted`;
    ELSE
        /*
           No partitions are being deleted, so print out "N/A" (Not applicable) to indicate
           that no changes were made.
        */
        SELECT CONCAT(SCHEMANAME, ".", TABLENAME) AS `table`, "N/A" AS `partitions_deleted`;
    END IF;
END$$
DELIMITER ;

DELIMITER $$
-- 厂商提供分区按天维护（调用创建、删除等）存储过程
DROP PROCEDURE IF EXISTS `partition_maintenance`;
CREATE PROCEDURE `partition_maintenance`(SCHEMA_NAME VARCHAR(32), TABLE_NAME VARCHAR(32), KEEP_DATA_DAYS INT, HOURLY_INTERVAL INT, CREATE_NEXT_INTERVALS INT)
BEGIN
    DECLARE OLDER_THAN_PARTITION_DATE VARCHAR(16);
    DECLARE PARTITION_NAME VARCHAR(16);
    DECLARE LESS_THAN_TIMESTAMP INT;
    DECLARE CUR_TIME INT;
 
    CALL partition_verify(SCHEMA_NAME, TABLE_NAME, HOURLY_INTERVAL);
    SET CUR_TIME = UNIX_TIMESTAMP(DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00'));
    IF DATE(NOW()) = '2014-04-01' THEN
        SET CUR_TIME = UNIX_TIMESTAMP(DATE_FORMAT(DATE_ADD(NOW(), INTERVAL 1 DAY), '%Y-%m-%d 00:00:00'));
    END IF;
    SET @__interval = 1;
    create_loop: LOOP
        IF @__interval > CREATE_NEXT_INTERVALS THEN
            LEAVE create_loop;
        END IF;
 
        SET LESS_THAN_TIMESTAMP = CUR_TIME + (HOURLY_INTERVAL * @__interval * 3600);
        SET PARTITION_NAME = FROM_UNIXTIME(CUR_TIME + HOURLY_INTERVAL * (@__interval - 1) * 3600, 'p%Y%m%d%H00');
        CALL partition_create(SCHEMA_NAME, TABLE_NAME, PARTITION_NAME, LESS_THAN_TIMESTAMP);
        SET @__interval=@__interval+1;
    END LOOP;
 
    SET OLDER_THAN_PARTITION_DATE=DATE_FORMAT(DATE_SUB(NOW(), INTERVAL KEEP_DATA_DAYS DAY), '%Y%m%d0000');
    CALL partition_drop(SCHEMA_NAME, TABLE_NAME, OLDER_THAN_PARTITION_DATE);
 
END$$
DELIMITER ;

DELIMITER $$
-- 厂商提供分区检验存储过程
DROP PROCEDURE IF EXISTS `partition_verify`;
CREATE PROCEDURE `partition_verify`(SCHEMANAME VARCHAR(64), TABLENAME VARCHAR(64), HOURLYINTERVAL INT(11))
BEGIN
    DECLARE PARTITION_NAME VARCHAR(16);
    DECLARE RETROWS INT(11);
    DECLARE FUTURE_TIMESTAMP TIMESTAMP;
 
    /*
     * Check if any partitions exist for the given SCHEMANAME.TABLENAME.
     */
    SELECT COUNT(1) INTO RETROWS
    FROM information_schema.partitions
    WHERE table_schema = SCHEMANAME AND TABLE_NAME = TABLENAME AND partition_name IS NULL;
 
    /*
     * If partitions do not exist, go ahead and partition the table
     */
    IF RETROWS = 1 THEN
        /*
         * Take the current date at 00:00:00 and add HOURLYINTERVAL to it.  This is the timestamp below which we will store values.
         * We begin partitioning based on the beginning of a day.  This is because we don't want to generate a random partition
         * that won't necessarily fall in line with the desired partition naming (ie: if the hour interval is 24 hours, we could
         * end up creating a partition now named "p201403270600" when all other partitions will be like "p201403280000").
         */
        SET FUTURE_TIMESTAMP = TIMESTAMPADD(HOUR, HOURLYINTERVAL, CONCAT(CURDATE(), " ", '00:00:00'));
        SET PARTITION_NAME = DATE_FORMAT(CURDATE(), 'p%Y%m%d%H00');
 
        -- Create the partitioning query
        SET @__PARTITION_SQL = CONCAT("ALTER TABLE ", SCHEMANAME, ".", TABLENAME, " PARTITION BY RANGE(`clock`)");
        SET @__PARTITION_SQL = CONCAT(@__PARTITION_SQL, "(PARTITION ", PARTITION_NAME, " VALUES LESS THAN (", UNIX_TIMESTAMP(FUTURE_TIMESTAMP), "));");
 
        -- Run the partitioning query
        PREPARE STMT FROM @__PARTITION_SQL;
        EXECUTE STMT;
        DEALLOCATE PREPARE STMT;
    END IF;
END$$
DELIMITER ;

DELIMITER //
-- 
-- DROP PROCEDURE IF EXISTS `zabbix`.`create_tab_partitions` //
-- 
-- CREATE PROCEDURE `zabbix`.`create_tab_partitions` ()
-- BEGIN
--     CALL zabbix.create_next_partitions("zabbix","trends");
--     CALL zabbix.create_next_partitions("zabbix","trends_uint");
--     CALL zabbix.drop_old_partitions("zabbix","trends");
--     CALL zabbix.drop_old_partitions("zabbix","trends_uint");
-- END //
-- 

-- 自建按月创建存储过程
DROP PROCEDURE IF EXISTS `zabbix`.`create_next_partitions` //
CREATE PROCEDURE `zabbix`.`create_next_partitions` (SCHEMANAME varchar(64), TABLENAME varchar(64))
BEGIN
    DECLARE NEXTMONTH timestamp;
    DECLARE PARTITIONNAME varchar(16);
    DECLARE CLOCK int;
    SET @totalmonth = 3;    -- 创建分区数，创建当前月份之后3个月的分区
    SET @i = 1;

    createloop: LOOP
    SET NEXTMONTH =date_add(curdate()-day(curdate())+1,interval @i month);
    SET PARTITIONNAME = DATE_FORMAT( NEXTMONTH, 'p%Y%m' );
    SET CLOCK = UNIX_TIMESTAMP(DATE_FORMAT(DATE_ADD( NEXTMONTH ,INTERVAL 1 month),'%Y-%m-%d 00:00:00'));
    CALL zabbix.partition_create( SCHEMANAME, TABLENAME, PARTITIONNAME, CLOCK );
    SET @i=@i+1;

    IF @i > @totalmonth THEN
        LEAVE createloop;
    END IF;
    END LOOP;
END //

-- 自建调度按月删除存储过程（调用删除存储过程）
DROP PROCEDURE IF EXISTS `zabbix`.`drop_old_partitions` //
CREATE PROCEDURE `zabbix`.`drop_old_partitions` (SCHEMANAME varchar(64), TABLENAME varchar(64))
BEGIN
    DECLARE OLDCLOCK timestamp;
    DECLARE PARTITIONNAME varchar(16);
    DECLARE CLOCK int;
    SET @minmonth = 12;     -- 设置旧分区保留月份，如:2，则表示保留2月，删除2月前的分区
    SET @maxmonth = @minmonth+3;
    SET @i = @maxmonth;

    droploop: LOOP
    SET OLDCLOCK = date_sub(curdate()-day(curdate())+1,interval @i month);
    SET PARTITIONNAME = DATE_FORMAT( OLDCLOCK, 'p%Y%m');
    CALL zabbix.drop_partition( SCHEMANAME, TABLENAME, PARTITIONNAME );
    SET @i=@i-1;
    IF @i <= @minmonth THEN
        LEAVE droploop;
    END IF;
    END LOOP;
END //


-- 自建按月创建存储过程
-- DROP PROCEDURE IF EXISTS `zabbix`.`create_partition` //
-- CREATE PROCEDURE `zabbix`.`create_partition` (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
--     SELECT CONCAT( "create_partition(", SCHEMANAME, ",", TABLENAME, ",", PARTITIONNAME, ",", CLOCK, ")" ) AS msg;
--     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`.`drop_partition` //
CREATE PROCEDURE `zabbix`.`drop_partition` (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
    SELECT CONCAT( "drop_partition(", SCHEMANAME, ",", TABLENAME, ",", PARTITIONNAME, ")" ) AS msg;
    SET @sql = CONCAT( 'ALTER TABLE `', SCHEMANAME, '`.`', TABLENAME, '`', ' DROP PARTITION ', PARTITIONNAME, ';' );
    PREPARE STMT FROM @sql;
    EXECUTE STMT;
    DEALLOCATE PREPARE STMT;
    END IF;
END //
DELIMITER ;

DELIMITER $$
-- 创建调度存储过程，用于调度7表的分区管理存储过程
DROP PROCEDURE IF EXISTS `partition_maintenance_all`;
CREATE PROCEDURE `partition_maintenance_all`(SCHEMA_NAME VARCHAR(32))
BEGIN
    CALL partition_maintenance(SCHEMA_NAME, 'history', 14, 24, 7);
    CALL partition_maintenance(SCHEMA_NAME, 'history_log', 14, 24, 7);
    CALL partition_maintenance(SCHEMA_NAME, 'history_str', 14, 24, 7);
    CALL partition_maintenance(SCHEMA_NAME, 'history_text', 14, 24, 7);
    CALL partition_maintenance(SCHEMA_NAME, 'history_uint', 14, 24, 7);
    /* 厂商推荐趋势表按天分区，保存365天
    CALL partition_maintenance(SCHEMA_NAME, 'trends', 365, 24, 14);
    CALL partition_maintenance(SCHEMA_NAME, 'trends_uint', 365, 24, 14);
    */
    -- 实际使用按月分区，保存12个月
    CALL create_next_partitions(SCHEMA_NAME,'trends');
    CALL create_next_partitions(SCHEMA_NAME,'trends_uint');
    CALL drop_old_partitions(SCHEMA_NAME,'trends');
    CALL drop_old_partitions(SCHEMA_NAME,'trends_uint');
END $$
DELIMITER ;

-- 创建 event 执行记录表，用于记录 event 的执行情况
DROP TABLE IF EXISTS `zabbix`.`event_hist_logs`;
CREATE TABLE IF NOT EXISTS `zabbix`.`event_hist_logs` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`db_name` varchar(128) NOT NULL DEFAULT '' COMMENT 'DB数据库名字',
`event_name` varchar(128) NOT NULL DEFAULT '' COMMENT 'event 名字',
`start_time` datetime(3) NOT NULL DEFAULT now(3) COMMENT 'event 执行开始时间',
`end_time` datetime(3) DEFAULT NULL COMMENT 'event 执行结束时间',
`is_success` tinyint(4) DEFAULT 0 COMMENT 'event 是否成功--0，不成功--非0',
`duration` decimal(15,3) DEFAULT NULL COMMENT 'event 本次执行持续时间s',
`error_msg` varchar(512) DEFAULT NULL COMMENT 'event 错误信息',
`event_gid` varchar(36) NOT NULL COMMENT 'event 唯一性ID，UPPER(REPLACE(UUID(),-,)',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_event_hist_logs_event_gid` (`event_gid`),
KEY `idx_event_hist_logs_s_e_time` (`start_time`,`end_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

DELIMITER $$

-- 创建 event，调度实现定时分区管理，Added @ now()
DROP EVENT IF EXISTS `e_zabbix_partition_maintenance`;
CREATE EVENT IF NOT EXISTS `e_zabbix_partition_maintenance`
ON SCHEDULE EVERY 1 DAY
STARTS DATE_ADD(DATE_ADD(CURDATE(), INTERVAL 1 DAY), INTERVAL 1 HOUR)
ON COMPLETION PRESERVE
COMMENT '定时执行 zabbix 数据库 7 表分区维护'
DO
BEGIN
    DECLARE r_code CHAR(5) DEFAULT '00000';
    DECLARE r_msg TEXT;
    DECLARE v_error INT;
    DECLARE v_start_time DATETIME(3) DEFAULT NOW(3);
    DECLARE v_event_gid VARCHAR(36) DEFAULT UPPER(REPLACE(UUID(),'-',''));

    /* 删除 1 年前执行记录 */
    delete from zabbix.event_hist_logs where start_time < DATE_SUB(now(3), INTERVAL 1 YEAR) and event_name = 'e_zabbix_partition_maintenance';

    /* 新增一行执行记录 */
    INSERT INTO zabbix.event_hist_logs (db_name, event_name, start_time, event_gid)
    VALUES(DATABASE(), 'e_zabbix_partition_maintenance', v_start_time, v_event_gid);

    BEGIN
        DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
        BEGIN
            SET  v_error = 1;
            GET DIAGNOSTICS CONDITION 1 r_code = RETURNED_SQLSTATE, r_msg = MESSAGE_TEXT;
        END;
        /* 调用存储过程 partition_maintenance_all */
        CALL partition_maintenance_all('zabbix');
    END;

    /* 更新执行记录 */
    UPDATE zabbix.event_hist_logs
    SET end_time = NOW(3), is_success = ISNULL(v_error), duration = TIMESTAMPDIFF(microsecond,start_time, NOW(3)) / 1000000,
    error_msg = CONCAT('error = ', r_code,', message = ', r_msg)
    WHERE event_gid = v_event_gid;
    commit;

    /*
    -- 调用存储过程 partition_maintenance_all
    CALL partition_maintenance_all('zabbix');
    */
END $$
DELIMITER ;

-- 执行一次分区管理调度
CALL partition_maintenance_all('zabbix');

五、回插trends 相关表1年数据

注意：
不回插 history 相关表数据；
预计回插 5亿 数据；
回插过程会占用较大的IO（insert 数据、binlog生成–需要关注 binlog 及时清理，临时调整: set global binlog_expire_logs_seconds=60; flush logs;）;
回插过程有可能导致数据库响应变慢；

数据回插操作整理为脚本执行

#!/bin/bash
# 定义备份表后缀
bakmark=$(date +'%Y%m%d')
for tbs in history history_log history_str history_text history_uint trends trends_uint;do
    echo "### $(date +'%F %T') : Begin Move DATA From ${tbs}_${bakmark} to ${tbs}"
    crt_idx=
    insert_sql=
    if [ "$tbs" == "trends" ] || [ "$tbs" == "trends_uint" ];then
        daynum=365
        # trends 相关表生成回插语句
        insert_sql="insert into ${tbs} select * from ${tbs}_${bakmark} A where A.clock>UNIX_TIMESTAMP(DATE_FORMAT(DATE_SUB(NOW(), INTERVAL ${daynum} DAY), '%Y-%m-%d 00:00:00'));"
    else
        daynum=0
        # 回插操作可能使用到 clock 列索引，本次 history 相关表不回插数据，不建立索引
        # crt_idx="alter table ${tbs}_${bakmark} add index IDX_${tbs}_${bakmark}_clock (clock), algorithm = INPLACE, lock = none;"
    fi
    echo -e "${crt_idx}\n${insert_sql}\ncommit;" >/tmp/${tbs}_insert.sql
    # 分析表
    echo "ANALYZE TABLE ${tbs};" >>/tmp/${tbs}_insert.sql
    # 查看执行的语句
    cat /tmp/${tbs}_insert.sql
    # 执行生成的语句
    mysql --login-path=zabbixlogin -N zabbix </tmp/${tbs}_insert.sql
    # 判断是否执行成功
    if [ $? -ne 0 ]; then
        echo "### $(date +'%F %T') : TABLE ${tbs} Move Back ${daynum} DAY DATA, Failed"
        exit 5
    else
        echo "### $(date +'%F %T') : TABLE ${tbs} Move Back ${daynum} DAY DATA, successful"
    fi
done

六、调整后状态

1、history 相关表保存最近 30 个分区，trends 相关表保存 365 个分区（均为：1天数据占1个分区）；
2、分区表调整前数据保存在备份表 tablename_$(date +'%y%m%d') -- 如history_231128，调整后 history 表无数据，trends 表应该回插备份表所有数据；
3、调整之后借助 MySQL Event 实现分区表数据管理，不再使用"管家"数据保留策略，需要停用；