mysql 中怎样定时添加删除历史分区
发布时间：2022-02-11 14:24:24 所属栏目：搜索优化 来源：互联网
导读：mysql 中怎么定时添加删除历史分区，很多新手对此不是很清楚，为了帮助大家解决这个难题，下面小编将为大家详细讲解，有这方面需求的人可以来学习下，希望你能有所收获。 1. 新建表 CREATE TABLE `perf_biz_vm_new` ( `CREATE_TIME` datetime NOT NULL COMME
|
mysql 中怎么定时添加删除历史分区，很多新手对此不是很清楚，为了帮助大家解决这个难题，下面小编将为大家详细讲解，有这方面需求的人可以来学习下，希望你能有所收获。 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； 看完上述内容是否对您有帮助呢？如 （编辑：武汉站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
