1.修改MySQL存储过程
DELIMITER ;;
CREATE DEFINER=root
@%
PROCEDURE add_drop_partition
()
BEGIN
START TRANSACTION;
SET @s=CONCAT(‘alter table ‘,‘transactions’,’ add PARTITION ‘,’(’,‘partition ‘, CONCAT(‘p’,DATE_FORMAT(date_sub(now(),interval 1 week), ‘%Y%m%d’)), ’ VALUES IN ‘,’(’,TO_DAYS(DATE_FORMAT(date_sub(now(),interval 1 week), ‘%Y%m%d’)),’)’,’)’);
SELECT @s;
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
COMMIT;
END ;;
DELIMITER ;
mysql> show procedure status; #查询存储过程;
查看存储过程或函数的创建代码
show create procedure proc_name;
show create function func_name;
2.配置shell启动执行脚本
]# vim /etc/cron.d/auto_add_partition.sh
#!/bin/bash
#0 1 * * * sh /etc/cron.d/auto_add_partition.sh
host1=127.0.0.1
user=root
passwd=mypassword
port=3306
mysql -h
h
o
s
t
1
−
u
{host1} -u
host1−u{user} -p
p
a
s
s
w
d
−
P
{passwd} -P
passwd−P{port} -e “call gbh_center.add_drop_partition” >> /var/log/addpartion.log ;
3.给sh脚本增添执行权限
]# chmod +x /etc/cron.d/auto_add_partition.sh
4.Linux定时任务中增添定时任务
]# crontab -e
新增一条:(每天早上3点半执行该脚本)
30 3 * * * /etc/cron.d/auto_add_partition.sh
5.重启crontab 服务
]# systemctl restart crond (centos7)