zabbix_zabbix监控mysql_zabbix二次开发(4)

SET @i=@i+1;

IF @i > @totaldays THEN

LEE createloop;

END IF;

END LOOP;

END //

DROP PROCEDURE IF EXISTS `zabbix`.`drop_old_partitions` //

CREATE PROCEDURE `zabbix`.`drop_old_partitions` (SCHEMANAMEvarchar(64), TABLENAME varchar(64))

BEGIN

DECLARE OLDCLOCK timestamp;

DECLARE PARTITIONNAME varchar(16);

DECLARE CLOCK int;

SET @mindays = 3;

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.drop_partition( SCHEMANAME, TABLENAME, PARTITIONNAME);

SET @i=@i-1;

IF @i <= @mindays THEN

LEE droploop;

END IF;

END LOOP;

END //

DROP PROCEDURE IF EXISTS `zabbix`.`create_partition` //

CREATE PROCEDURE `zabbix`.`create_partition` (SCHEMANAMEvarchar(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))

本文来自电脑杂谈，转载请注明本文网址：
http://www.pc-fly.com/a/ruanjian/article-37966-4.html