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";


?>

26 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
    Replies
    1. IEEE Project Domain management in software engineering is distinct from traditional project deveopment in that software projects have a unique lifecycle process that requires multiple rounds of testing, updating, and faculty feedback. A IEEE Domain project Final Year Projects for CSE system development life cycle is essentially a phased project model that defines the organizational constraints of a large-scale systems project. The methods used in a IEEE DOmain Project systems development life cycle strategy Project Centers in Chennai For CSE provide clearly defined phases of work to plan, design, test, deploy, and maintain information systems.


      This is enough for me. I want to write software that anyone can use, and virtually everyone who has an internet connected device with a screen can use apps written in JavaScript. JavaScript Training in Chennai JavaScript was used for little more than mouse hover animations and little calculations to make static websites feel more interactive. Let’s assume 90% of all websites using JavaScript use it in a trivial way. That still leaves 150 million substantial JavaScript Training in Chennai JavaScript applications.

      Delete
  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
  5. Really cool post, highly informative and professionally written and I am glad to be a visitor of this perfect blog, thank you for this rare info!
    Php course in chennai

    ReplyDelete
  6. You have the alternative to experience a specialist who knows the business and, for a charge, will speak to you to the makers. You could attempt chilly calling - giving over or messaging the content to makers yourself. coverage service

    ReplyDelete
  7. Hi Thanks for the nice information its very useful to read your blog.
    Advanced SEO Training

    ReplyDelete
  8. While writing PHP code, programmers have option to use either single quotes (') or double quotes ("). But the developers can easily enhance the performance of the PHP application by using single quotes instead of double quotes. The single code will increase the execution speed of loops drastically.plakatų spausdinimas

    ReplyDelete
  9. If you think that running a website ends by simple creating the website and regularly updating it, then you are forgetting one important factor which is Random Password Generator Tool. You need to understand that the security of your blogs and websites is your top priority.

    ReplyDelete
  10. Memang agar tidak mengundang kecurigaan yang lebih, disarankan untuk bermain pindah pindah meja. Hal ini dikarenakan dengan menggunakan Akun Pro maka kesempatan anda untuk menang semakin besar
    asikqq
    dewaqq
    sumoqq
    interqq
    pionpoker
    bandar ceme terbaik
    hobiqq
    paito warna terlengkap
    syair sgp

    ReplyDelete
  11. This comment has been removed by the author.

    ReplyDelete
  12. PHP and MySQL programming are exceptionally useful in Content Management System that has turned into an unquestionable requirement for each site to advance appropriately. Top Expert Cakephp Developers

    ReplyDelete
  13. Thanks for sharing this information.
    Want to Buy Step Down Transformer or Are you Curious about What is Step Down Transformer? Read the blog to get your queries resolved before making a purchase.

    ReplyDelete
  14. PHP substance can continue running across over working systems, for instance, Linux, Windows, Solaris, OpenBSD, Mac OSX, etc and moreover offer assistance for all genuine web servers, for instance, Apache, IIS, iPlanet and besides supports each.Why use Laravel

    ReplyDelete
  15. The first guy should be in medical classrooms, cause you could pretty much see every single muscle in his chest
    zero city zombie shelter survival hack

    ReplyDelete
  16. This is really use ful for me thank u and wonderful aap thank uuuu again gin rummy plus hack

    ReplyDelete
  17. Great blog thanks for sharing Leaders in the branding business - Adhuntt Media is now creating a buzz among marketing circles in Chennai. Global standard content creation, SEO and Web Development are the pillars of our brand building tactics.
    digital marketing company in chennai
    seo service in chennai
    web designing company in chennai
    social media marketing company in chennai

    ReplyDelete
  18. Nice blog thanks for sharing Re-decorate your messy lawn and fix up your boring old garden with Karuna Nursery Gardens. We offer speciality Garden maintenance services in Chennai. It’s time you made a difference to your backyard.
    plant nursery in chennai
    rental plants in chennai
    corporate gardening service in chennai

    ReplyDelete
  19. Excellent blog thanks for sharing Pixies Beauty Shop is unlike any of the other cosmetic shops in Chennai. With tons of exclusive imported brands to choose from and the best value, this is the best shopping destination for your personal and salon needs.
    Cosmetics Shop in Chennai

    ReplyDelete