MySql - Convert MyISAM Tables to InnoDB
Exec this query to produce a list MyISAM Tables:
SELECT *
FROM information_schema.TABLES
WHERE engine='MyISAM';
Exec this query to produce a list of queries. Each of them will convert a single table related to <schema>.
SELECT CONCAT("ALTER TABLE <schema>.", TABLE_NAME, " ENGINE='InnoDB';")
FROM information_schema.TABLES
WHERE table_schema='<schema>'
AND engine='MyISAM';
Example Output where <schema> was 'cacti'
-------------------------------------------------------------------------
ALTER TABLE <schema>.plugin_realms ENGINE='InnoDB';
ALTER TABLE <schema>.host ENGINE='InnoDB';
ALTER TABLE <schema>.host_template_snmp_query ENGINE='InnoDB';
ALTER TABLE <schema>.data_local ENGINE='InnoDB';
ALTER TABLE <schema>.plugin_hooks ENGINE='InnoDB';
ALTER TABLE <schema>.cdef_items ENGINE='InnoDB';
ALTER TABLE <schema>.plugin_db_changes ENGINE='InnoDB';
ALTER TABLE <schema>.graph_tree_items ENGINE='InnoDB';
ALTER TABLE <schema>.snmp_query_graph_sv ENGINE='InnoDB';
ALTER TABLE <schema>.user_auth ENGINE='InnoDB';
ALTER TABLE <schema>.graph_templates ENGINE='InnoDB';
...
...
-------------------------------------------------------------------------
Then select all these result queries and and exec them as a single script.
Done!
All MyIsam tables are now converted to InnoDB Engine.
IMPORTANT
After conversion it would be better that all these tables ROW_FORMAT were Dynamic and not Fixed.
This conversion shuld be automatic in moving from MyISAM to InnoDB.
But if some table doesn't convert its row format it's suggested to manually convert it.
Exec this query to list <schema> tables using InnoDB Engine and having Fixed Row Format:
SELECT *
FROM information_schema.TABLES
WHERE table_schema='<schema>'
AND ENGINE='InnoDB'
AND ROW_FORMAT='Fixed';