ITPub博客

首页 > 数据库 > MySQL > mysql 定时添加删除历史分区

mysql 定时添加删除历史分区

原创 MySQL 作者:18141908802 时间:2018-11-01 11:38:20 0 删除 编辑




1. 新建表

CREATE TABLE `perf_biz_vm_new` (




           `CREATE_TIME` datetime NOT NULL COMMENT '性能采集时间',




           `VM_ID` varchar(80) NOT NULL COMMENT '虚拟机ID',




            `PROCESSOR_USED` varchar(100) DEFAULT NULL COMMENT 'CPU利用率(%)',




            `MEM_USED` varchar(100) DEFAULT NULL COMMENT '内存的使用率(%)',




            `MEM_UTILITY` varchar(100) DEFAULT NULL COMMENT '可用内存量(bytes)',




            `BYTES_IN` varchar(100) DEFAULT NULL COMMENT '流入流量速率(Mbps)',




            `BYTES_OUT` varchar(100) DEFAULT NULL COMMENT '流出流量速率(Mbps)',




            `PROC_RUN` varchar(100) DEFAULT NULL COMMENT 'CPU运行队列中进程个数',




            `WRITE_IO` varchar(100) DEFAULT NULL COMMENT '虚拟磁盘写入速率(Mb/s)',




            `READ_IO` varchar(100) DEFAULT NULL COMMENT '虚拟磁盘读取速率(Mb/s)',




            `PID` varchar(36) NOT NULL,




             PRIMARY KEY (`PID`,`CREATE_TIME`),




             KEY `mytable_categoryid` (`CREATE_TIME`) USING BTREE,




             KEY `perf_biz_vm_vm_id_create_time` (`VM_ID`,`CREATE_TIME`)




 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='虚拟机性能采集表'




 /*!50500 PARTITION BY RANGE  COLUMNS(CREATE_TIME)




(PARTITION p20180225 VALUES LESS THAN ('20180226') ENGINE = InnoDB,




 PARTITION p20180226 VALUES LESS THAN ('20180227') ENGINE = InnoDB,




 PARTITION p20180227 VALUES LESS THAN ('20180228') ENGINE = InnoDB,




 PARTITION p20180228 VALUES LESS THAN ('20180229') ENGINE = InnoDB,




 PARTITION p20180229 VALUES LESS THAN ('20180230') ENGINE = InnoDB) */

 

 

2. 更换表名

 

 rename table perf_biz_vm to perf_biz_vm_old;


rename table perf_biz_vm_new to perf_biz_vm;

 

 

3. 把最近2天的数据插入到新表里面.


 


#!/bin/bash


function insert(){


end_time="$1 $2"


start_time="$3 $4"


mysql -u'user' -p'passwd' << !


use monitor_alarm_openstack;


set innodb_flush_log_at_trx_commit=0;


start transaction;


insert into perf_biz_vm select * from perf_biz_vm_old where create_time < '$end_time' and create_time > '$start_time';


commit;


select TABLE_ROWS from information_schema.tables where TABLE_SCHEMA ="monitor_alarm" and TABLE_NAME="perf_biz_vm";


!


}


base_time="2018-02-27 2:00:00"


while true


do


        #end_time=$(date -d "-1hour $base_time" +%Y-%m-%d" "%H:%M:%S)


        end_time=$base_time


        start_time=$(date -d "-1hour $end_time" +%Y-%m-%d" "%H:%M:%S)


        #base_time=$end_time


        base_time=$start_time


        echo "Cur_time: $(date +%Y%m%d" "%H%M%S)" | tee -a 1.log


        echo "Range: $end_time $start_time" | tee -a 1.log


        insert ${end_time} ${start_time} | tee -a 1.log


        sleep 2


done



4.编写存储过程用于定期创建新的分区,并删除几天前旧的分区


代码如下:


delimiter $$


CREATE  PROCEDURE clean_partiton(SCHEMANAME VARCHAR(64), TABLENAME VARCHAR(64),reserve INT)


BEGIN


      -- 注:该储存过程适用于分区字段类型为datetime,按天分区且命名为p20180301格式规范的分区表


--     获取最旧一个分区,判断是否为reserve天前分区,是则进行删除,每次只删除一个分区


--     提前创建14天分区,判断命名不重复则创建


--     创建 history_partition 表,varchar(200)和datetime类型。记录执行成功的SQL语句


        DECLARE PARTITION_NAMES VARCHAR(16);    


        DECLARE OLD_PARTITION_NAMES VARCHAR(16); 


        DECLARE LESS_THAN_TIMES varchar(16);     


        DECLARE CUR_TIME INT;                  


        DECLARE RETROWS INT;


        DECLARE DROP_PARTITION VARCHAR(16);


SET CUR_TIME = DATE_FORMAT(NOW(),'%Y%m%d');


        BEGIN


        SELECT PARTITION_NAME INTO DROP_PARTITION FROM information_schema.partitions WHERE table_schema = SCHEMANAME AND table_name = TABLENAME order by PARTITION_ORDINAL_POSITION  asc limit 1 ;


        IF SUBSTRING(DROP_PARTITION,2) < DATE_FORMAT(CUR_TIME - INTERVAL reserve DAY, '%Y%m%d') THEN


SET @sql = CONCAT( 'ALTER TABLE ', SCHEMANAME, '.', TABLENAME, ' drop PARTITION ', DROP_PARTITION, ';' );


        PREPARE STMT FROM @sql;


        EXECUTE STMT;


        DEALLOCATE PREPARE STMT;


        INSERT INTO history_partition VALUES (@sql, now());


END IF;


        end;


        


        SET @__interval = 1;


        create_loop: LOOP


                IF @__interval > 15 THEN


                        LEAVE create_loop;


                END IF;


                SET LESS_THAN_TIMES = DATE_FORMAT(CUR_TIME + INTERVAL @__interval DAY, '%Y%m%d');


                SET PARTITION_NAMES = DATE_FORMAT(CUR_TIME + INTERVAL @__interval -1 DAY, 'p%Y%m%d');


                IF(PARTITION_NAMES != OLD_PARTITION_NAMES) THEN


                     SELECT COUNT(1) INTO RETROWS FROM information_schema.partitions WHERE table_schema = SCHEMANAME AND table_name = TABLENAME AND LESS_THAN_TIMES <= substring(partition_description,2,8) ;


                     IF  RETROWS = 0 THEN


                     SET @sql = CONCAT( 'ALTER TABLE ', SCHEMANAME, '.', TABLENAME, ' ADD PARTITION (PARTITION ', PARTITION_NAMES, ' VALUES LESS THAN ( "',LESS_THAN_TIMES, '" ));' );


                     PREPARE STMT FROM @sql;


                     EXECUTE STMT;


                     DEALLOCATE PREPARE STMT;


INSERT INTO history_partition VALUES (@sql, now());


                     END IF;


                     


                END IF;


                SET @__interval=@__interval+1;


                SET OLD_PARTITION_NAMES = PARTITION_NAMES;


        END LOOP;


END


$$


delimiter ;



Step 5:创建名称为clean_perf_biz_vm的事件,并在每天凌晨00:30:00的时候调用clean_partition存储过程创建下一个新分区,并删除两天前的旧分区。




delimiter |


CREATE DEFINER=’root’@’localhost’ event clean_perf_biz_vm on schedule every 1 day starts DATE_ADD(DATE_ADD(CURDATE(),INTERVAL 1 DAY),INTERVAL 30 MINUTE)


ON COMPLETION PRESERVE


do


begin


call clean_partition(‘monitor_alarm’,’perf_biz_vm’,’2’);


end |


delimiter;





来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31429259/viewspace-2218330/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2017-01-16

  • 博文量
    103
  • 访问量
    48096