MySQL 中怎么定時添加刪除歷史分區(qū)，很多新手對此不是很清楚，為了幫助大家解決這個難題，下面小編將為大家詳細講解，有這方面需求的人可以來學習下，希望你能有所收獲。
創(chuàng)新互聯公司公司2013年成立，先為芒市等服務建站，芒市等地企業(yè)，進行企業(yè)商務咨詢服務。為芒市企業(yè)網站制作PC+手機+微官網三網同步一站式服務解決您的所有建站問題。
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.編寫存儲過程用于定期創(chuàng)建新的分區(qū)，并刪除幾天前舊的分區(qū)
代碼如下：
delimiter $$
CREATE PROCEDURE clean_partiton(SCHEMANAME VARCHAR(64), TABLENAME VARCHAR(64)，reserve INT)
BEGIN
-- 注：該儲存過程適用于分區(qū)字段類型為datetime，按天分區(qū)且命名為p20180301格式規(guī)范的分區(qū)表
-- 獲取最舊一個分區(qū)，判斷是否為reserve天前分區(qū)，是則進行刪除，每次只刪除一個分區(qū)
-- 提前創(chuàng)建14天分區(qū)，判斷命名不重復則創(chuàng)建
-- 創(chuàng)建 history_partition 表，varchar(200)和datetime類型。記錄執(zhí)行成功的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：創(chuàng)建名稱為clean_perf_biz_vm的事件，并在每天凌晨00:30:00的時候調用clean_partition存儲過程創(chuàng)建下一個新分區(qū)，并刪除兩天前的舊分區(qū)。
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；
看完上述內容是否對您有幫助呢？如果還想對相關知識有進一步的了解或閱讀更多相關文章，請關注創(chuàng)新互聯行業(yè)資訊頻道，感謝您對創(chuàng)新互聯的支持。
重慶分公司
重慶分公司
