パーティション追加スクリプト

#!/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 "=========================================="