How to convert all tables from MyISAM into InnoDB?

by

If you omit the ENGINE option, the default storage engine is used. Normally, this is MyISAM, but you can change it by using the --default-storage-engine server startup option, or by setting the default-storage-engine option in the my.cnf configuration file.

You may also want to change the default storage engine just for the current session. You can do this by setting the storage_engine variable:

SET storage_engine=INNODB;

PHP Code to change the database engine

<?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 <> 'InnoDB'";

    $rs = mysql_query($sql);

    while($row = mysql_fetch_array($rs))
    {
        $tbl = $row[0];
        $sql = "ALTER TABLE $tbl ENGINE=INNODB";
        mysql_query($sql);
    }
?>

Leave a Comment