• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
The Electric Toolbox Blog

The Electric Toolbox Blog

Linux, Apache, Nginx, MySQL, Javascript and PHP articles

  • Applications
  • FCKEditor
  • Apache
  • Windows
  • Contact Us
Home / PHP script to convert MySQL tables to a new storage engine

PHP script to convert MySQL tables to a new storage engine

MySQL supports multiple storage engines which each have their pros and cons; the two most commonly used are MyISAM and INNODB. It’s easy to convert a table from one storage engine to another, and this post has a PHP script which convert all tables in a MySQL database from one engine to another.

Storage engine incompatibilities

Note that there are often some incompatibilities between storage engines which prevent a table from being converted from one to another. For example, MyISAM tables can have full text indexing but INNODB tables cannot; attempting to convert a MyISAM table to INNODB when it has a full text index will fail.

Conversion can take time

Converting from one storage engine to another can take a lot of time depending on how large the database tables are; they have to be recreated to change the engine. It is not recommended this be done on busy production websites without first taking the website offline.

The PHP script

Substitute the variables at the top of the script with those appropriate for your database, and set the $from and $to variables to the storage engine you want to change from and to. The example script attempts to convert all MyISAM tables to INNODB.

$db = 'myDatabaseName';
$dsn = "mysql:host=localhost;dbname=$db";
$username = "myUsername";
$password = "myPasssword";

$from = 'MyISAM';
$to = 'INNODB';

try {
    $pdo = new PDO($dsn, $username, $password);
}
catch(PDOException $e) {
    die("Could not connect to the databasen");
}

$result = $pdo->query("
	SELECT TABLE_NAME
	FROM information_schema.TABLES
	WHERE TABLE_SCHEMA = '$db'
	AND ENGINE = '$from'
");

foreach($result as $row) {
	$success = $pdo->exec("ALTER TABLE {$row['TABLE_NAME']} ENGINE = $to");
	if($success) {
		echo "{$row['TABLE_NAME']} - successn";
	}
	else {
		$info = $pdo->errorInfo();
		echo "{$row['TABLE_NAME']} - failed: $info[2]n";
	}
}

The script connects to the database server, then queries the information_schema for all the tables in the database that have a storage engine of the type $from.

It then loops through the result set and attempts to convert the table. If there was an error in converting the table this is echoed out.

Example output

Here’s some example output from the script, showing a table that was successfully converted from MyISAM to INNODB and one that failed because it contains a full text index:

test1 - success
test1 - failed: The used table type doesn't support FULLTEXT indexes

Check Out These Related posts:

  1. MySQL utility commands
  2. Fetching data using PHP and PDO with bound placeholders
  3. How to tell which storage engine a MySQL table uses
  4. How to change the storage engine a MySQL table uses

Filed Under: MySql, PHP

Primary Sidebar

Categories

  • Apache
  • Applications
  • Article
  • Case Studies
  • Email Servers
  • FCKEditor
  • HTML And CSS
  • Javascript
  • Linux/Unix/BSD
  • Microsoft SQL Server
  • Miscellaneous Postings
  • MySql
  • Networking
  • Nginx Web Server
  • Offsite Articles
  • OSX
  • PHP
  • Quick Tips
  • RFC – Request for Comments
  • SilverStripe
  • VMWare
  • VPN
  • Windows
  • WordPress

Recent Posts

  • Vim Show Line Numbers
  • Add User To Group Linux
  • Chmod 777 Tutorial
  • How to Copy Directory Linux
  • Linux create user

Copyright © 2021. ElectricToolBox. All Rights Reserved.

  • Contact Us
  • Copyright Info
  • Privacy Policy
  • Sitemap