Convert all MySQL tables to MyISAM from InnoDB

Run this SQL statement (in the mysql client, phpMyAdmin, or wherever) to retrieve all the InnoDB tables in your database.

Replace value of the name_of_your_db variable with your database name.

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

Then, copy the output and run as a new SQL query.

Leave a Reply

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

five − 2 =

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