Hướng dẫn chuyển MySQL tables từ MyISAM trờ thành InnoDB

One of the greatest things ever to happen to MySQL was the InnoDB engine. Before InnoDB, indexes would get corrupted, updates meant table locks, not just row locks, and we had no support for transactions. Since the advent of InnoDB however, we've come a long way. These days, most serious DBAs using MySQL build exclusively on the InnoDB engine.

Sadly though, many sites are still using the MyISAM engine. Some are hosted on shared hosting servers and some just don't have a proper DBA to look after their databases. For whatever the reason, these sites are missing out on the performance and stability gains that the rest of us take for granted. At Pantheon, we know there are a lot of these sites out there because we see them when they migrate their sites onto our platform. As part of our Launch Check, we check the engine type on every table. If we find a table using the MyISAM engine, we notify the user so they can fix it.

Fixing this problem is simple to any developer who understands a little SQL. However, for non-developers, this can be a daunting task. So I have put together a little PHP script to help you convert your MyISAM tables to InnoDB.

NOTE: This is an unofficial script. It is not supported by Pantheon. Customer Support will not help you run it, nor will they hold your hand while fixing things if this script screws up your database. Use THIS SCRIPT AT YOUR OWN RISK. I strongly suggest you make a backup of your database before running this script. 

The Easy Way

Still with me? Cool! Let's dive right in. 

There are 2 versions of this script. One is meant to be called from a web browser. It is meant for Pantheon customers. If you are a Pantheon customer, save the code in a file with the extension .php and sftp it up to your site. Place it in the code directory of your website.

Now, point a browser to your newly created script that should be in the root directory of your Dev environment.

http://your.dev.url.gotpantheon.com/filename.php

That is all there is to it. The script will do all the work. You don't need to change anything.

Here is the browser version of the script:

<style>
.green { color: green; font-family: monospace;}
.red { color: red; font-family: monospace;}
</style>
<h1>Pantheon MyISAM to InnoDB engine converter</h1>

<?php
/*
 * Use this script ONLY if you are a Pantheon customer. 
 * ONLY RUN THIS SCRIPT IN DEV!
 */
$mysqli = @new mysqli($_ENV['DB_HOST'] . ':' . $_ENV['DB_PORT'], $_ENV['DB_USER'], $_ENV['DB_PASSWORD'], $_ENV['DB_NAME']);

if ($mysqli->connect_errno) {
    echo "<h1>Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error ."</h1>\n";
    die(1);
}

$results = $mysqli->query("show tables;");

if ($results===false or $mysqli->connect_errno) {
    echo "<h1>MySQL error: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error ."</h1>\n";
    die(2);
}

while ($row= $results->fetch_assoc())
{
    $sql = "SHOW TABLE STATUS WHERE Name = '{$row['Tables_in_pantheon']}'";
    $thisTable = $mysqli->query($sql)->fetch_assoc();

    if ($thisTable['Engine']==='MyISAM') {
        $sql = "alter table " . $row['Tables_in_pantheon'] . " ENGINE = InnoDB;";
        echo $row['Tables_in_pantheon'] . " is using the " . $thisTable['Engine'] . " Engine. <span class='red'>[ Changing ]</span> <br />\n";
        $mysqli->query($sql);           
    } else {
        echo $row['Tables_in_pantheon'] . ' is already using the ' . $thisTable['Engine'] . " Engine. <span class='green'>[ Ignoring ]</span> <br />\n";
    }   
};

die(0);

The Developer Way

If you are not a Pantheon customer, or you want to run the script from the command line, use the one below. To use this one however, you need to know two things before you begin.

1. You have to have PHP installed on the computer you want to run this on. I run this on my laptop. I have PHP installed on my laptop. I know that because I can type php -v at the command line and I get a proper response. If you do not understand any of that, this script is not for you, use the one above.
2. If you are a Pantheon customer, you can get your database connection info from your site's dashboard. Make sure you get the information for your Dev environment. After you've run the script, and everything looks good, you can easily migrate it up the line to Test and then Live. If you are running this on your site and your site is not on Pantheon, you will have to find another way to get your MySQL connection info.

Here is the command line script:

<?php
/*
 * Use this version if you are NOT a Pantheon customer. 
 */
$db = array();

/*
 * Change these to match your database connection information
 */
$db['host']     = "localhost";
$db['port']     = "3306";
$db['user']     = "";
$db['password'] = "";
$db['database'] = "";

/*
 * DO NOT CHANGE ANYTHING BELOW THIS LINE
 * Unless you know what you are doing. :)
 */
$db['connectString'] = $db['host'];

if (isset($db['port']) && (int)$db['port']==$db['port']) {
	$db['connectString'] .= ":" . $db['port'];
}

$mysqli = @new mysqli($db['connectString'], $db['user'], $db['password'], $db['database']);

if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error ."\n";
    die(1);
}

$results = $mysqli->query("show tables;");

if ($results===false or $mysqli->connect_errno) {
    echo "MySQL error: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error ."\n";
    die(2);
}

while ($row= $results->fetch_assoc()) {
	$sql = "SHOW TABLE STATUS WHERE Name = '{$row['Tables_in_' . $db['database']]'";
	$thisTable = $mysqli->query($sql)->fetch_assoc();

	if ($thisTable['Engine']==='MyISAM') {
		$sql = "alter table " . $row['Tables_in_' . $db['database']]. " ENGINE = InnoDB;";
		echo "Changing {$row['Tables_in_' . $db['database']]} from {$thisTable['Engine']} to InnoDB.\n";
		$mysqli->query($sql);		
	} else {
		echo $row['Tables_in_' . $db['database']] . ' is of the Engine Type ' . $thisTable['Engine'] . ".\n";
		echo "Not changing to InnoDB.\n\n"; 
	}
}

die(0);

See the line that says "* DO NOT CHANGE ANYTHING BELOW THIS LINE"? I'm serious about that. If it doesn't' work and you are not a programmer, don't mess with it. If you are a programmer, you realize exactly how dead-simple the script is.

Here are the parameters you will need to configure before running the script.

• host = This is the name of the machine your db is running on. If you are a Pantheon customer localhost is WRONG. Get the correct host and paste it in there replacing localhost. 
• port = This is the port that is running MySQL on your computer. Again, if you are a Pantheon customer, we give you this information. If you are not, 3306 is the standard port for MySQL. 
• user = This is the user name you use to connect to MySQL with. 
• password = This is your MySQL password for the user you specified in the line above.
• database = This is the name of the database that contains the tables. If you are a pantheon customer, this is "pantheon". If you are not a Pantheon customer, you will need to get this from your host.

Now, save the file.

Then from a command window execute the program. (How you get a command window varies by OS. If you don't know, ask your computer friend, younger sibling, or niece.)

The script will tell you everything it is doing. It has safeties built in to keep it from changing anything but MyISAM tables. It will look at every table in your database and if the engine is MyISAM, it will change it to an InnoDB.

Once you have run it successfully, check everything! 

This is not a particularly dangerous script. The change is pretty simple. However, it's your data. So be careful.