mysql 存储过程执行ddl_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 ""