Site icon David Yin's Blog

Convert MyISAM tables to InnoDB without Touching Data

It is a smoothly converting without hurting data inside of the tables.

I have phpmyadmin installation on my VPS. So just goto there and select database, click SQL tab of it.

Enter following sql and click Go.

ALTER TABLE tablename ENGINE = INNODB;

If the table is large, then it may take a while to convert it over. There will probably be a fair amount of CPU usage and disk I/O in the process. In my case, I do not feel the delay when I click Go button.

There are also some script can help you alter all tables. Then, you don’t have to type sql command for each table, if you have hundred tables of databases.

<?php
    // connect your database here first
    //

    // Actual code starts here

    $sql = "SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE engine <> 'InnoDB'";
    $rs = mysql_query($sql);

    while($row = mysql_fetch_array($rs))
    {
        $tbl = $row[0];
        $sql = "ALTER TABLE $tbl ENGINE=INNODB";
        mysql_query($sql);
    }
?>

Save above php script into a php file into your web server. Modify the database connection portion. Then run this script from your browser. It will do all the job for you.

Some help from Major.

Exit mobile version