通过shell脚本自动增加mysql分区表的分区
(2010-12-09 16:18)
分类： 关于数据库
- #!/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}
