How to easily convert a single database or all databases from MyISAM storage engine to InnoDB storage engine

Programming Technology

InnoDB is a more robust engine and a better database table type for modern applications.  Using InnoDB will increase your sites performance, speed, and they will have better chances to survive most database hiccups that can occur

The steps below will allow you to update “Individual” or “ALL” existing database installations using the MyISAM storage engine to the InnoDB storage engine.


You’ll need the following in order to convert your databases:

1. The current version of phpMyAdmin or an SSH client

2. A database version that supports full text indexes for InnoDB table types

I will show you three different processes to convert your database(s).

Processes Used Below:

1. Using phpMyAdmin to convert a single database from using MyISAM engine to InnoDB engine

2. Using phpMyAdmin to convert all databases from using MyISAM engine to InnoDB engine

3. “easiest” Using SSH to convert all databases from using MyISAM engine to InnoDB engine

Single Database Conversion From MyISAM to InnoDB using phpMyAdmin:

1. Backup Your Database “Just in case”

2. Launch phpMyAdmin. You must explicitly click on the name of your database in the top of the left column. There are sometimes multiple databases, so make sure you are clicking the correct database. “Do not click on the one labelled information_schema.”

3. Click the Query tab

4. In the query box, paste the following code replacing “your-db-name” with your actual DB name and press the submit query button, this will pull up a list of tables that need to alter to InnoDB.

> eries using the “Copy to clipboard” link in the “Query results operations” box below the results

*e nd tables, you will have to repeat steps 1-10 till step 4 produces 0 results other than the mysql database results as they will not be converted due to permissions.

6” :paragraph –>

7. Coltilar to the results produced using the above single database query output but have the database.table instead of just the table

8. Patefull output and copy all the lines STARTING with “ALTER TABLE” to your clipboard, they will look like the example below.

In this edoll your databases, the ALTER TABLE strings will look slightly different

ALTER TABmm;            

ALTER TABmmbsp;   


If in youces between some query’s, remove the spaces

ALTER TABLEennbsp;           


ALTER TABLEs — /wp:paragraph –>

Should lookagagraph –>

ALTER TABLEennbsp;           


ALTER TABLEs — /wp:paragraph –>

9. Again, C twp:paragraph –>

10. Paste tntnd press the “Go” button in the lower right shaded area, there will be a pause, depending on how many databases and tables are getting converted, it can take a few minutes to complete

To confirm all ccher than the mysql database, click eack of your database names in the left column, and all table types should now say InnoDB except for some tables in the mysql database.

user: root

Root Password: paparagraph –>

[root@server ~]phph –>

“You will need r your MySQL password, continue to step #3, if not, go to next step

On servers runnySored locally in a file that only the root user can access. You can open the file using an explorer client like WinSCP or by connecting to root via SSH like in step #1.

By running the otew the MySQL credentials

2. [root@serverf

That should outg hich will appear similar to the following:

[root@server1 ~p>->



< <

password=”passw /- wp:paragraph –>

Make a note of hauotations on your output

3. LOGIN TO MYSapaph –>

[root@server1 ~> >

Enter password:ou2″

4. at the comma <




5. Press enter edp:paragraph –>

This query willr this is just an example which will show output for all the databases and tables.

+————————————————/w wp:paragraph –>


| alter table COLLATIONS–!– wp:paragraph –>

| alter table COLUMN_PRIVILE <

| alter table ENGINES–!– wp:paragraph –>

| alter table EVENTS engine=INNgrgraph –>

| alter table COLLATION_CHARACTEgiwp:paragraph –>

|ng/wp:paragraph –>

| alter table FILES engine=INNOraraph –>

| alter table GLOBAL_STATUS engin:p:paragraph –>

| alter table GLOBAL_VARIp>->

| alter table KEY_COLU;

| alter table OPTIMIZE

| alter table PARAMETER!-

**NOTE** If you want to chanicall databases then execute the below query.


Press enter and all done if executed properly

I know this tutorial se ur skill level. The tutorial was written to help even in

If something was missed or you see an in accuracy, please let me know in the comments below. I wrote this tutorial while eput could have a mistype.

Written by: Joe Lovrek

Leave a Reply