0

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?

1

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;" > /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?

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' > 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

GIVE IT A TRY !!!

  • I tried the last one method and I got this msg: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DB_TO_COVERT=xxxxx MYSQL_USER=root MYSQL_PASS=xxxxx' at line 1. My SQL Version is 5.5.46-cll. – Alex Nov 21 '15 at 13:24
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.