Convert from INNODB to MYISAM

MYISAM is use by very less hosting companies now a days, as its mostly provided with the shared hosting account.

But sometimes, we needed to change mysql engine, when its create a problem while exporting and importing sql data from INNODB to MYISAM engine.

Steps to follow:

  • Take backup of Mysql database.
  • Run this sql query via terminal or in phpmyadmin for the database which you wish to convert into MYISAM.
 
mysql -u username -p -e "SELECT concat('ALTER TABLE ', TABLE_NAME,' ENGINE=MYISAM;') FROM Information_schema.TABLES WHERE TABLE_SCHEMA = 'db_name' AND ENGINE = 'InnoDB' AND TABLE_TYPE = 'BASE TABLE'" | tail -n+2 >> alter.sql

Note: Change ‘db_name’ with your actual database name

  • Import that alter.sql file into INNODB database

And you are done! 🙂