Shulou(Shulou.com)05/31 Report--

Mysql how to regularly add and delete historical zones, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can gain something.

1. New table

CREATE TABLE `perf_biz_vm_ New` (

`TIME`datetime NOT NULL COMMENT 'performance acquisition time' CREATE_

`VM_ ID`varchar (80) NOT NULL COMMENT 'virtual machine ID'

`USED` varchar (100) DEFAULT NULL COMMENT 'CPU utilization (%)'

`USED` varchar (100) DEFAULT NULL COMMENT 'memory utilization (%)'

`UTILITY`varchar (100) DEFAULT NULL COMMENT 'amount of available memory (bytes)'

`In` varchar (100) DEFAULT NULL COMMENT 'inflow flow rate (Mbps)'

`OUT`varchar (100) DEFAULT NULL COMMENT 'outflow rate (Mbps)'

`Run` varchar (100) DEFAULT NULL COMMENT 'number of processes in the CPU run queue'

`WRITE_ IO`varchar (100) DEFAULT NULL COMMENT 'Virtual disk write rate (Mb/s)'

`READ_ IO`varchar (100) DEFAULT NULL COMMENT 'virtual disk read rate (Mb/s)'

`PID`varchar (36) NOT NULL

PRIMARY KEY (`PID`, `CREATE_ TIME`)

KEY `mytable_ Secretyid` (`CREATE_ TIME`) USING BTREE

KEY `perf_biz_vm_vm_id_create_ time` (`VM_ ID`, `CREATE_ time`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=' virtual machine performance acquisition table'

/ * 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) * /

two。 Change the table name

Rename table perf_biz_vm to perf_biz_vm_old

Rename table perf_biz_vm_new to perf_biz_vm

3. Insert the data from the last 2 days into the new table.

#! / bin/bash

Function insert () {

End_time= "$1 $2"

Start_time= "$3 $4"

Mysql-upright username-pendant passwd''$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. Write stored procedures to periodically create new partitions and delete old partitions from a few days ago

The code is as follows:

Delimiter $$

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

BEGIN

Note: this storage process is applicable to partition tables whose partition field type is datetime, partitioned by day and named p20180301 format specification.

-- get the oldest partition to determine whether it is a reserve partition. If so, delete it. Delete one partition at a time.

-- create a partition 14 days in advance. If the name is not repeated, it will be created.

-- create history_partition tables, varchar and datetime types. Record the successful execution of SQL statements

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.