How to convert all MyISAM tables to InnoDB in a MySQL Database

To change multiple tables from the MyISAM storage engine to InnoDB, run SELECT CONCAT… to generate the conversion commands, then run the resulting commands in the MySQL console. This saves typing and includes all the tables you want to convert. Method #1 - Change the storage engine from MyISAM to InnoDB Run the command in the MySQL console and copy the results back into the console one command at a time. This lets you see which tables cannot be converted and the errors they produce. Command to display the storage engine conversion commands:

SELECT CONCAT('ALTER TABLE `',table_schema,'`.`',table_name,'` engine=InnoDB;') FROM information_schema.tables WHERE engine = 'MyISAM';

Output:

+--------------------------------------------------------------------------+
| CONCAT('ALTER TABLE `',table_schema,'`.`',table_name,'` engine=InnoDB;') |
+--------------------------------------------------------------------------+
| ALTER TABLE `information_schema`.`COLUMNS` engine=InnoDB;                |
| ALTER TABLE `information_schema`.`EVENTS` engine=InnoDB;                 |
| ALTER TABLE `information_schema`.`PARAMETERS` engine=InnoDB;             |
                            ... 
| ALTER TABLE `mysql`.`time_zone_transition` engine=InnoDB;                |
| ALTER TABLE `mysql`.`time_zone_transition_type` engine=InnoDB;           |
| ALTER TABLE `mysql`.`user` engine=InnoDB;                                |
                            ... 
| ALTER TABLE `DB1`.`table1` engine=InnoDB;                                |
| ALTER TABLE `DB1`.`table2` engine=InnoDB;                                |
| ALTER TABLE `DB1`.`table3` engine=InnoDB;                                |
| ALTER TABLE `DB1`.`table4` engine=InnoDB;                                |
| ALTER TABLE `DB1`.`table5` engine=InnoDB;                                |
| ALTER TABLE `DB1`.`table6` engine=InnoDB;                                |
+--------------------------------------------------------------------------+
31 rows in set, 1 warning (0.04 sec)

The result contains storage engine conversion commands for all databases. Select every database except mysql and information_schema, because those two do not support the InnoDB storage engine, and run the commands in the MySQL console. Method #2 - Change the storage engine from MyISAM to InnoDB Run the command in the MySQL console, save the results to a text file, and use source in the MySQL console to run all the commands.

SELECT CONCAT('ALTER TABLE `',table_schema,'`.`',table_name,'` engine=InnoDB;') FROM information_schema.tables WHERE engine = 'MyISAM' into outfile '/tmp/MyISAM2InnoDB.txt';

The command above creates MyISAM2InnoDB.txt containing alter table statements. Remove the commands for the mysql and information_schema databases. Then open the MySQL console and run the following to change the storage engine from MyISAM to InnoDB:

source /tmp/MyISAM2InnoDB.txt;