Learn how to – Convert all MySQL tables from MyISAM into InnoDB Storage engine

Learn how to – Convert all MySQL tables from MyISAM into InnoDB Storage engine.

Do you have some tables in your MySQL database still using MyISAM and would like to convert them to use InnoDB Storage engine?. This guide has been written to walk you through the conversion of MyISAM into InnoDB Storage engine.

InnoDB has a strong focus on performance, support for transactions and reliability. It has been the default MySQL storage engine as of MySQL 5.5. You can read the MyISAM to InnoDB: Why and How to get a summary of why you should choose InnoDB.

First, before you can convert tables, you’ll need to get a list of tables which are using MyISAM. To do this, login to your MySQL/MariaDB from CLI and run below query.

$ mysql -u root -p

Then run:

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

Replace mydb with your actual database name. This will give you a list of tables in the database mydb using MyISAM and the queries you need to use for converting them into InnoDB.

You should get output similar to the one below.

+------------------------------------------------------+
 | CONCAT('ALTER TABLE ',TABLE_NAME,' ENGINE=InnoDB;')  |
 +------------------------------------------------------+
 | ALTER TABLE wp_ninja_forms_fav_fields ENGINE=InnoDB; |
 | ALTER TABLE wp_blc_synch ENGINE=InnoDB;              |
 | ALTER TABLE wp_nf_relationships ENGINE=InnoDB;       |
 | ALTER TABLE wp_top_ten_daily ENGINE=InnoDB;          |
 | ALTER TABLE wp_es_deliverreport ENGINE=InnoDB;       |
 | ALTER TABLE wp_usermeta ENGINE=InnoDB;               |
 | ALTER TABLE wp_comments ENGINE=InnoDB;               |
 | ALTER TABLE wp_term_relationships ENGINE=InnoDB;     |
 | ALTER TABLE wp_options ENGINE=InnoDB;                |
 | ALTER TABLE wp_terms ENGINE=InnoDB;                  |
 | ALTER TABLE wp_blc_links ENGINE=InnoDB;              |
 | ALTER TABLE wp_links ENGINE=InnoDB;                  |
 | ALTER TABLE wp_term_taxonomy ENGINE=InnoDB;          |
 | ALTER TABLE wp_postmeta ENGINE=InnoDB;               |
 | ALTER TABLE wp_nf_objects ENGINE=InnoDB;             |
 | ALTER TABLE wp_give_donormeta ENGINE=InnoDB;         |
 | ALTER TABLE wp_commentmeta ENGINE=InnoDB;            |
 | ALTER TABLE wp_es_sentdetails ENGINE=InnoDB;         |
 | ALTER TABLE wp_blc_instances ENGINE=InnoDB;          |
 | ALTER TABLE wp_snp_log ENGINE=InnoDB;                |
 | ALTER TABLE wp_posts ENGINE=InnoDB;                  |
 | ALTER TABLE wp_blc_filters ENGINE=InnoDB;            |
 | ALTER TABLE wp_slim_events_archive ENGINE=InnoDB;    |
 | ALTER TABLE wp_top_ten ENGINE=InnoDB;                |
 | ALTER TABLE wp_es_emaillist ENGINE=InnoDB;           |
 | ALTER TABLE wp_es_templatetable ENGINE=InnoDB;       |
 | ALTER TABLE wp_nf_objectmeta ENGINE=InnoDB;          |
 | ALTER TABLE wp_users ENGINE=InnoDB;                  |
 | ALTER TABLE wp_es_notification ENGINE=InnoDB;        |
 | ALTER TABLE wp_ninja_forms_fields ENGINE=InnoDB;     |
 | ALTER TABLE wp_give_donors ENGINE=InnoDB;            |
 | ALTER TABLE wp_termmeta ENGINE=InnoDB;               |
 | ALTER TABLE wp_snp_stats ENGINE=InnoDB;              |
 | ALTER TABLE wp_es_pluginconfig ENGINE=InnoDB;        |
 +------------------------------------------------------+
 34 rows in set (0.003 sec)

Convert MySQL tables from MyISAM into InnoDB Storage engine

All you need to do now is copy and run the command below in your MySQL shell.

Switch to the database you want to use.

MariaDB [mysql]> use mydb;
 Reading table information for completion of table and column names
 You can turn off this feature to get a quicker startup with -A
 Database changed
 MariaDB [mydb]> 

Then run the conversion commands given earlier.

Sample output:

 MariaDB [mydb]> ALTER TABLE wp_give_donors ENGINE=InnoDB;         
 Query OK, 0 rows affected (0.013 sec)              
 Records: 0  Duplicates: 0  Warnings: 0
 MariaDB [mydb]> ALTER TABLE wp_termmeta ENGINE=InnoDB;            
 Query OK, 0 rows affected (0.016 sec)              
 Records: 0  Duplicates: 0  Warnings: 0
 MariaDB [mydb]> ALTER TABLE wp_snp_stats ENGINE=InnoDB;           
 Query OK, 0 rows affected (0.015 sec)              
 Records: 0  Duplicates: 0  Warnings: 0
 MariaDB [mydb]> ALTER TABLE wp_es_pluginconfig ENGINE=InnoDB;     
 Query OK, 1 row affected (0.014 sec)               
 Records: 1  Duplicates: 0  Warnings: 0

Rerun the first command to check if there are tables returning for MyISAM.

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

Output:

 Empty set (0.003 sec)

Good, you now have all your database tables using InnoDB data storage engine.

Udemy Best Video Courses to Learn MySQL / MariaDB Databases:

• The Ultimate MySQL Bootcamp: Go from SQL Beginner to Expert
• SQL – MySQL for Data Analytics and Business Intelligence
• MySQL, SQL and Stored Procedures from Beginner to Advanced
• SQL for Beginners: Learn SQL using MySQL and Database Design
• The Complete MySQL Developer Course
• MySQL Database Administration: Beginner SQL Database Design
• Learn Database Design with MySQL

More MySQL guides:

How to Install MySQL 8.0 on Debian