Skip to content

Instantly share code, notes, and snippets.

@conmame
Created June 12, 2012 05:28
Show Gist options
  • Save conmame/2915331 to your computer and use it in GitHub Desktop.
Save conmame/2915331 to your computer and use it in GitHub Desktop.
Add Monthly Partition (MySQL 5.5=<)
#!/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"
@conmame
Copy link
Author

conmame commented Jun 12, 2012

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

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment