Split huge Innodb data file into several ones

Hi, I’m MySQL and I’m greedy, I WANNA ALL YOUR BYTES.

If you have a huge ibdata1 file (/var/lib/mysql/ibdata1) probably is because you don’t have innodb_file_per_table option in your my.cnf. To fix this follow this simple method (it requires to have free disk space):

1. Add innodb_file_per_table to your my.cnf and restart MySQL
2. Create list of innodb tables to migrate (all of them!)
   
SELECT `TABLE_SCHEMA`,`TABLE_NAME` FROM TABLES WHERE ENGINE = ‘InnoDB’;
3. For each table execute this:
   ALTER TABLE `table_name` ENGINE=InnoDB
   With that the table will be regenerated in its own file. But ibdata1 won’t shrink because MySQL is greedy and will keep that (empty) space for him
4. Free space deleting ibdata1
5. Profit