Convert From MyISAM to InnoDB
Posted by mikeytown2
Run this code, you can do it in a code block, as it will only do it once. Be sure to remove code block once the database has been converted.
<?php
$tables = db_query('SHOW TABLE STATUS');
while ($table = db_fetch_array($tables)) {
if ($table['Engine'] == 'MyISAM') {
db_query('ALTER TABLE {%s} ENGINE = InnoDB', $table['Name']);
}
}
?>
In addition to adding "default-storage-engine = InnoDB" to your my.cnf file, you should also add "innodb_file_per_table". The default behavior of InnoDB is to store all database tables in a single ibdata file. Adding the line "innodb_file_per_table" causes InnoDB to store each new table in it's own file, somewhat similar to the way the MyISAM engine does.
Here's an interesting technique to recover space in the ibdata file after switch to InnoDB if you neglected to use the innodb_file_per_table option prior to converting to InnoDB. It won't work if there are foreign keys, but might be helpful to the typical user.
http://brian.moonspot.net/2008/08/22/shrinking-ibdata-files-after-innodb...