Differences

This shows you the differences between two versions of the page.

Link to this comparison view

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>​