MySQL Command to Mass Change Specific DB from MyISAM to InnoDB and vice versa? I have an mu WordPress database and I want to change it from MyISAM to InnoDB to test its performance. 

Which MySQL command/commands will do it for all of the tables instead of doing manually one by one?

Is there anything that I should keep my eye on in this application?
There are two methods to mass convert all your MyISAM data into InnoDB

CONVERSION METHOD #1 : In-Place Conversion

You can run ALTER TABLE tblname ENGINE=InnoDB; against all MyISAM tables

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
echo "SET SQL_LOG_BIN = 0;" &gt; /tmp/Mass_Convert_MyISAM_To_InnoDB.sql
SQL="${SQL} SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;')"
SQL="${SQL} FROM information_schema.tables WHERE engine='MyISAM'"
SQL="${SQL} AND table_schema NOT IN"
SQL="${SQL} ('information_schema','mysql','performance_schema')"
mysql ${MYSQL_CONN} -AN-e"${SQL}" &gt; /tmp/Mass_Convert_MyISAM_To_InnoDB.sql
less /tmp/Mass_Convert_MyISAM_To_InnoDB.sql


If you are satisfied with the script's contents, then run it

mysql ${MYSQL_CONN} &lt; /tmp/Mass_Convert_MyISAM_To_InnoDB.sql


I wrote a similar script 3 years ago : When to switch from MyISAM to InnoDB?

CONVERSION METHOD #2 : Dump and Reload

Verace's answer is short and sweet.

Here is the way to do it

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
#
# Gather all Databases except MySQL-centric Databases
#
SQL="SET group_concat_max_len = 1048576;"
SQL="${SQL} SELECT GROUP_CONCAT(schema_name SEPARATOR ' ')"
SQL="${SQL} FROM information_schema.schemata WHERE schema_name"
SQL=${SQL} NOT IN ('information_schema','mysql','performance_schema')"
DBLIST=`mysql ${MYSQL_CONN} -ANe"${SQL}"`
#
# Dump the Schema Only, changing the Storage Engine on the Fly
#
DUMP_OPTS="--single-transaction --routines --triggers -d --add-drop-database -B ${DBLIST}"
mysqldump ${MYSQL_CONN} ${DUMP_OPTS} | sed 's/ENGINE=MyISAM/ENGINE=InnoDB/g' &gt; Schema.sql
#
# Dump the Data Only
#
DUMP_OPTS="-t -B ${DBLIST}"
mysqldump ${MYSQL_CONN} ${DUMP_OPTS} &gt; Data.sql


Then, you run the two scripts

mysql ${MYSQL_CONN} &lt; Schema.sql
mysql ${MYSQL_CONN} &lt; Data.sql


I wrote a similar answer last year : How to export mysql databases with different ENGINE?

NOTE : If you want to convert just one database, here is the in-place method

DB_TO_COVERT=mydata
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
echo "SET SQL_LOG_BIN = 0;" &gt; /tmp/Mass_Convert_MyISAM_To_InnoDB.sql
SQL="${SQL} SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;')"
SQL="${SQL} FROM information_schema.tables WHERE engine='MyISAM'"
SQL="${SQL} AND table_schema = '${DB_TO_COVERT}'"
mysql ${MYSQL_CONN} -AN-e"${SQL}" &gt; /tmp/Mass_Convert_MyISAM_To_InnoDB.sql
mysql ${MYSQL_CONN} &lt; /tmp/Mass_Convert_MyISAM_To_InnoDB.sql


GIVE IT A TRY !!!