Change MySQL engine type of all tables from MyISAM to InnoDB

Do you want to change MySQL engine type of a table from MyISAM to InnoDB? Well, it can be done using ALTER TABLE statement, but what if you have few hundreds of tables and you need to convert all of those into InnoDB? In that case, the ALTER TABLE statement has to be executed for every table in the database and that’s time consuming. Wonder if that can be done little quicker? Here’s how it’s.

How to change MySQL Engine type of all tables from MyISAM to InnoDB?

Step 1: Login to MySQL using the below options.

$ mysql -s -r -u root -p -h localhost

Note: The above option -r instructs MySQL to print the output in non-tabular format (which is actually easy to copy and paste)

Step 2: Set the database name.

SET @DATABASE_NAME='database_name'

E.g.,

mysql> SET @DATABASE_NAME = 'filecloud';
Query OK, 0 rows affected (0.00 sec)

Step 3: Execute the below query.

SELECT CONCAT('ALTER TABLE `', table_name, '` ENGINE=InnoDB;') AS sql_statements FROM information_schema.tables AS tb WHERE table_schema = @DATABASE_NAME AND     `ENGINE` = 'MyISAM'
AND     `TABLE_TYPE` = 'BASE TABLE'
ORDER BY table_name DESC;

For e.g.,

mysql> SELECT CONCAT('ALTER TABLE `', table_name, '` ENGINE=InnoDB;') AS sql_statements
-> FROM information_schema.tables AS tb
-> WHERE table_schema = @DATABASE_NAME
-> AND `ENGINE` = 'MyISAM'
-> AND `TABLE_TYPE` = 'BASE TABLE'
-> ORDER BY table_name DESC;
+--------------------------------------------------------+
| sql_statements |
+--------------------------------------------------------+
| ALTER TABLE `user_level_pricing` ENGINE=InnoDB; |
| ALTER TABLE `user_level` ENGINE=InnoDB; |
| ALTER TABLE `users` ENGINE=InnoDB; |
| ALTER TABLE `theme` ENGINE=InnoDB; |
| ALTER TABLE `stats` ENGINE=InnoDB; |
| ALTER TABLE `site_config` ENGINE=InnoDB; |
| ALTER TABLE `remote_url_download_queue` ENGINE=InnoDB; |
| ALTER TABLE `premium_order` ENGINE=InnoDB; |
| ALTER TABLE `plugin` ENGINE=InnoDB; |
| ALTER TABLE `payment_log` ENGINE=InnoDB; |
| ALTER TABLE `login_success` ENGINE=InnoDB; |
| ALTER TABLE `login_failure` ENGINE=InnoDB; |
| ALTER TABLE `language_key` ENGINE=InnoDB; |
| ALTER TABLE `language_content` ENGINE=InnoDB; |
| ALTER TABLE `language` ENGINE=InnoDB; |
| ALTER TABLE `internal_notification` ENGINE=InnoDB; |
| ALTER TABLE `file_status` ENGINE=InnoDB; |
| ALTER TABLE `file_server_status` ENGINE=InnoDB; |
| ALTER TABLE `file_server` ENGINE=InnoDB; |
| ALTER TABLE `file_report` ENGINE=InnoDB; |
| ALTER TABLE `file_folder_share` ENGINE=InnoDB; |
| ALTER TABLE `file_folder` ENGINE=InnoDB; |
| ALTER TABLE `file_block_hash` ENGINE=InnoDB; |
| ALTER TABLE `file_action` ENGINE=InnoDB; |
| ALTER TABLE `file` ENGINE=InnoDB; |
| ALTER TABLE `download_tracker` ENGINE=InnoDB; |
| ALTER TABLE `download_page` ENGINE=InnoDB; |
| ALTER TABLE `cross_site_action` ENGINE=InnoDB; |
| ALTER TABLE `country_info` ENGINE=InnoDB; |
| ALTER TABLE `banned_ips` ENGINE=InnoDB; |
| ALTER TABLE `background_task_log` ENGINE=InnoDB; |
| ALTER TABLE `background_task` ENGINE=InnoDB; |
+--------------------------------------------------------+
32 rows in set (0.00 sec)

Note: The output of step 3 will give you the ALTER TABLE statement for every table in the database.

Step 4: Copy the output and paste it on the mysql prompt. It’s basically like executing ALTER TABLE statement for every table in the selected database. This step will change MySQL engine type to InnoDB for all tables.

Step 5: Confirm that the tables are using ENGINE type InnoDB.

mysql> SHOW TABLE STATUS from <database_name>;

For e.g.,

mysql> SHOW TABLE STATUS from filecloud;

Lookout for column ‘Engine‘ in the output.

That’s it!

Alternatively, a StackOverflow user has created a PHP script to do this job.