Re: changing from 1 big file to file_per_table
Posted by:
Rick James
Date: November 16, 2011 09:54AM
1. switch to file_per_table on this machine
2. deal with the table that you copied to another machine and dropped from this machine.
Step 1 is, as described in the link. Note that it replaces ibdata1 with a small file, and creates .ibd files for all the InnoDB tables.
Step 2 is effectively loading (restoring) a table from somewhere else. It would be best to take a mysqldump of that one table on the other machine, then feed that to mysql, pointed at this machine. This will create a .ibd file for the table.
Do not attempt to copy .ibd tables around; it does not work.
ibdata1 is used for various admin things, so it may grow beyond its initial size. Still, it should not grow huge, as before.
Each .ibd file, like ibdata1, can only grow, never shrink. However, you have more control -- you can ALTER TABLE foo ENGINE=InnoDB to reconstruct the foo.ibd, thereby freeing up wasted space after, say, deleting half the rows. Caution: ALTER needs extra disk space to hold the new copy of foo before the old copy is removed.