MySQL 存储过程中执行DDL

一、定期增加表分区

1、增加表分区例

CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `p_create_Partition`(IN databaseName VARCHAR(50),IN tableName VARCHAR(50))
L_END:BEGIN  
  DECLARE V_SQL VARCHAR(500);
  DECLARE D1 VARCHAR(20) DEFAULT 0;
  DECLARE D2 VARCHAR(20) DEFAULT 0;
  SELECT CONCAT('p',DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 2 day),'%Y%m%d')) INTO D1;
  SELECT CONCAT('''',DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 2 day),'%Y-%m-%d'),'''') INTO D2;
  SET V_SQL=CONCAT('ALTER TABLE ',databaseName,'.',tableName,' REORGANIZE PARTITION pmax INTO ( PARTITION ',D1,' VALUES LESS THAN (',D2,'),'
 'PARTITION pmax VALUES LESS THAN (MAXVALUE))');
   set @V_SQL=V_SQL;
    PREPARE stmt FROM @v_sql; 
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END

2、定时器

1)crontab

#################################
## mysql add partition
##       2018
#################################
30 6 * * * /u01/dba_scripts/mysql_add_partition/mysql_add_partition_tables.sh

2)脚本

cat /u01/dba_scripts/mysql_add_partition/mysql_add_partition_tables.sh

#!/bin/bash
##############################
##
##
##############################
logdir=/u01/dba_scripts/mysql_add_partition
exec >> $logdir/all_out.log 2>&1
startdate=`date +%Y-%m-%d\ %H:%M:%S`
echo "Start mysql_add_partition,$startdate"
user=root
password=xxxxxxxxxx
host=127.0.0.1
t=`mysql -u"$user" -p"$password" -h"$host" -e"select tab_name from dbadmin.part_tables"|tail -n +2`
for i in $t
  do
    echo "exec table $i:"
    j=`mysql -u"$user" -p"$password" -h"$host" -e"select db from dbadmin.part_tables where tab_name='$i'"|tail -n +2`
    mysql -u"$user" -p"$password" -h"$host"  -e "call dbadmin.p_create_Partition('$j','$i')" #>> "$logdir"/mylog.log 2>&1
done
enddate=`date +%Y-%m-%d\ %H:%M:%S`
echo "End mysql_add_partition,$enddate"
echo ""

 

二、定期删除表分区

1、删除表分区

create procedure dbadmin.tab_drop_partition()
begin
 DECLARE pstring_drop_part Varchar(1000);
 DECLARE part_name VARCHAR(20);
    set part_name = concat('p', date_format( DATE_SUB(CURDATE(), INTERVAL 6 day),'%Y%m%d'));
    set pstring_drop_part =concat('alter table ifpay_ccpay.spider_alive_report drop partition ',part_name);
    select pstring_drop_part;
    Execute Immediate pstring_drop_part;
end;

2、定时器

1)定时器

2)脚本

cat mysql_drop_partition_tables.sh
#!/bin/bash
##############################
## drop partition
## 2018/12/25
##############################
logdir=/u01/dba_scripts/mysql_drop_partition
exec >> $logdir/drop_part_out.log 2>&1
startdate=`date +%Y-%m-%d\ %H:%M:%S`
echo "$startdate,Start mysql_drop_partition"
user=root
password=xxxxxxxxx
host=127.0.0.1
mysql -u"$user" -p"$password" -h"$host"  -e "call dbadmin.tab_drop_partition()"
enddate=`date +%Y-%m-%d\ %H:%M:%S`
echo "$enddate,End mysql_drop_partition"
echo ""

 

转载于:https://www.cnblogs.com/xibuhaohao/p/10172985.html

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值