How do I convert a database from MyISAM to InnoDB? 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.
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" &gt; /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 &lt; /root/ConvertMyISAM2InnoDB.sql


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

mysql&gt; 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 !!!