Convert All Tables in a Database from MyISAM to InnoDB with phpMyAdmin

This is a quick and dirty guide to quickly convert all of your MyISAM tables to the more modern Database Engine InnoDB within phpMyAdmin using a couple queries. This is the fastest method I’ve honed down after converting database tables on hundreds of websites.

The instructions and screenshots might feel a little long the first time, but once you’ve gotten through it once, the process is a breeze of some quick copy and pastes.

Load up phpMyAdmin, either from WHM or your cPanel account and the screen will look something like this:

Click on SQL and enter this code:

SELECT CONCAT('ALTER TABLE ',TABLE_NAME,' ENGINE=InnoDB;') 
FROM INFORMATION_SCHEMA.TABLES
WHERE ENGINE='MyISAM'
AND table_schema = 'your-db-name';

Change your-db-name to your actual database name and hit Go. Here’s what your SQL should look like:

This will get you a list of every table in your database using MyISAM.

However, a lot of these tables will be cut off and you need the entire SQL query to run successfully.

1. Click the “Extra Options” link above the results, choose the “Full texts” option, and press the “Go” button in the shaded area at the lower right
2. Above the results, tick the “Show all” checkbox
3. Copy all the queries using the “Copy to clipboard” link in the “Query results operations” box below the results
4. Paste the result into a text editor and copy all the lines starting with “ALTER TABLE” to your clipboard

The screen with Full Texts and Show All turned on:

Click the Copy to Clipboard link and then navigate back to the SQL tab.

Paste in your results. Delete the last empty line, then all lines from CONCAT and above. The highlighted text all needs to be removed from the SQL paste, so you only have lists of ALTER TABLE commands. Click Go.

When the query is successful, your screen will look something like this as the result.

That’s it! Every table in your database is now converted.

I use the tool MySQLTuner to quickly locate any databases left on MyISAM. A successful run after conversion will show me something like this:

-------- MyISAM Metrics ----------------------------------------------------------------------------
[--] General MyIsam metrics:
[--]  +-- Total MyISAM Tables  : 0
[--]  +-- Total MyISAM indexes : 0B
[--]  +-- KB Size :16.0M
[--]  +-- KB Used Size :2.9M
[--]  +-- KB used :18.3%
[--]  +-- Read KB hit rate: 99.7% (31K cached / 105 reads)
[--]  +-- Write KB hit rate: 95.5% (440 cached / 420 writes)
[--] No MyISAM table(s) detected ....