If you followed my last post you wound up with a killer VPS running WordPress. One that crashes a little too often. Whoops. So here’s how to correct that issue.

MySQL uses InnoDB as its default Storage Engine. Well as it turns out InnoDB consumes a lot of RAM which makes it not very VPS friendly. So just disable it, right? Yeah of course not quite so easy. Since its been on from when you started setting up your wordpress DB, everything is already in InnoDB format. By switching it off, WordPress won’t be able to see the tables and will want to start over from scratch. Luckily a little Google search turned up a quick and painless way to convert between the 2 storage engine formats. Easy for you that is, I spent a good hour trying to sort this all out.

First, take this handy little php script and save it on your server:

<?php
#This script will change all the table engine types for a given database!
#All the DB tools I have (GNU/freeware) will not change a list of database
# types, so this script saves time when a CMS or other populates a database
# with tables we cannot use! This can be migrated to InnoDB by changing line
# 23, col 46 from MyISAM to InnoDB (double check the capitals there!).
# Change these variables relative: serverName, userName, password, databaseName

# 20051410 JLynch
# myisamFixer.php

ini_set(‘display_errors’, ‘On’);
error_reporting(E_ALL);

$link = mysql_connect(“localhost“,”root“,”myrootpass“)
or die(“unable to connect to msql server: ” . msql_error());

mysql_select_db(“wordpress“, $link)
or die(“unable to select database ‘db’: ” . msql_error());

$result = mysql_query(“show tables”);
if (!$result) {
die(‘query failed: ‘);
}

while ($row = mysql_fetch_array($result)){
mysql_query(“ALTER TABLE “.$row[0].” ENGINE=MyISAM; “);
#Command Reference: ALTER TABLE tableName ENGINE=MyISAM
}

?>

Ovbiously you need to change the bolded values to match your own config.

Next, stop mysql:

service mysqld stop

Execute the .php script from the command line. It might throw a couple of errors about such and such command is now deprecated, whatever bitch, it works:

php myisamFixer.php

Next, edit /etc/my.cnf. Add these 2 lines somewhere in the [mysqld] section:

ignore_builtin_innodb
default_storage_engine=MyISAM

Start mysql back up:

service mysqld start

Thats it. Back in business with all your content and settings preserved running MyISAM instead of the memory-hungry DB crashing VPS hatin nightmare InnoDB. Had I done this from the start I could have avoided the conversion.