Compress MySQL Tables
#!/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