PHP Script: MySQL, database-wide engine modification
2 large MySQL databases (3 gigabytes worth of data and 2,600 tables) somehow managed to corrupt last week and could not be repaired. It should have been a straight forward task for the outsourced Indian server administrators to restore a backup from a previous day but it wasn't!
In the end they managed to restore a backup of both databases but for some reason they restarted the MySQL server with skip-innodb, so rather than using the desired InnoDB storage engine the databases were restored using the MyISAM storage engine. The application that needs to use this database is a high usage web statistics application that cannot run using MyISAM without 20% of the tables corrupting every other day. So MyISAM just isn't acceptable.
You cannot just issue an alter database command and change the storage engine database-wide, the only option you have is alter table. With 2,600 tables that just isn't practical so I created a PHP script to change the engine type for all tables in a MySQL database.
$mySqlusr = "";// your mySql user name
$mySqlpwd = ""; // your mySql password
$mySqldb = ""; // the name of the database
$mySqlhost = ""; // mySql database host address
$fromEngine = "MyISAM";
$toEngine = "InnoDB";
$connect_current = mysql_connect($mySqlhost, $mySqlusr, $mySqlpwd);
mysql_select_db($mySqldb, $connect_current);
//show all tables with $fromEngine engine type
$result = mysql_query("SHOW TABLE STATUS FROM $mySqldb WHERE ENGINE = '${fromEngine}'", $connect_current);
echo mysql_error();
//loop through tables
while ($row = mysql_fetch_array($result)) {
//update table with $toEngine engine type
$unique_visitors = mysql_query("ALTER TABLE ${row[0]} ENGINE=${toEngine}", $connect_current);
}
echo "MySql engine type changed from <strong>${fromEngine}</strong> to <strong>${toEngine}</strong>.";
?>
