MARKDOWN 31
Convert all MySQL tables from MyISAM into InnoDB By mike on 14th July 2021 03:47:29 PM

Convert all MySQL tables from MyISAM into InnoDB

https://computingforgeeks.com/how-to-convert-all-mysql-tables-from-myisam-into-innodb-storage-engine/

Check which tables are MyISAM

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; 

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.

Hasta la pasta! is for source code and general debugging text.

Login or Register to edit, delete and keep track of your pastes and more.

Raw Paste

Login or Register to edit or fork this paste. It's free.