Testing HyperDB

- by

I’m as excited as a kid in a candystore! I’ve heard a lot about HyperDB and what can be done with it, and I’ve been thinking how cool it would be to implement it on the sites I’m hosting. And today my friends I’m proud to say that IT WORKS A TREAT!

Hyper What?

Let me tell you a bit about HyperDB. It’s another super cool Automattic project fronted by Matt, Andy, Ryan and Blogluftwaffe. It’s a rather complex plugin for WordPress that will let you specify more than one Database Server. In a high production environment your one and only Database Server can get busy or even crash and die – so all websites would be down until you restart it.

HyperDB solves this by letting you specify several servers in the handy db-config.php file. You can set one as master server for write queries and several others for read queries. This means that the master server doesn’t get bothered with read queries while several other servers can deal with those. If one read server dies, the others can pick up the slack. It’s genius!

The Experiment

Before a general roll-out to live sites a little test might be an idea. Currently I have three servers at my disposal and a dev site for testing. All servers are running CentOS 5 in a mixture of 32-bit and 64-bit installations, with Plesk 9.3/9.5 on them. Here’s my setup:

  • Server 1 (Hubert) hosts the content. This server can be defined as “localhost” in wp-config.php. We’ll make him a Database Slave later.
  • Server 2 (Drexel) is the Database Master.
  • Server 3 (Verslpzy) is another Database Slave.

Let’s replicate some data

Before we start, I want to make sure that I can test which server is chosen by HyperDB at any given time. The easiest way to do this is to replicate the current database to all servers using the following commands.

In a production environment you’d setup proper database replication for all databases on the master. In this case though, I’m only working with one replicated database and I don’t want it to be updated automatically – hence I’m going for a manual approach.

Let’s run the following command on Server 1:

mysqldump -u root -pyourpassword testdatabase > testdb.sql

Let’s move this file to Server 1

rsync root@server1.com:/testdb.sql /temp

Now we’ll pop it into an existing database of the same name with the same privileges – (I’ve already created this via phpMyAdmin becasue I’m lazy):

mysql -u root -pyourpassword testdatabase < testdb.sql

We’ll do the same on Server 3 so we have the exact database 3 times.

Identifying those databases

Now I need to write a post on my dev site stating which Database Server we’re looking at. All I need is a good headline really that says “Looking at Server 1”. So far so good.

Next we’ll edit the wp-config file and change “localhost” to our next Database Server (let’s say it’s server2.com – obviously it’ll be the IP address or domain of your server 2). Reload the front page of your dev site and magically that “Looking at Server 1” message has disappeared. Just what we wanted 😉

If at this point you get the “Error establishing Database Connection” it means some of your details in wp-config aren’t correct, or your replication didn’t work. Could be the replicated server hasn’t got the right privileges defined in MySQL. I won’t go into detail about that here though.

Let’s write a new post that says “Looking at Server 2”. Publish and refresh. Nice.

On to change the wp-config again to the third database server, and over to writing a new post entitled “Looking at Server 3”.

All we’ve done now is amend each single database slightly – and more importantly make this change visible on our front page.

Bring on HyperDB

Now comes the fun part: let’s bring in the star of the show.

Download HyperDB and unpack it. Upload db.php into your wp-content directory – this alone will activate it. It’s super simple steps that made WordPress great!

Let’s have a look at the db-config.php file before we upload it. It’s already pre-configured to work with whatever database host is configured in wp-config.php via this bit of code here:

$wpdb->add_database(array(
'host'     => DB_HOST,     // If port is other than 3306, use host:port.
'user'     => DB_USER,
'password' => DB_PASSWORD,
'name'     => DB_NAME,
));

Note that you can change these values to override the wp-config settings. This code defines that one server as database master/slave for both reading and writing. A bit further down, similar bit of code re-defines the server as a read-only slave:

$wpdb->add_database(array(
'host'     => DB_HOST,     // If port is other than 3306, use host:port.
'user'     => DB_USER,
'password' => DB_PASSWORD,
'name'     => DB_NAME,
'write'    => 0,
'read'     => 1,
'dataset'  => 'global',
'timeout'  => 0.2,
));

Because this is defined as an array, you can copy this bit of code for as many Database Slaves as you’d like to setup. All you have to do is amend the DB_HOST constant with your server’s domain name or IP address like so:

$wpdb->add_database(array(
'host'     => 'server2.com',     // If port is other than 3306, use host:port.
'user'     => DB_USER,
'password' => DB_PASSWORD,
'name'     => DB_NAME,
'write'    => 0,
'read'     => 1,
'dataset'  => 'global',
'timeout'  => 0.2,
));

or like so:

$wpdb->add_database(array(
'host'     => '192.168.0.101',     // If port is other than 3306, use host:port.
'user'     => DB_USER,
'password' => DB_PASSWORD,
'name'     => DB_NAME,
'write'    => 0,
'read'     => 1,
'dataset'  => 'global',
'timeout'  => 0.2,
));

Then just copy the same bit of code underneath and amend the host again – say like this:

$wpdb->add_database(array(
'host'     => '192.168.0.102',     // If port is other than 3306, use host:port.
'user'     => DB_USER,
'password' => DB_PASSWORD,
'name'     => DB_NAME,
'write'    => 0,
'read'     => 1,
'dataset'  => 'global',
'timeout'  => 0.2,
));

