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
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;" > /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}" > /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} < /tmp/Mass_Convert_MyISAM_To_InnoDB.sql
I wrote a similar script 3 years ago : When to switch from MyISAM to InnoDB?
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' > Schema.sql
#
# Dump the Data Only
#
DUMP_OPTS="-t -B ${DBLIST}"
mysqldump ${MYSQL_CONN} ${DUMP_OPTS} > Data.sql
Then, you run the two scripts
mysql ${MYSQL_CONN} < Schema.sql
mysql ${MYSQL_CONN} < 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;" > /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}" > /tmp/Mass_Convert_MyISAM_To_InnoDB.sql
mysql ${MYSQL_CONN} < /tmp/Mass_Convert_MyISAM_To_InnoDB.sql
Do a mysqldump and then use your editor of choice to search for the word ENGINE - change MyISAM to InnoDB (for all tables in your database - and NOT the system tables) and reload your database. Your db will now run with all tables as InnoDB.
