mysql 表分区脚本_通过shell脚本自动增加mysql分区表的分区-hero-

#!/bin/sh

#

#

:<

######################################################################

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 "

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

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

use ${DB};

$V_SQL;

EOF

done

done<.>

#working end

END_DATE=`${DATE} +'%Y-%m-%d %H:%M:%S'`

echo "${END_DATE} runing finished" >> ${LOG_FILE}

echo -e "\n--------------------------------------------------------------------" >> ${LOG_FILE}