Compress MySQL Tables

compress-tables.sh

#!/usr/bin/env bash

# Compress MySQL tables on disk.
# Author: Andrew Berry, andrew.berry@lullabot.com
#
# Compress all tables in a MySQL InnoDB database using compression from the
# Barracuda table format. Tables have to already be in the Barracuda file
# format to actually compress tables, otherwise the table setting is ignored.
#
# innodb_file_per_table = 1 MUST be set in my.cnf for compression to work.
#
# Consider setting the default file format in my.cnf:
#
#   innodb_file_format = "Barracuda"
#
# If you wish to compress existing tables that are stored in the previous
# "Antelope" format, you will need to dump and re-import them to convert the
# table format.

# Help about this script.
usage() {
  cat <<EOD
Usage: compress-tables <database> [ --test ] [ --decompress | ROW_FORMAT ]

Use this script to compress or decompress MySQL InnoDB tables. This script only
works with MySQL 5.5 or higher. It may work with MySQL derivatives that
implement the Barracuda table format.

EOD
}

# Set up our parameters.
parameters() {
  DATABASE=$1

  if [[ $2 == '--test' ]]
  then
    DEBUG=1
    echo "Test mode enabled. No tables will be modified."
    FORMAT_ARG=$3
  else
    FORMAT_ARG=$2
  fi

  if [[ -z $FORMAT_ARG ]]
  then
    FORMAT='Compressed'
  elif [ $FORMAT_ARG == '--decompress' ]
  then
    FORMAT='Dynamic'
  else
    FORMAT=$FORMAT_ARG
  fi
}

# Store the root password so we only have to prompt once.
mysql_root_password() {
  echo -n "Please enter your root mysql password: "
  read -s PASS
  echo ""
}

# Fetch all of the tables for the given database.
find_tables() {
  TABLES=`mysql --batch --skip-column-names -u root --password=$PASS -e 'SHOW TABLES;' $1`

  if [ $FORMAT == 'Compressed' ]
  then
    for TABLE in $TABLES
    do
      CURRENT_FORMAT=`mysql --batch --skip-column-names -u root --password=$PASS -e "SELECT ROW_FORMAT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='$DATABASE' AND TABLE_NAME='$TABLE';"`
      if [ $CURRENT_FORMAT != 'Compressed' -a $CURRENT_FORMAT != 'Dynamic' ]
      then
        ANACONDA_TABLES="$ANACONDA_TABLES $TABLE"

        cat <<EOD
  $TABLE does not appear to be stored in the Barracuda table format. The table
  format is $CURRENT_FORMAT. You might need to export and re-import the table for
  it to be compressed.

EOD
      fi
    done
  fi
}

# Build out our ALTER statements and execute them, unless $1 is set.
alter_tables() {
  for TABLE in $TABLES
  do
    STATEMENT="ALTER TABLE $TABLE ROW_FORMAT=$FORMAT;"
    if [ $1 ]
    then
      echo $STATEMENT
    fi

    ALTER_STATEMENTS="$ALTER_STATEMENTS $STATEMENT"
  done

  if [[ -z $1 ]]
  then
    echo "Altering tables. This may take some time depending on the size of your database."

    # We use -vvv so we can see the progress of each statement.
    echo $ALTER_STATEMENTS | mysql -vvv -u root --password=$PASS $DATABASE
  fi

}

# Dump and restore all tables in the database.
reimport_tables() {
  CONVERT_TABLES=$*
  SQL=`mktemp -t compress-tables`
  mysqldump -u root --password=$PASS --add-drop-table $DATABASE $CONVERT_TABLES > $SQL
  mysql -u root --password=$PASS -e "SET GLOBAL innodb_file_format='Barracuda'"
  mysql -u root --password=$PASS $DATABASE < $SQL
  mysql -u root --password=$PASS -e "SET GLOBAL innodb_file_format='Antelope'"
  rm -f $SQL
}

if [ $# -lt 1 -o $1 == "--help" ]
then
  usage
  exit 1
fi

# Here is the start of the script.
parameters $*
mysql_root_password
find_tables $DATABASE
alter_tables $DEBUG
if [[ ( $FORMAT == 'Compressed') && ( -z $DEBUG ) && ( ! -z "$ANACONDA_TABLES" ) ]]
then
  echo "Tables were found in the Antelope format. Would you like to reimport them? (yes / no)"
  read CONTINUE
  echo ""
  if [[ $CONTINUE == "yes" ]]
  then
    reimport_tables $ANACONDA_TABLES
    echo "Import complete."
  fi
fi