Admin-Ahead Community

Linux => General Linux => Topic started by: jominj on December 30, 2013, 07:31:54 pm

Title: php script for converting MyIsam tables to InnoDB
Post by: jominj on December 30, 2013, 07:31:54 pm

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

Code: [Select]

<?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