InternetTechnologyWeb Development

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!

Paul Freeman-Powell

Paul Freeman-Powell

Paul (@paulfp) is the main presenter of the award-winning Switched On Network YouTube Channel, which covers a variety of interesting topics usually relating to his love of technology and all things geeky. He also founded and runs Innobella Media, where he leads in all aspects of video production, video editing, sound & lighting. A father of 3 children including twins, his hobbies used to include photography, playing the drums and cycling. With a degree in Modern European Languages, Paul speaks French, Spanish and a little bit of Italian, and holds dual British & Irish citizenship.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.