Convert MySql database tables types from MyISAM to InnoDBzalam
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.