Shrinking a MySQL ibdata1 file
MySQL’s InnoDB engine stores all tables by default in one large file which is located (in Debian) under /var/lib/mysql/ibdata1. This file allocates some diskspace (in Debian it defaults to 10M chunks), but never unallocates the disk space. So if you make a few large tables, your partition might get full. Removing those large tables doesn’t help a thing, since the ibdata1 file is never shrunken by MySQL’s InnoDB engine. I needed to shrink it, preferably with as little as possible downtime.
It is nigh impossible. I’ve tried several things. First, I added the option innodb_file_per_table. The idea was to optimize each table, so it would be rewritten into a separate file. Then delete the ibdata1 file and it’s logs and restart MySQL. This worked in a simple test setup, but failed miserably in the production environment. I have no idea what went wrong, but even when I restored the old ibdata1 and it’s logs, it wouldn’t work anymore. Chaos and mayhem all around. Joy.
So I resorted to the only other solution I could find: Make a dump of the complete database and remove all files in /var/lib/mysql, then restart MySQL and restore the dump. Not a very practical solution, but the only one that seems to work reliably.
