InnoDB

Reclaim Disk Space

If you have a couple of tables that tend to grow and shrink a lot, your 'ibdata1' file will grow and never shrink again, even if you drop all those tables or the whole database. The right solution for me was to make MySQL use separate tablespaces for all InnoDB tables, because those can be shrunk by running an OPTIMIZE on the table. If you set innodb_file_per_table before creating an InnoDB table, it will be created with its own tablespace. If you already have an existing InnoDB table and would like to convert it to its own tablespace, you need to set innodb_file_per_table=1 and then run the following for each InnoDB table:

ALTER TABLE myDatabase.myTable ENGINE=InnoDB;

Here's an quick command to generate this query for all your InnoDB tables:

/usr/bin/mysql -NB -e "SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', table_name, ' ENGINE=InnoDB;') FROM INFORMATION_SCHEMA.tables WHERE table_type='BASE TABLE' AND engine = 'InnoDB';"

It's probably a good idea to run each of the generated queries manually, just in case something unforeseen (disk space?) happens. The conversion process may take quite a while, depending on the amount of data, as it basically creates a copy of the table in the separate tablespace file. You better make sure you have at least the same amount of free disk space as the size of your tables.

After you've converted all your tables, you may notice that there's still the ibdata1 file, which is probably rather huge, as it contained all the data that's now in separate files. There is no easy way to reclaim the disk space it occupies, you can't just purge the file or truncate or optimize it, you will have to dump all tables that use InnoDB (really, absolutely all in your server), then drop them, shut down the MySQL daemon, move/delete 'ibdata1' and the log files, start the MySQL daemon again, and then import the dumps.

If you only remove the ibdata1 file without dumping and then importing the databases again, you MySQL daemon won't know anything about any InnoDB tables and just silently ignore them, that's why you must re-recreate them from the dumps.

Once you have done this, you will notice that the ibdata1 file still gets recreated (it's like an global index), and that it also may grow quite a bit - but don't worry, it will probably never become as huge as before, as all the data is now stored outside of it. The only way to keep its size in check is to run the whole dump/drop/delete/import process again, which can be a bit tedious, so...

I have created a script (below), which was inspired by Branko Toic's blog post, that aims to simplify the process and make it pretty much completely hands-off. You could probably even run it as a cronjob every night, but please test it on your system before leaving it unattended.

Required Configuration

/etc/mysql/my.cnf

[mysqld]
innodb_file_per_table=1
innodb_file_format=Barracuda

# my preferences, not required though
innodb_flush_log_at_trx_commit=0
innodb_support_xa=0
innodb_flush_method=O_DIRECT
innodb_buffer_pool_size=4G
group_concat_max_len=8192

Script

#!/bin/bash

if [ -z ${1} ]; then
  echo "Usage: ${0} </path/where/to/dump/sql/files>"
  exit 0;
fi

CWD="${1}"

/usr/bin/mysql -NB -e "SELECT DISTINCT(table_schema) FROM information_schema.TABLES where ENGINE = 'InnoDB'" > ${CWD}/innodb-databases.list
while read DB; do
  echo -n "dumping data from ${DB} to ${CWD}/${DB}.sql ... "
  /usr/bin/mysqldump ${DB} > ${CWD}/${DB}.sql;
  echo "done"
  echo -n "dropping database ${DB} ... "
  /usr/bin/mysqladmin -f drop ${DB}
  echo "done"
done < ${CWD}/innodb-databases.list

echo "stopping MySQL daemon"
/usr/sbin/service mysql stop

echo -n "backing up 'ibdata1', 'ib_logfile0' and 'ib_logfile1'  files ... "
mv -v /var/lib/mysql/ibdata1 /var/lib/mysql/bak.ibdata1
mv -v /var/lib/mysql/ib_logfile0 /var/lib/mysql/bak.ib_logfile0
mv -v /var/lib/mysql/ib_logfile1 /var/lib/mysql/bak.ib_logfile1
echo "done"

echo "starting MySQL daemon"
/usr/sbin/service mysql start

while read DB; do
  echo -n "creating database ${DB} ..."
  /usr/bin/mysqladmin create ${DB}
  echo "done"
  echo -n "importing data from ${CWD}/${DB}.sql into ${DB} ... "
  /usr/bin/mysql ${DB} < ${CWD}/${DB}.sql
  echo "done"
done < ${CWD}/innodb-databases.list
rm -v ${CWD}/innodb-databases.list

When the script is done and everything's working as expected, you can remove 'bak.ibdata1', 'bak.ib_logfile0', 'bak.ib_logfile1' and all the SQL dump files.