Thursday, April 12, 2012

Moodle: PHP script to convert Mysql database from MYISAM to InnoDB

I wrote (or rather modified an existing script) that batch converts the moodle database from MyISAM to InnoDB. This saves a lot of time, rather than writing a couple hundred ALTER TABLE statements, or even using the phpMyadmin interface.
Credit for the original script:
http://stackoverflow.com/questions/3856435/how-to-convert-all-tables-from-myisam-to-innodb
 
Please test on a copy of the database before using. Do not run on a production database! 
I can't emphasize that enough.

It's been tested on both Moodle 1.9 and 2.0/2/2, see script preamble for further details.

It's a potentially hazardous script, so I won't recommend placing it in any web accessible folders (like /var/www/html/..). It's been made relatively simple to implement, and the instructions are below:

0. Login as root/su

1. Ensure that InnoDB is enabled on your server
mysql -u root -p *****
SHOW ENGINES;
Expected output
...
InnoDB | YES | Supports transactions...


2. Install script on your server
Copy/Paste the code at the bottom of the post into a new file named innodb_convert.php on your Moodle server where the database is located.
Please ensure that it is not in a web accessible folder.



3. Give the file execute permissions to the script
chmod +x innodb_convert.php

4. Configuration
Open the file and navigate to the database configuration section (approx line 48). Set relevant variables.
$host = 'myhost'; //Specify host
$dbuser = 'myuser'; // Specify user with alter permissions 
$dbpass = 'mypass'; //user password
$mydb = 'mydb'; //specify schema or db to be modified
5. Save and exit.

6. From the command line, call the script using php
cd /tmp
php innodb_convert.php



7. Normal output expected:
ALTER TABLE mytable1 engine=InnodDB;
ALTER TABLE mytable2 engine=InnodDB;
ALTER TABLE mytablen engine=InnodDB;
Operation Completed

Confirm Storage Engine conversion using phpyadmin
or from Mysql: show create tablename.
Script Execution time: 999.99 seconds

The script (see preamble for additional information):

Download text file here.

<?php
// WARNING: PLEASE TEST BEFORE RUNNING ON A PRODUCTION DATABASE
// Tested on Moodle 1.9 and Moodle 2.0/2.2
//
// Disclaimer:
//
// This script was written to be executed from the command line in 
// Linux using a MYSQL database 
// and MYISAM storage engine. It converts the storage engine to 
// INNODB using the ALTER TABLE command.
//
// It assumes that the INNODB engine has been enabled. 
// This can be confirmed by logging into the mysql prompt 
// and running the command: 
// show engines;
//
// If it is disabled, check your my.cnf and 
// comment out the line: skip-innodb
//
// It does not check if disabled, so the script would execute 
 //and attempt to co
// It contains secure information, so DO NOT PLACE IN WEB DIRECTORY
//
// Execute from /temp and delete afterwards
//
// This is a modification of a script located here:
// http://stackoverflow.com/questions/3856435/how-to-convert-all-tables-from-myisam-to-innodb 
//
// Version : 1.2
// Date  : 11 April 2012
// Diff  :  check for recordset before executing alter table
//
// Version : 1.1
// Date  : 31 January 2012
// Diff  :  Added Timer to script; Display user and DB being affected
//
//
// Version :  1.0 
// Date  : 25 January 2012
//
// 
//
// Noveck Gowandan http://noveckg.blogspot.com

//Time Calculation - start count

$time = explode(' ',microtime());
$time = $time[1] + $time[0]; //return array
$begintime = $time; //define begin time

//Your database connection items here
 
$host = 'myhost'; //Specify host
$dbuser = 'myuser'; // Specify user with alter permissions 
$dbpass = 'mypass'; //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);

$time = explode(" ", microtime());
$time = $time[1] + $time[0]; 
$endtime = $time; //define end time
$totaltime = ($endtime - $begintime);
echo "Script Execution Time: $totaltime" . " seconds." . "\n\n";


?>

4 comments:

  1. Thanks for sharing such a nice information. I hope it will help many more. Teachers can schedule online classes, give high impact presentations and watch class recordings with Moodle! Know more about Moodle - http://bit.ly/IpcyZ8

    ReplyDelete
  2. better use this sql

    $sql = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE = 'MyISAM' AND TABLE_SCHEMA = '$mydb'";

    ReplyDelete
  3. Obrigada! Funcionou muito bem, travou um pouco na tabela license, mas depois de uns minutos prosseguiu... Thanks very much!!!

    ReplyDelete
  4. Frequent auditing of the safety measures and updating the tools can also be of immense help in protecting the database of a firm.create mysql dashboard

    ReplyDelete