InnoDB
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>
Conversion
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>