How to convert All tables from MyIsam to innoDB in Mysql

Suppose you have 1 database, in which you have n number of tables.You want to fire such a Query so that will convert all table type from MyIsam to innoDB engine.

For that there is very simple technique, following are the details

  • Your database Name is must
  • Know your list of tables under that Database
  • Generate a dynamic query to Alter the table engine from MyIsam to InnoDB
  • We need to copy all those query and fire it in a SQL Query Browser

Following is the query which will get list of tables under particular Database, so that CONCAT function of mysql will generate the query which will further can be used to convert the Type types from MyIsam to innoDB.

1
2
SELECT CONCAT('ALTER TABLE ', TABLE_NAME, ' ENGINE=InnoDB;') AS QueryToFire
FROM information_schema.TABLES WHERE table_schema = 'DATABASENAME'

After firing this Query, this will generate list of Query for individual tabase, to convert MyIsam to InnoDB.