We can convert the MyIsam tables to InnoDB using the mysql command mysql> ALTER TABLE tablename ENGINE=InnoDB
But this may become a tedious task when the number of tables is very large, We can convert MyIsam tables to InnoDB using a php script file
Create a php file and add the following code to the file
<?php
//Your database connection items here
$host = 'hostname'; //Specify host
$dbuser = 'username'; // Specify user with alter permissions
$dbpass = 'password'; //user password
$mydb = 'mydb'; //specify schema or db to be modified
//connect to database using variables above
$link = mysql_connect($host,$dbuser,$dbpass);
$db = mysql_select_db($mydb);
if (!$link) {
die('Could not connect: ' . mysql_error());
}
echo "Connected Successfully to: $host." . "\n\n";
echo "Using database: $mydb." . "\n\n";
echo "Running script as $dbuser." . "\n\n";
//show tables in database
$sql = 'SHOW TABLES';
$rs = mysql_query($sql);
echo $sql;
echo "\n";
if (!$rs) {
die('SQL Recordset Error: ' . mysql_error());
}
else {
//loop through tables and convert to InnoDB
while($row = mysql_fetch_array($rs))
{
$tbl = $row[0];
$sql = "ALTER TABLE $tbl engine=InnoDB;";
mysql_query($sql);
echo $sql;
echo "\n";
}
echo 'Operation Completed.' . "\n\n";
echo 'Confirm Storage Engine conversion using phpmyadmin ' . "\n" . 'or from mysql: show create table tblname.' . "\n";
}
//close connection
mysql_close($link);
?>
Give execution permission to the file using the command # chmod +x file.php
Run the file using the command # php file.php
