通过shell自动增加mysql分区表分区

最新推荐文章于 2025-04-13 10:17:13 发布

minitoy

最新推荐文章于 2025-04-13 10:17:13 发布

阅读量384

点赞数

分类专栏： mysql 文章标签： mysql shell date unix file table

mysql 专栏收录该内容

37 篇文章

订阅专栏

本文介绍了一个Shell脚本，用于自动为日志数据库表添加分区。该脚本通过读取配置文件中的表名和列名，计算下个月每天的日期，并据此创建相应的分区。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

#!/bin/sh
#
#
:<<BLOCK
######################################################################
SHELL_NAME：Logdb_Add_Partition.sh
  Functional Description：At the last month auto add the logdb table partition
Argument：

         $1 USER Mysql Account
         $2 PASS Mysql Account Pass
         $3 DB       Mysql Logdb
Version：V1.0
Creater : SongYunkui
            Colin_Song
Crete_time：2010/12/9
Modify：1. MODIFY BY
         2. ADD BY ____ ___/__/__ Add:_________
######################################################################
BLOCK

#######################################################################################
if [ $# -lt 3 ]; then
  echo "Please Input The Correct Args"
  echo "Usage Logdb_Add_Partition.sh <USER> <PASS> <DB>"
  exit -1
fi

USER=$1
PASS=$2
DB=$3

##config section begin
CONN_MYSQL="-u$USER -p$PASS -s"
MYSQL_HOME=/opt/modules/mysql
MYSQL_DIR=${MYSQL_HOME}/bin/mysql
SHELL_BASE=/opt/sbin/Logdb
LOG_DIR=${SHELL_BASE}/log
OPT_NAME=add_partition

MKDIR=`whereis -b mkdir|awk '{print $2}'`
TOUCH=`whereis -b touch|awk '{print $2}'`
DATE=`whereis -b date|awk '{print $2}'`
if [ ! -d ${SHELL_BASE} ]
then
${MKDIR} -p ${SHELL_BASE}
fi

if [ ! -d ${LOG_DIR} ]
then
${MKDIR} -p ${LOG_DIR}
fi

if [ ! -d ${INI_DIR} ]
then
${MKDIR} -p ${INI_DIR}
fi

LOG_FILE=${LOG_DIR}/${OPT_NAME}.log

#config section end

#working start
CURRENT_DATE=`${DATE} +'%Y-%m-%d'`
echo "${CURRENT_DATE} everything is ok, runing start" >> ${LOG_FILE}

#loop read the partition table and column
while read TAB_NAME COL_NAME
do

COUNTER=1
CURRENT_YEAR=`date +%Y`
#check the next month
NEXT_MONTH=`date -d next-month +%m`

#check the next month has many days
case ${NEXT_MONTH} in
         1|01|3|03|5|05|7|07|8|08|10|12)
                                       CURRENT_DAY=31
         ;;
         4|04|6|06|9|09|11)
                           CURRENT_DAY=30
         ;;
         2|02)
            if [ `expr ${CURRENT_YEAR} % 4` -eq 0 ]; then
                     if [ `expr ${CURRENT_YEAR} % 400` -eq 0 ]; then
                              CURRENT_DAY=29
                     elif [ `expr ${CURRENT_YEAR} % 100` -eq 0 ]; then
                              CURRENT_DAY=28
                     else
                              CURRENT_DAY=29
                     fi
            else
                     CURRENT_DAY=28
            fi
         ;;
esac

#work start add the every day partition
while [ ${COUNTER} -le ${CURRENT_DAY} ]

do

#calculate the current day's next {counter} day
PATNAME_DATE=`date -d "${COUNTER} days" +%Y%m%d`
COUNTER=`expr ${COUNTER} + 1`
PAT_DATE=`date -d "${COUNTER} days" +%Y%m%d`

#change the unix_timestamp
PAT_UNIX_TIMESTAMP=`${MYSQL_DIR} ${CONN_MYSQL} <<EOF
use ${DB};
select UNIX_TIMESTAMP('${PAT_DATE}');
EOF`

##add partition sql
V_SQL="ALTER TABLE ${DB}."${TAB_NAME}" ADD PARTITION (PARTITION P"${PATNAME_DATE}" VALUES LESS THAN ("${PAT_UNIX_TIMESTAMP}"));"
echo $V_SQL

#exec the sql
${MYSQL_DIR} ${CONN_MYSQL} <<EOF
use ${DB};
$V_SQL;
EOF

done
done<./Logdb_Partition.ini

#working end
END_DATE=`${DATE} +'%Y-%m-%d %H:%M:%S'`
echo "${END_DATE} runing finished" >> ${LOG_FILE}
echo -e "/n--------------------------------------------------------------------" >> ${LOG_FILE}