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.
Per Cederberg Says:
This method fails if any of the InnoDB tables uses foreign keys. From the MySQL 5.0 manual (13.2.6.4):
Also, if an InnoDB table has foreign key constraints, ALTER TABLE cannot be used to change the table to use another storage engine. To alter the storage engine, you must drop any foreign key constraints first.