Find InnoDB tables

The table_schema table is the database name.

SELECT table_schema, table_name, engine FROM information_schema.tables WHERE engine = 'InnoDB' ORDER BY table_schema, table_name;

Use pt-find:

pt-find --noquote --engine InnoDB <database>


Update MyISAM tables to InnoDB:

pt-find --engine MyISAM --exec "ALTER TABLE %D.%N ENGINE=InnoDB" <database>

When changing MySQL configuration, the default log file size of 5 MB will have to be changed. Delete the old logs ib_logfile*, then restart the MySQL database with the new configuration.

After you're finished, run mysqlcheck on all the tables to verify their integrity.

Dump and Recreate

First, create a database dump that contains the schema only.

mysqldump --no-data -r schema.sql --databases <database> 

Create a database dump of the data only:

mysqldump --no-create-db --no-create-info -r data.sql --databases <database>