Save the file and upload it into the same folder that holds your wp-config.php file (as described above, db.php should be uploaded into the /wp-content/ folder).

Testing Testing… is this thing on?

If you now go back to your live site and refresh the home page, you should see that your latest post is different upon every refresh. Keep refreshing, and the “Looking at Server x” messages will just keep coming. We’ve just proven that HyperDB works a TREAT with minimal effort!

Once you’ve played that game for a while, you’ll want to see what would happen in an emergency. Say “Server 1” decides it’s time for a coffee break and crashes. We’ll simulate this by simply switching off the MySQL deamon with this command:

service mysqld stop

Go back and keep refreshing your site. “Looking at Server 1” shouldn’t come back anymore. You can do the same with the other servers for testing, obviously at least one must be active. The command

service mysqld start

should bring MySQL back online on any given server 😉

Conclusion

HyperDB works so well it’s scary!

Simple to implement and hyper functional. Of course you need to implement proper Database Replication for all servers to produce the same content, but that’s fairly easy to do (if you know how). Maybe we’ll find out in a forthcoming article 😉

What I’m pleased about is that WordPress works like magic with multiple database servers and minimal effort. I can’t wait to implement this on a bigger scale on all my sites.



If you enjoy my content, please consider supporting me on Ko-fi. In return you can browse this whole site without any pesky ads! More details here.

30 thoughts on “Testing HyperDB”

  1. Two websites on 5 servers. I guess where I am lost is I am not sure where in the config file you tell HyperDB which one the master is. I have the files all syncing with rsync from the master server over ssh and each set of files is using the same config file. Here are the settings, the privileges are all good, each server can connect to all of the others but someone told me they connected to the site and added a post but then it vanished which made me think I had this set up wrong. The first one is the master.

    **
    * This is the most basic way to add a server to HyperDB using only the
    * required parameters: host, user, password, name.
    * This adds the DB defined in wp-config.php as a read/write server for
    * the ‘global’ dataset. (Every table is in ‘global’ by default.)
    */
    $wpdb->add_database(array(
    ‘host’ => ‘xxx.xxx.xxx.23’, // MASTER DB
    ‘user’ => ‘some_un’,
    ‘password’ => ‘some_passwd’,
    ‘name’ => ‘some_db’,
    ‘write’ => 1,
    ‘read’ => 1,
    ‘dataset’ => ‘global’,
    ‘timeout’ => 0.2,
    ));

    /**
    * This adds the same server again, only this time it is configured as a slave.
    * The last three parameters are set to the defaults but are shown for clarity.
    */

    $wpdb->add_database(array(
    ‘host’ => ‘xxx.xxx.xxx.26’, // SLAVE DB.
    ‘user’ => ‘some_un’,
    ‘password’ => ‘some_passwd’,
    ‘name’ => ‘some_db’,
    ‘write’ => 0,
    ‘read’ => 1,
    ‘dataset’ => ‘global’,
    ‘timeout’ => 0.2,
    ));

    $wpdb->add_database(array(
    ‘host’ => ‘xxx.xxx.xxx.206’, // SLAVE DB.
    ‘user’ => ‘some_un’,
    ‘password’ => ‘some_passwd’,
    ‘name’ => ‘some_db’,
    ‘write’ => 0,
    ‘read’ => 1,
    ‘dataset’ => ‘global’,
    ‘timeout’ => 0.2,
    ));

    $wpdb->add_database(array(
    ‘host’ => ‘xxx.xxx.xxx.27’, // SLAVE DB.
    ‘user’ => ‘some_un’,
    ‘password’ => ‘some_passwd’,
    ‘name’ => ‘some_db’,
    ‘write’ => 0,
    ‘read’ => 1,
    ‘dataset’ => ‘global’,
    ‘timeout’ => 0.2,
    ));

    $wpdb->add_database(array(
    ‘host’ => ‘xxx.xxx.xxx.24’, // SLAVE DB.
    ‘user’ => ‘some_un’,
    ‘password’ => ‘some_passwd’,
    ‘name’ => ‘some_db’,
    ‘write’ => 0,
    ‘read’ => 1,
    ‘dataset’ => ‘global’,
    ‘timeout’ => 0.2,
    ));

    Does this look right to you? Also, this was a great post 🙂 Thanks so much 🙂

  2. Thanks Jon, I’m glad you liked the post 😉

    Ah I see what may have happened here: the master should also be setup as a slave so it can be read from. Your second server appears to have a diferent IP address than your master. Copy the second block and paste it underneath the master block, then change the IP address in this copied block to match the matser. That way, you can write a new post, then view it straight away (from the master). The slaves will take a moment to update so from a different machine the update may not be visible until all slaves are updated. But for the person writing a new post it should then be there immediately.

    Let me know if it works.

  3. it works, oh I am so happy 🙂 This has truly renewed my faith in WordPress as a scalable CMS thanks so much for your help I will certainly be following more of the posts here for sure!

  4. I have a question,
    I’m in shared hosting environment, can i still use it with single DB ? does it have any advantage for such condition ?

Leave a Comment!

This site uses Akismet to reduce spam. Learn how your comment data is processed.