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

October 17, 2015January 28, 2018 - by Ryan - 8 Comments. 17.6K

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

SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME,' ENGINE=MyISAM;') 
FROM Information_schema.TABLES WHERE TABLE_SCHEMA = 'DBNAME' AND ENGINE = 'InnoDB' AND TABLE_TYPE = 'BASE TABLE'

1 2	SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME,' ENGINE=MyISAM;') FROM Information_schema.TABLES WHERE TABLE_SCHEMA = 'DBNAME' AND ENGINE = 'InnoDB' AND TABLE_TYPE = 'BASE TABLE'

To convert all DBNAME's MyISAM Tables to InnoDB

SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME,' ENGINE=InnoDB;') 
FROM Information_schema.TABLES WHERE TABLE_SCHEMA = 'DBNAME' AND ENGINE = 'MyISAM' AND TABLE_TYPE = 'BASE TABLE'

1 2	SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME,' ENGINE=InnoDB;') FROM Information_schema.TABLES WHERE TABLE_SCHEMA = 'DBNAME' AND ENGINE = 'MyISAM' AND TABLE_TYPE = 'BASE TABLE'

To convert all InnoDB Tables to MyISAM (all databases)

SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ' engine=MyISAM;') 
FROM information_schema.TABLES WHERE ENGINE = 'InnoDB';

1 2	SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ' engine=MyISAM;') FROM information_schema.TABLES WHERE ENGINE = 'InnoDB';

To convert all MyISAM Tables to InnoDB (all databases)

SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ' engine=InnoDB;') 
FROM information_schema.TABLES WHERE ENGINE = 'MyISAM';

1 2	SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ' engine=InnoDB;') FROM information_schema.TABLES WHERE ENGINE = 'MyISAM';

If you need to perform this task on a single Database, replace DBNAME 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.

Print Friendly & PDF Download

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. Microsoft MVP for Development Technologies since 2018.

View all posts by Ryan →

8 Comments on “MySQL - Convert all Tables of one or more Database(s) from MyISAM to InnoDB and vice-versa”

kopeboy says:

January 24, 2016 at 18:16

Tried pasting in Sequel Pro and not working.

Reply
1. Dark says:
  
  August 9, 2016 at 15:50
  
  Any info regarding the given error?
  
  Reply
Pingback: Mengubah Seluruh Tabel MyISAM ke InnoDB dan Sebaliknya
Dan Bolser says:

May 20, 2016 at 14:27

You may need to deal with foreign keys, e.g. like this:

mysql … DB_NAME -Ne ‘
SELECT
CONCAT(“ALTER TABLE “, TABLE_NAME, ” DROP FOREIGN KEY “,
CONSTRAINT_NAME, “;”)
FROM
information_schema.table_constraints
WHERE
table_schema = DATABASE()
AND
CONSTRAINT_TYPE = “FOREIGN KEY”
‘ > drop.fks

kthxbi

Reply
Oliver says:

March 19, 2017 at 01:45

To get a single string of all the ‘ALTER TABLE’ commands try, e.g.,:

SELECT

group_concat(

CONCAT(‘ALTER TABLE ‘, TABLE_SCHEMA, ‘.’, TABLE_NAME,’ ENGINE=InnoDB;’)

SEPARATOR ‘ ‘)

FROM Information_schema.TABLES WHERE TABLE_SCHEMA = ‘mydbname’ AND ENGINE = ‘MyISAM’ AND TABLE_TYPE = ‘BASE TABLE’
GROUP BY TABLE_SCHEMA

Note: ‘mydbname’ is usually case sensitive

Reply
Pingback: Projects Update: April 2nd 2017 – Wolfe’s Journal
Pingback: Converting WordPress Database Tables from MyISAM to InnoDB with WP-CLI •
Jan Steinman says:

July 18, 2019 at 08:34

Cool!

I had one other good use for this technique today. After a power failure, I had corrupted ib_logfile* files, and InnoDB would not start. So I deleted the ib_logfiles, then used your script-script to re-create them, by changing the WHERE to select “ENGINE = ‘InnoDB'” thus re-initializing them with the proper information.

Until I did this, the error log was full of stuff like: “[ERROR] InnoDB: Page [page id: space=143, page number=109] log sequence number 37337311781 is in the future!”

Reply