MySQL5.5数据库自动分区剧本_数据恢复date
日期:2014-07-13 / 人气: / 来源:网络
一、使用说明:
1.此脚本为分区后,定时自动增加分区.(被自动分区的表,一定要先手动分几个区)
2.每隔15天,定时器会执行一个存储过程,对分区日期最后的那天再往后新增15个分区.3.Script里面Auto_partitions.sql 为存储过程
4.Script里面Timer_event.sql 为定时事件脚本
5.MySQL5.5默认并没有开启EVENT机制,需要在my.cnf文件中添加[mysqld] event_scheduler= ON
7.增加打开文件上线.这个很重要.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天执行一次.
ruby on rails程序中如何使用mysql数据库
导读:1、在系统里安装mysql:下载mysql软件,在百度上很多下载地址可以下载,或者进入mysql中文官方网站下载安装软件,我选择的是5.1.62版本的安装mysql,设置默认字符集为utf-8将mysql安装目录(默认C:/programfiles/m
MySQL教程,
作者:管理员
推荐内容 Recommended
- 江苏飞浩信息科技期待您的加入07-20
- 江苏飞浩科技欢迎您07-19
相关内容 Related
- 江苏飞浩信息科技期待您的加入07-20
- 江苏飞浩科技欢迎您07-19