1935.MYSQL分区表创建、自动备份、自动删除、自动创建

1935.MYSQL分区表创建、自动备份、自动删除、自动创建
由于程序大量写入数据库，导致mysql一个表数据量极大，ORACLE里面分区表用的非常好，因此建议MYSQL的表也进行分区表改造，并清理历史数据，经与应用讨论，分区改造要求如下：
1、按天分区；
2、每天删除最早的一个分区，删除前需要备份到历史表；
3、每天新增一个分区，新增的分区比最大的分区大一天，不限于第二天的分区，只要愿意，可以连续执行，连续创建多天分区。
一、创建分区表
创建分区表需要注意两点:
1、分区字段，必须在主键内，否则无法创建；
2、按照时间字段创建，类型应为datetime ，有时候我们习惯用varchar字段来存放时间，需要转换，函数分区需要查询支持函数，很多函数不支持在分区时使用，因此建议直接使用datetime类型。
CREATE TABLE daf_autoplan_log_tcpdump (
logid int NOT NULL AUTO_INCREMENT ,
daf_id varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
host_m_Ip varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
host_m_Port int NULL DEFAULT NULL ,
fteo_id int NULL DEFAULT NULL ,
host_m_Command varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
cmd_result longtext CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
runtime datetime NOT NULL ,
cmdresult varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
sen_sms int NULL DEFAULT 0 COMMENT ‘是否发送短信：0 未发送，1发送 2 发送失败’ ,
remark1 varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT ‘0’ COMMENT ‘是否被分析，0未被分析，1：已经被分析’ ,
remark2 varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT ‘预留字段’ ,
remark3 varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT ‘预留字段’ ,
PRIMARY KEY (logid,runtime),
INDEX logid (logid, daf_id, host_m_Ip, host_m_Port) USING BTREE
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
AUTO_INCREMENT=2381
ROW_FORMAT=DYNAMIC
PARTITION BY RANGE (TO_DAYS(runtime) ) (
PARTITION p20210905 VALUES LESS THAN ( TO_DAYS(‘20210906’) ),
PARTITION p20210906 VALUES LESS THAN ( TO_DAYS(‘20210907’) ),
PARTITION p20210907 VALUES LESS THAN ( TO_DAYS(‘20210908’) )
);

也支持直接在原表上进行分区改造，但是目前一般思路都是新建表比较好。分区根据需要，建的足够多，一旦自动调度开启，将删除最小的，增加一个最新分区，保持分区数量固定。
二、创建自动分区存储过程
存储过程名称：create_Partition_daf_autoplan_log_tcpdump，包括三个功能：
1、建分区；
2、备份最小分区数据到历史表；
3、删除最小分区。
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
START TRANSACTION;
/* 按照 runtime 字段创建分区，创建之前，取出最大分区名称*/
SELECT REPLACE(partition_name,‘p’,’’) INTO @Pmax FROM INFORMATION_SCHEMA.PARTITIONS
WHERE table_name=‘daf_autoplan_log’ ORDER BY partition_ordinal_position DESC LIMIT 1;
SET @Max_date= DATE(DATE_ADD(@P12_Name, INTERVAL 2 DAY))+0;
SET @Now_date= DATE(DATE_ADD(@P12_Name, INTERVAL 1 DAY))+0;
SET @s1=CONCAT(‘ALTER TABLE daf_autoplan_log ADD PARTITION (PARTITION p’,@Now_date,’ VALUES LESS THAN (TO_DAYS (’’’,DATE(@Max_date),’’’)))’);
SELECT @s1;
PREPARE stmt2 FROM @s1;
EXECUTE stmt2;
DEALLOCATE PREPARE stmt2;

select partition_name into @Pmin from INFORMATION_SCHEMA.PARTITIONS		
where table_name='daf_autoplan_log' order by partition_ordinal_position limit 1;

/备份最小分区数据到历史表/
SET @ins=CONCAT(‘INSERT INTO daf_autoplan_log_history select * from daf_autoplan_log PARTITION(’,@Pmin,’)’);
PREPARE stmt3 FROM @ins;
EXECUTE stmt3;
DEALLOCATE PREPARE stmt3;

/* 删除最小分区 /
SET @s=concat('ALTER TABLE daf_autoplan_log DROP PARTITION ',@Pmin);
PREPARE stmt1 FROM @s;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
/ 提交 */
COMMIT ;
END

三、创建event（类似oracle的job）
事件名称：Partition_daf_autoplan_log_event
CREATE DEFINER=root@%
EVENT NewEvent
ON SCHEDULE EVERY 1 DAY STARTS ‘2021-01-01 02:00:00’
ON COMPLETION NOT PRESERVE
ENABLE
DO
BEGIN

CALL lining.`create_Partition_daf_autoplan_log_tcpdump`;  

END;

用navicat创建更方便，且工具一目了然。
四、相关视图
（1）查询event定义
SELECT * FROM INFORMATION_SCHEMA.EVENTS ;
（2）查询调度器开启情况
SELECT @@event_scheduler;
（3）调度开关
ALTER EVENT Partition_daf_autoplan_log_event ENABLE;
ALTER EVENT Partition_daf_autoplan_log_event DISABLE;

分区表 按天自动分区
创建测试数据库
create database csl_test character set utf8 collate utf8_unicode_ci;
创建测试表
注：要分区的字段 需要为主键。
use csl_test; # 切换到刚刚创建的测试数据库

create table t_partition_test (
pk_id bigint(20) not null auto_increment,
time datetime not null,
msg varchar(200),
primary key (pk_id,time)
)engine=innodb default charset=utf8 collate utf8_unicode_ci comment=“test partition”;

插入测试数据
insert into t_partition_test(time,msg) values
(‘2021-02-01 13:34:23’,“20210201133423”),
(‘2021-02-02 14:23:13’,“20210202142313”);
手动分区
在自动分区前，需对表进行手动分区，这样自动分区才能进行。
分区字段应为主键包含字段，不可为索引。
可使用该命令修改主键字段
alter table t_partition_test drop primary key, add primary key (pk_id, time);
批量分区
alter table t_partition_test partition by range columns(time)(
partition p20210201 values less than(‘2021-02-02’),
partition p20210202 values less than(‘2021-02-03’),
partition p20210203 values less than(‘2021-02-04’)
);
单条分区
alter table t_partition_test add partition (partition p20210201 values less than (‘2021-02-02’) );
删除分区
由于数据是存放在分区中，所以删除分区 也会删除 对应分区的数据。
alter table t_partition_test drop partition p20210201;
查看表分区
select partition_name,partition_description as val from information_schema.partitions
where table_name = ‘t_partition_test’ and table_schema = ‘csl_test’;
创建增加删除分区的存储过程
– 定义mysql提交查询语句 结束标识 为 $$
delimiter $$
DROP PROCEDURE IF EXISTS p_partition_test # 创建过程 p_partition_test

CREATE PROCEDURE p_partition_test() BEGIN DECLARE v_sysdate date; # 声明 当前时间 DECLARE v_mindate date; # 声明 目前分区的最小值 DECLARE v_maxdate date; # 声明 目前分区值中的最大值 DECLARE v_pt varchar(20); # 声明 分区名称 数字部分 DECLARE v_maxval varchar(20); # 声明 最大值 DECLARE i int; # 声明 ?? -- 增加新分区 -- select into A from B 用法；A表作为临时表，不存在。 -- 不同于insert into A select from B，A必须存在。 SELECT max(cast(replace(partition_description, '''', '') AS date)) AS val INTO v_maxdate FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 't_partition_test' AND TABLE_SCHEMA = 'csl_test'; set v_sysdate = sysdate(); # 赋值v_sysdate为当前时间 -- INTERVAL 时间计算的关键字 WHILE v_maxdate <= (v_sysdate + INTERVAL 3 DAY) DO SET v_pt = date_format(v_maxdate ,'%Y%m%d'); SET v_maxval = date_format(v_maxdate + INTERVAL 1 DAY, '%Y-%m-%d'); SET @sql = concat('alter table t_partition_test add partition (partition p', v_pt, ' values less than(''', v_maxval, '''))'); -- SQL语句预处理，在对于反复调用相同或极度相似的语句，进行预处理会优化处理速度，我感觉这里的用法应该没有效果 PREPARE stmt FROM @sql; EXECUTE stmt; -- 删除预处理 DEALLOCATE PREPARE stmt; SET v_maxdate = v_maxdate + INTERVAL 1 DAY; # 最大值 加一操作 END WHILE; --删除旧分区 SELECT min(cast(replace(partition_description, '''', '') AS date)) AS val INTO v_mindate FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 't_partition_test' AND TABLE_SCHEMA = 'csl_test'; -- 删除一年前的旧分区 WHILE v_mindate <= (v_sysdate - INTERVAL 1 YEAR) DO SET v_pt = date_format(v_mindate - INTERVAL 1 DAY,'%Y%m%d'); SET @sql = concat('alter table t_partition_test drop partition p', v_pt); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET v_mindate = v_mindate + INTERVAL 1 DAY; END WHILE; END

– 重新定义 MySQL提交查询语句的 结束标识 为 ;
delimiter ;
手动调用存储过程
call p_partition_test; # call p_partition_test();
开启事件
mysql默认是关闭定时任务
查看定时任务是否开启，OFF是没有开启：
show variables like ‘%event_scheduler%’;
– 或者
select @@event_scheduler;
临时开启定时任务（重启之后，配置会回复）
set global event_scheduler = 1;
永久开启，修改配置文件 my.cnf，
event_scheduler=ON;
创建event事件
delimiter $$
drop event if exists auto_pt $$
create event auto_pt
on schedule
– every 1 minute
every 1 day
starts ‘2021-02-01 13:19:02’
do
BEGIN
call p_partition_test();
END$$
delimiter ;

建议不要用event, 配到os的crontab里面
用mysql自带调度就会遇到我之前说的问题，已经出现好几次由于切换后忘了在新主库上开调度导致新数据无法入库的情况了