Bobby's Blog

Changing your MySQL storage engine – MyISAM and InnoDB

mysql-storage-engine-bobby-iliev

I’ve been looking into many ways of converting my database tables from MyISAM to InnoDB and the other way around and I can say that this is the best/easiest one that I’ve found.

Here are the queries that I would usually use. The queries won’t convert the storage engine! They will basically create a list of the actual queries you’ll need to perform to actually execute the change, that way you can review the queries before actually changing the engine.

To actually convert the engine, just cut-paste the resulting row and execute them (each row is a single working query).

To convert all MyISAM Tables to InnoDB for the specified database (change database_name_here to your actual database name) – the most likely scenario!

To convert all InnoDB Tables to MyISAM for the specified database (change database_name_here to your actual database name)

To convert all InnoDB Tables to MyISAM for all databases

To convert all MyISAM Tables to InnoDB for all databases

Once again, please note that these queries will build a list of queries you will have to execute individually to actually perform the conversion.

Here’s an example. The output that you would get would look something like this:

To do the actual conversion just run the ALTER TABLE .. queries.

This is pretty much it.


About the author

Bobby

Linux System Administrator