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!
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!
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.
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.
We’ll do the same on Server 3 so we have the exact database 3 times.
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.
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.
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!
'host' => DB_HOST, // If port is other than 3306, use host:port.
'host' => 'server2.com', // If port is other than 3306, use host:port.
'host' => '192.168.0.101', // If port is other than 3306, use host:port.
'host' => '192.168.0.102', // If port is other than 3306, use host:port.
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!
HyperDB works so well it’s scary!
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.
how about when I have a very long database and it takes 20 seconds to load… what should I do?
Tough one. I’d say if your database is extremely large (we’re talking several GB) then HyperDB can indeed help – but you need to have two or more database servers at your disposal. You can then split the database into several tables per server.
I would personally try upgrading to better hosting as this will likely solve the problem.
1 > Do I need to install the application on the slave server?
3 > In which folder the wp-config should go in the slave machine if there is no need of installing the application on the slave? Will it go to the www directory. Where should I put the db.php file?
4 > Do I comment the master section info when I configure it for slave?
1+2 can be installed on the same server – but therefore 3 is not a web server and does not need a copy of WordPress, and hence you only have one configuration file. In this you need to tell WordPress about the two database servers.
I suppose today we wouldn’t really use HyperDB anymore. HyperDB was developed before affordable cloud hosting became a reality.
Infrastructure such as Amazon RDS makes our lives much easier: with RDS you can setup a single MySQL instance which scales and replicates automagically, across several continents. WordPress only speaks to one database server, and Amazon handles the rest.