Differences
This shows you the differences between two versions of the page.
innodb [2013/01/10 14:59] |
innodb [2013/01/10 14:59] (current) |
||
---|---|---|---|
Line 1: | Line 1: | ||
+ | ====== InnoDB ====== | ||
+ | * [[MySQL]] | ||
+ | * [[InnoDB Foreign Keys]] | ||
+ | * [[Percona Toolkit]] | ||
+ | * [[pt-find]] | ||
+ | |||
+ | === Find InnoDB tables === | ||
+ | |||
+ | The ''table_schema'' table is the database name. | ||
+ | |||
+ | <code> | ||
+ | SELECT table_schema, table_name, engine FROM information_schema.tables WHERE engine = 'InnoDB' ORDER BY table_schema, table_name; | ||
+ | </code> | ||
+ | |||
+ | Use pt-find: | ||
+ | |||
+ | <code> | ||
+ | pt-find --noquote --engine InnoDB <database> | ||
+ | </code> | ||
+ | |||
+ | === Conversion === | ||
+ | |||
+ | Update MyISAM tables to InnoDB: | ||
+ | |||
+ | <code> | ||
+ | pt-find --engine MyISAM --exec "ALTER TABLE %D.%N ENGINE=InnoDB" <database> | ||
+ | </code> | ||
+ | |||
+ | 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. | ||
+ | |||
+ | <code> | ||
+ | mysqldump --no-data -r schema.sql --databases <database> | ||
+ | </code> | ||
+ | |||
+ | Create a database dump of the data only: | ||
+ | |||
+ | <code> | ||
+ | mysqldump --no-create-db --no-create-info -r data.sql --databases <database> | ||
+ | </code> |