mysql执行ddl_MySQL存储过程中执行DDL

一、定期增加表分区

1、增加表分区例

CREATE DEFINER&＃61;&＃96;root&＃96;&＃64;&＃96;127.0.0.1&＃96; PROCEDURE &＃96;p_create_Partition&＃96;(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&＃61;CONCAT(‘ALTER TABLE ‘,databaseName,‘.‘,tableName,‘ REORGANIZE PARTITION pmax INTO ( PARTITION ‘,D1,‘ VALUES LESS THAN (‘,D2,‘),‘

‘PARTITION pmax VALUES LESS THAN (MAXVALUE))‘);

set &＃64;V_SQL&＃61;V_SQL;

PREPARE stmt FROM &＃64;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&＃61;/u01/dba_scripts/mysql_add_partition

exec >> $logdir/all_out.log 2>&1

startdate&＃61;&＃96;date &＃43;%Y-%m-%d\ %H:%M:%S&＃96;

echo "Start mysql_add_partition,$startdate"

user&＃61;root

password&＃61;xxxxxxxxxx

host&＃61;127.0.0.1

t&＃61;&＃96;mysql -u"$user" -p"$password" -h"$host" -e"select tab_name from dbadmin.part_tables"|tail -n &＃43;2&＃96;

for i in $t

do

echo "exec table $i:"

j&＃61;&＃96;mysql -u"$user" -p"$password" -h"$host" -e"select db from dbadmin.part_tables where tab_name&＃61;‘$i‘"|tail -n &＃43;2&＃96;

mysql -u"$user" -p"$password" -h"$host"  -e "call dbadmin.p_create_Partition(‘$j‘,‘$i‘)" #>> "$logdir"/mylog.log 2>&1

done

enddate&＃61;&＃96;date &＃43;%Y-%m-%d\ %H:%M:%S&＃96;

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 &＃61; concat(‘p‘, date_format( DATE_SUB(CURDATE(), INTERVAL 6 day),‘%Y%m%d‘));

set pstring_drop_part &＃61;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&＃61;/u01/dba_scripts/mysql_drop_partitionexec >> $logdir/drop_part_out.log 2>&1startdate&＃61;&＃96;date &＃43;%Y-%m-%d\ %H:%M:%S&＃96;echo "$startdate,Start mysql_drop_partition"user&＃61;rootpassword&＃61;xxxxxxxxxhost&＃61;127.0.0.1mysql -u"$user" -p"$password" -h"$host"  -e "call dbadmin.tab_drop_partition()"enddate&＃61;&＃96;date &＃43;%Y-%m-%d\ %H:%M:%S&＃96;echo "$enddate,End mysql_drop_partition"echo ""