MySQL – Convert all Tables of one or more Database(s) from MyISAM to InnoDB and vice-versa

MySQL Master-Master Replication setup in 5 easy steps

Have you ever needed to convert one, some or all the tables of a MySQL Database from MyISAM to InnoDB or the other way around?

Here’s a couple queries you can use to achieve such results. As you will notice, they won’t execute the commands – they will basically create a list of the actual queries you’ll need to perform to actually execute the change, so you can review it just before it goes live. To execute the queries, just cut-paste the resulting row and execute them (each row is a single working query).

To convert all DBNAME’s InnoDB Tables to MyISAM

To convert all DBNAME’s MyISAM Tables to InnoDB

To convert all InnoDB Tables to MyISAM (all databases)

To convert all MyISAM Tables to InnoDB (all databases)

If you need to perform this task on a single Database, replace   with the name of your Database; if you need to do that on multiple Databases, add one or more additional OR conditions to the WHERE and let MySQL do the rest.

Once again, these commands will build a list of queries you will have to execute to actually perform the conversion, meaning that you’ll have to manually execute them afterwards.

That’s all for now: happy conversion!

EDIT: This logic can also be used to convert all the tables of one (or more) database(s) to a specific Collation (the series of rules used by MySQL to compare character within a given character set). for further info, read this post.



About Ryan

IT Project Manager, Web Interface Architect and Lead Developer for many high-traffic web sites & services hosted in Italy and Europe. Since 2010 it's also a lead designer for many App and games for Android, iOS and Windows Phone mobile devices for a number of italian companies.

View all posts by Ryan