How do I convert a database from MyISAM to InnoDB?

Question

I am going to convert all the tables of 500MB database from MyISAM to InnoDB to see whether it will improve the overall performance of a busy Drupal 6 site. I am wondering what is the best (i.e. safest/easiest/fastest) way to do the conversion.

Answer 1

As a MySQL DBA, I trust MySQL to do the conversion by having MySQL write the script for me.

Form the Linux command run this query

mysql -h... -u... -p... -A --skip-column-names -e"SELECT CONCAT('ALTER TABLE ',db,'.',tb,' ENGINE=InnoDB;') FROM (SELECT A.db,A.tb,A.tbsize FROM (SELECT table_schema db,table_name tb,(data_length+index_length) tbsize FROM information_schema.tables WHERE engine='MyISAM' AND table_schema NOT IN ('information_schema','mysql')) A LEFT JOIN (SELECT table_schema db,table_name tb FROM information_schema.statistics WHERE index_type='FULLTEXT') B USING (db,tb) WHERE B.db IS NULL) AA ORDER BY tbsize" > /root/ConvertMyISAM2InnoDB.sql

The script will convert the smallest tables first. This script was also bypass any MyISAM tables that have FULLTEXT indexes.

Ater looking over the script, you can simply run it in MySQL as follows:

mysql -h... -u... -p... -A < /root/ConvertMyISAM2InnoDB.sql

or if you want to see the timing of each conversion, login to mysql and run this:

mysql> source /root/ConvertMyISAM2InnoDB.sql

This should not get messed up because a full table lock happens when the conversion is being executed.

Once all tables are converted you need to tune the MySQL settings for InnoDB usage and scale down the key_buffer.

Please read this for setting the InnoDB Buffer Pool : https://dba.stackexchange.com/questions/1/what-are-the-main-differences-between-innodb-and-myisam/2194#2194

Please read this also : https://drupal.stackexchange.com/questions/1715/what-would-the-optimal-mysql-configuration-for-a-drupal-7-site-be/2367#2367

Give it a Try !!!

Answer 2

I've written a drush command for this a while ago.

<?php
/**
 * Implements hook_drush_command().
 */
function convert_drush_command() {
  $items = array();

  // the key in the $items array is the name of the command.
  $items['convert-engine'] = array(
    // a short description of your command
    'description' => "Convert MYSQL Table Type",
  );
  return $items;
}

function drush_convert_engine() {
  $args = func_get_args();
  $engine = $args[0];

  $result = db_query("SHOW TABLES");
  while ($row = db_fetch_array($result)) {
    $table = array_shift($row);
    drush_log(dt('Converting @table to @engine', array('@table' => $table, '@engine' => $engine)), 'success');
    db_query("ALTER TABLE $table ENGINE = $engine");
  }
}

Worked for me a year ago or so, not sure if the drush API changed since then.

You can place that in a convert.drush.inc for example in the .drush folder or execute it somehow on your site, for example with the devel execute php block. As a drush script, you can call it like this:

drush convert-engine InnoDB

Warning: If someone does something with the database while these commands run, your database will be messed up completely. Unrecoverably. So, put your site into maintenance mode and make a backup before trying this! And of course, try on a development/testing site first :)