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 ""