How to Change All MySQL Tables from MyISAM to InnoDB

Here’s a quick-and-dirty little script you can run if you realise you still have some MyISAM tables in your MySQL database which need converting to InnoDB.

(Obviously I’m not addressing any compatibility checks you might need to do here, and also make sure to back everything up first.)

[php] <?php
echo "<pre>";
ini_set(‘display_errors’,1);
ini_set(‘display_startup_errors’,1);
error_reporting(-1);

$database = mysqli_connect("localhost", "dbuser", "dbpasswd");

$sql = "SELECT TABLE_NAME, TABLE_SCHEMA FROM INFORMATION_SCHEMA.TABLES
WHERE ENGINE = ‘MyISAM’ AND TABLE_SCHEMA NOT IN (‘information_schema’, ‘mysql’)";

$rs = mysqli_query($database, $sql);

while($row = mysqli_fetch_array($rs)) {
$table = $row[‘TABLE_NAME’];
$databse_name = $row[‘TABLE_SCHEMA’];
$select = mysqli_select_db($database, $row[‘TABLE_SCHEMA’]);
$sql = "ALTER TABLE `$table` ENGINE=INNODB";
mysqli_query($database, $sql);
var_dump($sql);
}
?>
[/php]

The code gets a list of all your database tables (not including information_schema and mysql) which use the MyISAM engine, and then loops through them all first selecting the correct database and then changing the engine to InnoDB.

The user you run this as will need sufficient privileges across all the databases and tables that you want to affect.

There’s probably a better way to do this, but hey – it works!