$db = ‘your_database_name_here’;
$dsn = “mysql:host=localhost;dbname=$db”;
$username = “your_MySQL_username”;
$password = “your_password”;
$current = ‘MyISAM’; // Current storage engine
$target = ‘INNODB’; // Target storage engine
try {
$pdo = new PDO($dsn, $username, $password);
}
catch(PDOException $e) {
die(“Could not connect to the database\n”);
}
$result = $pdo->query(“SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = ‘$db’ AND ENGINE = ‘$current’”);
foreach($result as $row) {
$success = $pdo->exec(“ALTER TABLE {$row['TABLE_NAME']} ENGINE = $target”);
if($success) {
echo “{$row['TABLE_NAME']} – success\n”;
} else {
$info = $pdo->errorInfo();
echo “{$row['TABLE_NAME']} – failed: $info[2]\n”;
}
}
That’s all. If you see “failed” messages, there’s probably a permission issue with information_schema table. In that case you should enter MySQL command line, and repeat the procedure there. To list storage engines for tables, you should execute;
SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = ‘your_database_name’;
This will show you which tables are using which storage engine. After that you should execute;
USE your_database_name;
When you see “Database changed” message, you should execute;
ALTER TABLE your_table_name ENGINE = INNODB;
Have a nice life with your magnificent database storage engine InnoDB !
Please check out other articles about InnoDB and Transactions.
