Patrick Galbraith wrote in his blog about switching to innodb_file_per_table. For those that don’t know about this setting, it places the data for the tables into .ibd files within the database dir instead of storing it in the ibdata files in the main datadir. This is useful if you don’t want to babysit your innodb tablespace. At least, that was my main reason for wanting to use it. There is still dictionary data stored in the ibdata file(s) so you can’t just remove them.
Anyhow, the delima Patrick wrote about is recovering the space used by the ibdata files in the datadir after you have converted the tables to one file per table. I commented on his blog, but thought it worth a full post to be sure others could find my solution.
- Backup your data (cuz, you never know)
- Convert all tables to MyISAM
- Stop MySQL.
- Delete ib* in the datadir
- Restart MySQL. MySQL will recreate the files.
- Convert all tables to InnoDB
It worked for me. Your mileage may vary. No warranty that you won’t lose all your data. Try it on a dev server first.
The benefits of this were that the data was online while we converted the tables. The only downtime was while we shut down MySQL, removed the files and waited on MySQL to recreate a small 10MB ibdata file and the ib_logfiles. I am not 100% sure you have to remove the ib_logfiles, but I did for good measure. I just run with a single 10MB autoextend ibdata file. I think it is at 34MB or so on our main database server now.