#!/bin/sh DB_HOST=clown-adm01 DB_NAME=clown TABLENAME=$1 shift; if [ -z $TABLENAME ]; then echo "table name is needed." 1>&2 exit 1 fi NEXT=${1} if [ -z $TABLENAME ]; then NEXT=10 fi CURRENT="" QUERY="" # # 既存の最終パーティションを取得 # function getServerId { #echo $TABLENAME #CURRENT="" CURRENT=`mysql --defaults-extra-file=/tmp/.clownadmin.cnf -N -s << EOT SELECT PARTITION_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA = 'clown' AND TABLE_NAME = '${TABLENAME}' AND PARTITION_NAME != 'pmax' ORDER BY PARTITION_NAME DESC LIMIT 1; EOT` echo ${CURRENT/p/} } # # クエリ生成 # function createQuery { # # e.g. # PARTITION p20160501 VALUES LESS THAN ('2016-05-02') ENGINE = InnoDB, # # # N日先から既存の最終パーティションまでさかのぼってクエリ生成 # i=$NEXT j=`expr $i + 1` PT_NAME=`date --date $i' day' +"%Y%m%d"` # N日先 if [ $PT_NAME -le $CURRENT ]; then echo "$QUERY" return 0 fi while [ $PT_NAME -ne $CURRENT ] do THUMB=`date --date $j' day' +"%Y-%m-%d"` PTMAKE="PARTITION p${PT_NAME} VALUES LESS THAN ('$THUMB') ENGINE=InnoDB, $PTMAKE" i=`expr $i - 1` j=`expr $i + 1` PT_NAME=`date --date $i' day' +"%Y%m%d"` done if [ -n "$PTMAKE" ]; then QUERY="ALTER TABLE $TABLENAME REORGANIZE PARTITION pmax INTO ( $PTMAKE PARTITION pmax VALUES LESS THAN MAXVALUE );" fi echo "$QUERY" } # # メイン # # aggr_cleared_by_stage、aggr_payment_by_id、aggr_payment_by_os、aggr_total_by_day # aggr_use_by_type、aggr_uu、log_access、log_payment、log_use_item、log_user_action # log_user_gacha、log_user_rank、log_user_stage # and other... # echo "==========================================" echo "Get current max partition..." CURRENT=`getServerId $TABLENAME` #echo $CURRENT if [ -n "$CURRENT" ]; then echo "Create query..." QUERY=`createQuery $TABLENAME $CURRENT` echo "$QUERY" fi if [ -n "$QUERY" ]; then echo "A partition is added..." res=`mysql --defaults-extra-file=/tmp/.clownadmin.cnf -h "$DB_HOST" "$DB_NAME" -e "$QUERY" | sed -e 's/\t/,/g'` echo "$res" else echo "An added partition exists already..." fi echo "=========================================="