How to convert all tables from MyISAM into InnoDB?

Run this SQL statement (in the MySQL client, phpMyAdmin, or wherever) to retrieve all the MyISAM 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=InnoDB;') AS sql_statementsFROM    information_schema.tables AS tbWHERE   table_schema = @DATABASE_NAMEAND     `ENGINE` = 'MyISAM'AND     `TABLE_TYPE` = 'BASE TABLE'ORDER BY table_name DESC;

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

<?php    // connect your database here first     //     // Actual code starts here     $sql = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES        WHERE TABLE_SCHEMA = 'your_database_name'         AND ENGINE = 'MyISAM'";    $rs = mysql_query($sql);    while($row = mysql_fetch_array($rs))    {        $tbl = $row[0];        $sql = "ALTER TABLE `$tbl` ENGINE=INNODB";        mysql_query($sql);    }?>

SELECT CONCAT('ALTER TABLE ',TABLE_NAME,' ENGINE=InnoDB;') FROM INFORMATION_SCHEMA.TABLESWHERE ENGINE='MyISAM'AND table_schema = 'mydatabase';

Works like a charm.

This will give you list of all tables with the alter queries that you can run in a batch