Add Monthly Partition (MySQL 5.5=<)

add_monthly_partition.sh

#!/bin/sh

HOST='localhost'
USER='ID'
PASS='PASS'
DBNAME='DBNAME'
TABLENAME='TABLENAME'

PREVIOUSMONTH=`mysql -h $HOST -u $USER -p$PASS -e 'SHOW CREATE TABLE '$DBNAME'.'$TABLENAME'\G' | 
                tail -n 2 | 
                head -n 1 | 
                perl -wnl -e '/LESS\sTHAN\s\(\W+(\d{4}-\d{2}-\d{2})\W+\)/ and print $1;'`

THISMONTH=`date -d ''$PREVIOUSMONTH' 1 months' '+%Y-%m-%d'`
PARTITIONNAME=`echo $THISMONTH | perl -wnl -e '/(\d{4})-(\d{2})-(\d{2})/ and print "$1$2$3";'`

SQL="ALTER TABLE $DBNAME.$TABLENAME REORGANIZE PARTITION pmax INTO(
    PARTITION p$PARTITIONNAME VALUES LESS THAN ('$THISMONTH') ENGINE=InnoDB,
    PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB);"
mysql -h $HOST -u $USER -p$PASS -e "$SQL"

PARTITION p20131101 VALUES LESS THAN ('2013-11-01') ENGINE = InnoDB,
PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB

MySQLで、こんな感じで月毎にパーティショニングしている場合にパーテションを追加。
cronで月に一回動かせば良い。