How to change storage engine to InnoDB in MySQL?

You’ve designed a database with MyISAM and suddenly realized that you need ACID (atomicity, consistency, isolation, durability) compliant features and transactions. Right, you should use InnoDB. But how to change storage engine for your previous tables ? As far as i know, changing the storage engine for tables is easy by alter command. But there’s one flaw. You should do it one by one. On the other hand, you could either write complex SQL queries, or you could write a php code for bulk changes. Today i’m gonna show you how to change your storage engine with a tiny code. Please notice that if you decide to use my code it is your responsibility.
    $db = ‘your_database_name_here’;

    $dsn = “mysql:host=localhost;dbname=$db”;

    $username = “your_MySQL_username”;

    $password = “your_password”;

    $current = ‘MyISAM’; // Current storage engine

    $target = ‘INNODB’; // Target storage engine

     

    try {

    $pdo = new PDO($dsn, $username, $password);

    }

    catch(PDOException $e) {

    die(“Could not connect to the database\n”);

    }

    $result = $pdo->query(“SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = ‘$db’ AND ENGINE = ‘$current’”);

    foreach($result as $row) {

    $success = $pdo->exec(“ALTER TABLE {$row['TABLE_NAME']} ENGINE = $target”);

    if($success) {

    echo “{$row['TABLE_NAME']} – success\n”;

    } else {

    $info = $pdo->errorInfo();

    echo “{$row['TABLE_NAME']} – failed: $info[2]\n”;

    }

    }

That’s all. If you see “failed” messages, there’s probably a permission issue with information_schema table. In that case you should enter MySQL command line, and repeat the procedure there. To list storage engines for tables, you should execute;
SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = ‘your_database_name’;

This will show you which tables are using which storage engine. After that you should execute;
USE your_database_name;

When you see “Database changed” message, you should execute;
ALTER TABLE your_table_name ENGINE = INNODB;

Have a nice life with your magnificent database storage engine InnoDB !

Please check out other articles about InnoDB and Transactions.
  • 94 istifadəçi bunu faydalı hesab edir
Bu cavab sizə kömək etdi?

Uyğun məqalələr

Simple LDAP Class for PHP

In this post, i’m gonna explain how to connect to a LDAP server via using PHP. First i...

Update MySQL user passwords from H-Sphere DataBase

This article is only applicable to H-Sphere control panel where old mysql passwords are stored as...

Joomla – Handling Errors

I find Joomla non-developer friendly for the most of the time; documentation is poor and not...

Installing IonCube Loader on Linux

Installation of IonCube Loader on any variaty of Linux is very simple.Just get the copy from...

Install JSON Support to PHP on CentOS

Here’s a way to add JSON support for PHP on your CentOS server. yum install php-pear...