Convert MySql database tables types from MyISAM to InnoDB

Convert MySql database tables types from MyISAM to InnoDB

Conversion on dump file
If you have a dump file of mysql, The easiest way is to run the command on the dump file. It will replace the tables from MyISAM to INNODB

sed -i 's/MyISAM/INNODB/g' dbname.sql

You can also change the command and convert INNODB to MyISAM by this command

sed -i 's/INNODB/MyISAM/g' dbname.sql

 
 
Conversion on current database
If you want to convert MyISAM to InnoDB on the fly. The following is the procedure.
We will query and list the database tables and build the list of alter statements which will update the database tables.

SET @DATABASE_NAME = 'database_name';
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;

Once you have the list of queries. You can copy and executes these statements to alter database.
 

Share this post

Leave a Reply

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