In previous part we put aside the issue of foreign keys. We address this issue now.

What if my InnoDB tables have foreign keys?

MyISAM does not support them, so you can't just ALTER an InnoDB table to MyISAM and back into InnoDB, and expect everything to work.

Alas, this calls for additional steps (i.e. additional ALTER commands). However, these still fall well under the concept of "do it one table at a time, then take time to recover your breath and replication lag".

Save , drop and restore your Foreign Keys setup

You can use common_schema's sql_foreign_keys to get the full listing and create definition of your foreign keys. For example, assume we use the sakila database:

SELECT create_statement FROM common_schema.sql_foreign_keys WHERE TABLE_SCHEMA='sakila' INTO OUTFILE '/somewhere/safe/create_foreign_keys.sql'

(replace TABLE_SCHEMA='sakila' with whatever you want).

A sample output would be something like this (note: no semicolon on end of line):

Think it's an interesting approach that you've taken to resolve the ibdata file size, but I still the risks and overheads (investigation to see what might break) to achieve it are too high in a real-world environment. More so if the data is that of a financial nature. The safest is to do dump loads, or we can all just keep begging the MySQL developers to make a vacuum function to do background thread cleanup of ibdata files. This size issue of ibdata and idb files and the locking when doing optimizing is honestly one of the biggest flaws in MySQL, if they can fix this issue then MysQL could and would be so much more powerful than it is right now.