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 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.

 

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”

  1. Pingback: Mengubah Seluruh Tabel MyISAM ke InnoDB dan Sebaliknya
  2. 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

  3. 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

  4. Pingback: Projects Update: April 2nd 2017 – Wolfe’s Journal
  5. Pingback: Converting WordPress Database Tables from MyISAM to InnoDB with WP-CLI •
  6. 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!”

Leave a Reply

Your email address will not be published. Required fields are marked *


The reCAPTCHA verification period has expired. Please reload the page.

This site uses Akismet to reduce spam. Learn how your comment data is processed.