MySQL5.5 自动分区脚本

一、使用说明:1.此脚本为分区后,定时自动增加分区.（被自动分区的表,一定要先手动分几个区）2.每隔15天,定时器会执行一个存储过程,对分区日期最后的那天再往后新增15个分区.
3.Script里面Auto_partitions.sql 为存储过程4.Script里面Timer_event.sql 为定时事件脚本5.MySQL5.5默认并没有开启EVENT机制,需要在my.cnf文件中添加[mysqld] event_scheduler= ON7.增加打开文件上线.这个很重要.open_files_limit = 5000二、分区脚本

DELIMITER ||
DROP PROCEDURE IF EXISTS create_Partition ||
CREATE PROCEDURE create_Partition （IN databaseName VARCHAR（50）,IN tableName VARCHAR（50））
L_END:BEGIN
DECLARE MAX_PARTITION_DESCRIPTION VARCHAR（255） DEFAULT 0;
DECLARE P_NAME VARCHAR（255） DEFAULT 0;
DECLARE P_DESCRIPTION VARCHAR（255） DEFAULT 0;
DECLARE i INT DEFAULT 1;
DECLARE ISEXIST_PARTITION VARCHAR（255） DEFAULT 0;
SELECT PARTITION_NAME INTO ISEXIST_PARTITION FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA = databaseName AND TABLE_NAME = tableName LIMIT 1 ;
IF ISEXIST_PARTITION <=> "" THEN
SELECT "Partition table not is exist" AS "*****ERROR*****";
LEAVE L_END;
END IF;
SELECT partition_description INTO MAX_PARTITION_DESCRIPTION FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA = databaseName AND TABLE_NAME = tableName ORDER BY partition_description DESC LIMIT 1;
IF MAX_PARTITION_DESCRIPTION <=> "" THEN
SELECT "Partition table is error" AS "*****ERROR*****";
LEAVE L_END;
END IF;
SET MAX_PARTITION_DESCRIPTION = REPLACE（MAX_PARTITION_DESCRIPTION, """, ""）;
WHILE i <= 15 DO
SET P_DESCRIPTION = adddate（MAX_PARTITION_DESCRIPTION, INTERVAL i day）;
SET P_NAME = REPLACE（P_DESCRIPTION, "-", ""）;
SET @S=CONCAT（"ALTER TABLE ",tableName," ADD PARTITION （PARTITION p",P_NAME," VALUES LESS THAN （"",P_DESCRIPTION,""））"）;
SELECT @S;
PREPARE stmt2 FROM @S;
EXECUTE stmt2;
DEALLOCATE PREPARE stmt2;
SET i = i + 1 ;
END WHILE;
END L_END;||
DELIMITER ;

# 其中传入参数databaseName为数据库名,参数tableName为表名.三、添加事件处理

DELIMITER ||
CREATE EVENT auto_set_partitions
ON SCHEDULE
EVERY 15 DAY
DO
BEGIN
CALL create_Partition（"database_name","table_name"）;
/* 如果需要向多个表分区,可以写多个 CALL 调用
CALL create_Partition（"database_name","table_name"）;
*/
END ||
DELIMITER ;

这个事件每隔15天执行一次.MySQL备份恢复“故障”总结MySQL5.5 主从复制（触发器,函数,存储引擎,事件处理）说明相关资讯 MySQL脚本

MySQL 5.5.40自动化安装脚本（10/26/2014 13:04:24）
自动清理MySQL 的Lock 进程的脚本（09/05/2012 16:19:51）

MySQL中批量创建日志表信息脚本（10/10/2014 17:04:55）
监控MySQL进程的脚本，故障则重启（08/23/2012 09:43:37）

本文评论查看全部评论（0）