Manage partitions on a table partition with RANGE on datetime column

mysql-partition-manager.sh

#!/bin/bash

MYCMD="mysql"
MAX_OLD=3
MIN_NEW=3
SCHEMA=sphinx
TABLE=search_query_log
LOGFILE=/tmp/search_query_log_partitions.log
DISKPATH=/sphinx/search_query_log_archive
ERRTO="[email protected]"

_die() {
    cat $LOGFILE \
       mail -s "search_query_log Partitions Script ERROR" $ERRTO
    exit 1
}

_say() {
    echo "$(date +%Y-%m-%d_%H_%M_%S) $1" | tee -a $LOGFILE
}

echo -n "" > $LOGFILE
_say "Starting partition maintenance on $SCHEMA.$TABLE"
_say "Max old number of partitions is: $MAX_OLD"
_say "Minimum number of new partitions is: $MIN_NEW"

SQL=$(cat <<EOF
select substring(partition_name, 3) ts
from partitions
where table_schema = '$SCHEMA' and table_name = '$TABLE'
   and partition_name like 'mo%'
EOF
)

PS=$($MYCMD information_schema -BNe "$SQL"|xargs)
if [ "$PS" == "" ]; then
   _say "Could not determine partitions from search_query_log!"
   _die
fi

CURRENT=$($MYCMD information_schema -BNe "select date_format(now(), '%Y%m')")
if [ "$CURRENT" == "" ]; then
   _say "Could not determine current from search_query_log!"
   _die
fi

_say "Existing partitions: $PS"
_say "Current partition: $CURRENT"

CNT_OLD=0
CNT_NEW=0
OLD_LST=/tmp/search_query_log_partitions.old

echo -n "" > $OLD_LST
LAST_PART=""

for p in $PS; do
   SQL=$(cat <<EOF
select partition_description
from partitions where table_schema = '$SCHEMA'
and table_name = '$TABLE'
and partition_name = 'mo$p'
EOF
)
   PDESC=$($MYCMD information_schema -BNe "$SQL")
   if [ "$PDESC" == "" ]; then
      _say "Could not determine partition description from search_query_log!"
      _die
   fi

   if [ $(($p)) -lt $(($CURRENT)) ]; then
      CNT_OLD=$(($CNT_OLD+1));
      echo "$p $PDESC" >> $OLD_LST
   elif [ $(($p)) -gt $(($CURRENT)) ]; then
      CNT_NEW=$(($CNT_NEW+1));
   fi

   LAST_PART="$p $PDESC"
done

ARCHIVED=0
TO_ARCHIVE=$(($CNT_OLD-$MAX_OLD))

if [ "$TO_ARCHIVE" -gt "0" ]; then
   _say "Archiving old partitions to disk"
   TS_PREV=0
   while read t; do
      set -- $(echo $t)
      PART=$1
      DESC=$2
      if [ "$TS_PREV" -eq "0" ]; then 
         WHERE="logged_at < FROM_UNIXTIME(($DESC-719528)*24*60*60)"; 
      else
         WHERE="logged_at BETWEEN FROM_UNIXTIME(($TS_PREV-719528)*24*60*60) AND FROM_UNIXTIME(($DESC-719528)*24*60*60)"
      fi
      TS_PREV=$DESC
      DUMP_CMD="mysqldump --databases $SCHEMA --tables $TABLE --where '$WHERE' --no-create-info | gzip - > $DISKPATH/$TABLE-mo$PART.sql.gz"
      _say "Dumping partition mo$PART with command:"
      _say "$DUMP_CMD"
      mysqldump --databases $SCHEMA --tables $TABLE --where "'$WHERE'" --no-create-info | gzip - > $DISKPATH/$TABLE-mo$PART.sql.gz
      if [ "$?" -ne "0" ]; then
         _say "Could not dump partition mo$PART, aborting!"
         _die
      fi
      ARCHIVED=$(($ARCHIVED+1))
      $MYCMD $SCHEMA -BNe "ALTER TABLE $TABLE DROP PARTITION mo$PART"
      if [ "$?" -ne "0" ]; then
         _say "Could not drop partition mo$PART, aborting!"
         _die
      fi
      if [ "$ARCHIVED" -ge "$TO_ARCHIVE" ]; then break; fi
   done < $OLD_LST
fi

TO_ADD=$(($MIN_NEW-$CNT_NEW))
ADDED=0
ALTER=""

if [ "$TO_ADD" -gt "0" ]; then
   _say "Adding $TO_ADD new partitions to table"
   set -- $(echo $LAST_PART)
   PART=$1
   DESC=$2
   for t in $(seq 1 $TO_ADD); do
      NEXT=$($MYCMD $SCHEMA -BNe "SELECT DATE_FORMAT(FROM_UNIXTIME(($DESC-719528)*24*60*60), '%Y%m'), TO_DAYS(FROM_UNIXTIME(($DESC-719528)*24*60*60) + INTERVAL 1 MONTH)")
      if [ "$NEXT" == "" ]; then
         _say "Could not compute next partition for search_query_log!"
         _die
      fi
      set -- $(echo $NEXT)  
      PART=$1
      DESC=$2
      ALTER="$ALTER PARTITION mo$PART VALUES LESS THAN ($DESC) ENGINE = MyISAM,"
      ADDED=$(($ADDED+1))
   done

   ALTER="ALTER TABLE $SCHEMA.$TABLE REORGANIZE PARTITION future INTO ( $ALTER PARTITION future VALUES LESS THAN MAXVALUE ENGINE = MyISAM )"
   _say "$ALTER"
   $MYCMD $SCHEMA -BNe "$ALTER"
   if [ "$?" -ne "0" ]; then
      _say "Could add new partitions, aborting!"
      _die
   fi
fi

_say "Old partitions: $CNT_OLD"
_say "New partitions: $CNT_NEW"
_say "Archived and dropped partitions: $ARCHIVED"
_say "Added new partitions: $ADDED"

cat $LOGFILE | mail -s "search_query_log Partitions Script Completed" $ERRTO