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

1. #!/bin/sh
   
2. #
   
3. #
   
4. :<<BLOCK
   
5. ######################################################################
   
6. SHELL_NAME：Logdb_Add_Partition.sh
   
7.   Functional Description：At the last month auto add the logdb table partition
   
8.     Argument：
   
9. 
   
10.             $1 USER Mysql Account
    
11.             $2 PASS Mysql Account Pass
    
12.             $3 DB        Mysql Logdb
    
13.     Version：V1.0
    
14.     Creater : SongYunkui
15.               Colin_Song
    
16.     Crete_time：2010/12/9
    
17.     Modify：1. MODIFY BY
    
18.             2. ADD BY ____ ___/__/__ Add:_________
    
19. ######################################################################            
    
20. BLOCK
    
21. 
    
22. #######################################################################################
    
23. if [ $# -lt 3 ]; then
    
24.   echo "Please Input The Correct Args"
    
25.   echo "Usage Logdb_Add_Partition.sh "
    
26.   exit -1
    
27. fi
    
28. 
    
29. USER=$1
    
30. PASS=$2
    
31. DB=$3
    
32. 
    
33. ##config section begin
    
34. CONN_MYSQL="-u$USER -p$PASS -s"
    
35. MYSQL_HOME=/opt/modules/mysql
    
36. MYSQL_DIR=${MYSQL_HOME}/bin/mysql
    
37. SHELL_BASE=/opt/sbin/Logdb
    
38. LOG_DIR=${SHELL_BASE}/log
    
39. OPT_NAME=add_partition
    
40. 
    
41. MKDIR=`whereis -b mkdir|awk '{print $2}'`
    
42. TOUCH=`whereis -b touch|awk '{print $2}'`
    
43. DATE=`whereis -b date|awk '{print $2}'`
    
44. if [ ! -d ${SHELL_BASE} ]
    
45. then
    
46.     ${MKDIR} -p ${SHELL_BASE}
    
47. fi
    
48. 
    
49. if [ ! -d ${LOG_DIR} ]
    
50. then
    
51.     ${MKDIR} -p ${LOG_DIR}
    
52. fi
    
53. 
    
54. if [ ! -d ${INI_DIR} ]
    
55. then
    
56.     ${MKDIR} -p ${INI_DIR}
    
57. fi
    
58. 
    
59. LOG_FILE=${LOG_DIR}/${OPT_NAME}.log
    
60. 
    
61. #config section end
    
62. 
    
63. #working start
    
64. CURRENT_DATE=`${DATE} +'%Y-%m-%d'`
    
65. echo "${CURRENT_DATE} everything is ok, runing start" >> ${LOG_FILE}
    
66. 
    
67. #loop read the partition table and column
    
68. while read TAB_NAME COL_NAME
    
69. do
    
70. 
    
71. COUNTER=1
    
72. CURRENT_YEAR=`date +%Y`
    
73. #check the next month
    
74. NEXT_MONTH=`date -d next-month +%m`
    
75. 
    
76. #check the next month has many days
    
77. case ${NEXT_MONTH} in
    
78.             1|01|3|03|5|05|7|07|8|08|10|12)
    
79.                                            CURRENT_DAY=31
    
80.             ;;
    
81.             4|04|6|06|9|09|11)
    
82.                               CURRENT_DAY=30
    
83.             ;;
    
84.             2|02)
    
85.                 if [ `expr ${CURRENT_YEAR} % 4` -eq 0 ]; then
    
86.                         if [ `expr ${CURRENT_YEAR} % 400` -eq 0 ]; then
    
87.                                 CURRENT_DAY=29
    
88.                         elif [ `expr ${CURRENT_YEAR} % 100` -eq 0 ]; then
    
89.                                 CURRENT_DAY=28
    
90.                         else
    
91.                                 CURRENT_DAY=29
    
92.                         fi
    
93.                 else
    
94.                         CURRENT_DAY=28
    
95.                 fi
    
96.              ;;
    
97. esac
    
98. 
    
99. #work start add the every day partition
    
100. while [ ${COUNTER} -le ${CURRENT_DAY} ]
     
101. 
     
102. do
     
103. 
     
104. #calculate the current day's next {counter} day
     
105. PATNAME_DATE=`date -d "${COUNTER} days" +%Y%m%d`
     
106. COUNTER=`expr ${COUNTER} + 1`
     
107. PAT_DATE=`date -d "${COUNTER} days" +%Y%m%d`
     
108. 
     
109. #change the unix_timestamp
     
110. PAT_UNIX_TIMESTAMP=`${MYSQL_DIR} ${CONN_MYSQL} <<EOF
     
111. use ${DB};
     
112. select UNIX_TIMESTAMP('${PAT_DATE}');
     
113. EOF`
     
114. 
     
115. ##add partition sql
     
116. V_SQL="ALTER TABLE ${DB}."${TAB_NAME}" ADD PARTITION (PARTITION P"${PATNAME_DATE}" VALUES LESS THAN ("${PAT_UNIX_TIMESTAMP}"));"
     
117. echo $V_SQL
     
118. 
     
119. #exec the sql
     
120. ${MYSQL_DIR} ${CONN_MYSQL} <<EOF
     
121. use ${DB};
     
122. $V_SQL;
     
123. EOF
     
124. 
     
125. done
     
126. done<./Logdb_Partition.ini
     
127. 
     
128. #working end
     
129. END_DATE=`${DATE} +'%Y-%m-%d %H:%M:%S'`
     
130. echo "${END_DATE} runing finished" >> ${LOG_FILE}
     
131. echo -e "\n--------------------------------------------------------------------" >> ${LOG_FILE}