• 脚本功能
• 脚本使用示例

脚本功能

定时按天删分区

脚本使用示例

定时任务

0 23 * * * sh /appdata/goldendb/auto_drop_part.sh  >> /appdata/goldendb/auto_drop_part.log

shell脚本

#! /usr/bin/bash
# created enmo-liujichao   LINUX
# auto drop partition

echo "current user: ${USER}"

if [[ -f ~/.profile ]]
then
. ~/.profile
fi

if [[ -f ~/.bash_profile ]]
then
. ~/.bash_profile
fi

if [ "${USER}" != "zxmanager" ]
then
   echo "ERROR: This script must be run as zxmanager"
   echo "current user: ${USER}"
   exit 98
fi

STTIME=`date "+%Y-%m-%d %H:%M:%S"`
echo $STTIME
echo "begin drop partition..."
mysql -u$dbproxy_user -p$dbproxy_password -h$ip_proxy -P$dbproxy_port -Ddbmt -e"call P_drop_partition('xxx','CHKCDR',6);"

存储过程

----------------------------------------
DDL:
CREATE DEFINER=`root`@`%` PROCEDURE `p_drop_partition`(P_SCHEMA in char(50),P_TABLE_NAME in  char(50),P_EXPIRE_DAY in int)
IS
v_name varchar(500);
nums int DEFAULT 0; 
clean_sql VARCHAR(500); 
tab_partition_name VARCHAR(100); 
i INT DEFAULT 0;
cursor drop_partitions is select CONCAT('ALTER TABLE ',P_SCHEMA,'.',P_TABLE_NAME,' drop PARTITION ',PARTITION_NAME,';'), PARTITION_NAME from INFORMATION_SCHEMA.PARTITIONS where TABLE_SCHEMA= P_SCHEMA and TABLE_NAME= P_TABLE_NAME and to_number(REGEXP_SUBSTR(PARTITION_NAME ,'[0-9]{8}$')) < = to_number(DATE_FORMAT(CURDATE() - INTERVAL P_EXPIRE_DAY DAY, '%Y%m%d')) order by PARTITION_NAME ;
BEGIN
SELECT COUNT(*)  FROM INFORMATION_SCHEMA.PARTITIONS  where TABLE_SCHEMA= P_SCHEMA and TABLE_NAME= P_TABLE_NAME and to_number(REGEXP_SUBSTR(PARTITION_NAME ,'[0-9]{8}$')) < = to_number(DATE_FORMAT(CURDATE() - INTERVAL P_EXPIRE_DAY DAY, '%Y%m%d')) INTO nums;
INSERT into dbmt.drop_table_partition_log VALUES (user(),P_SCHEMA,P_TABLE_NAME,tab_partition_name,now(),NULL);
SET @pre_sql = clean_sql;
PREPARE STMT1 FROM @pre_sql;
 EXECUTE STMT1;
 UPDATE dbmt.drop_table_partition_log set end_time=now() WHERE table_schema = P_SCHEMA AND table_name = P_TABLE_NAME AND partition_name = tab_partition_name;
 COMMIT;
 END WHILE; 
 DEALLOCATE PREPARE STMT1;
 CLOSE drop_partitions;
END