How to deploy HyperDB

Ever since I’ve started experimenting with HyperDB I thought that once I’ve got enough servers at my disposal, and if I ever figure out how to setup MySQL replication I’d bring the two together. I believe that day has finally come: HyperDB is now deployed across over 60 sites I’m taking care of.

I want to share with you my configuration and some of the pitfalls I’ve come across – if it helps, great. But mainly these notes are for me to remember how I did it when the next cluster needs to be built.

My Setup

Currently I’m hosting sites with one main server running CentOS 6 and Plesk 10.4 – that’s for web and email. On another server I’m hosting my databases. This server is also running Plesk which makes replication a little bit more difficult.

I want to build a “cluster” of 4 database servers in total, two masters which replicate each other and can be used for read and write queries, and two slaves which replicate one master each. These are for read queries. Thanks to HyperDB I can decide whether I want to use my masters for write only and leave the read queries to my slaves. I could even expand on those slaves later if I wanted to.

I have considered circular multi-master replication, but that’ just giving me a headache. Imagine one going down, then all of them are out of sync. It’s got MESSY written all over. If I need more masters later, I’d rather build another cluster altogether.

 At the time of writing I’m dealing with WordPress 3.3.1 and HyperDB 1.1. MySQL on my system is at version 5.1 for the masters and 5.5 for the slaves. I know this sounds dangerous, and 5.5 has some additional configuration options in my.cnf, as well as an additional database (performance_schema). But testing shows that these replicate well despite the different versions.

Before we begin

HyperDB is great, but it DOES NOT replicate your servers for you. You need to do this yourself. Once replication is in place, HyperDB will work its magic – but it won’t build a cluster for you.

First we need to setup two multi-masters, so two servers need to replicate onto each other. I’ve written how to do this in another article. Once that’s done we need to introduce one slave to Master 1, so technically Master 1 replicates onto two machines. Master 2 will currently only replicate onto Master 1 because we’ll re-introduce our current standalone database server as Slave 2. More about that later.

To test if this is working, I like having 4 SSH windows open at a time. Three of them are logged into my database servers, and one of them is an ad-hoc. This is what it looks like:

You can never have enough SSH Terminals open. I use the standard Mac Terminal app for this

I like creating a database on one of the servers and check the other ones to make sure it’s been replicated properly. You do this with three easy MySQL commands:

These create, show all and delete database “aaa”. In an ideal world, you can create on Master 1, it should show up on both Master 2 and Slave 1. You should also be able to delete the same database from Master 2 and it should vanish from Master 1 and Slave 1.

Beware: if only ONE mistake happens, the replication on the problematic server will immediately stop replication until you fix the issue. By “mistake” I mean that if you’re dropping a database that the slave doesn’t know for example, or changing a value which does not exist. The only remedy in such cases is to stop the slave, pointing it to the synced master log position and then starting it again.

While you’re testing you should also emulate what happens when a server goes down. So switch Master 1 off by using the following shell command:

Then create a database on Master 2 and switch Master 1 back on via

In an ideal world Master 1 should show your database. Slave 1 on the other hand may need another minute to catch up – depending on how often you’ve told him to reconnect to the master. Mine is set to re-connect once every minute, so grab a coffee and check again. Do it the other way round too so you’re absolutely certain that things replicate even when a server can’t be reached.

Jay is the CEO and founder of WP Hosting, a boutique style managed WordPress hosting and support service. He has been working with Plesk since version 9 and is a qualified Parallels Automation Professional. In his spare time he likes to develop iOS apps and WordPress plugins, or draw on tablet devices. He blogs about his coding journey at http://wpguru.co.uk and http://pinkstone.co.uk.

7 thoughts on “How to deploy HyperDB

  1. UPDATE:

    It’s been three weeks since I’ve had HyperDB running in the above sample configuration on about 6 test sites. The concept works, albeit with minor performance decreases over a standard dual-server setup. However I’ve run into a couple of problems twice over the three weeks: MySQL database inconsistencies.

    MySQL just crashes every once in a while. Sad but true – and I don’t know why, I’m just the observer here. And it’s no biggie either, you just go and reboot the service. This means that one of the slaves takes about a minute to catch up with the master. No problem here either.

    But as soon as one server is out of sync with the others the real nightmare begins: say server 1 recorded a change before server 2 crashes. Server 2 reboots and applies the changes one cycle too late so he misses creating a database table for example. Now you delete said table on server 1… Server 2 tries to apply this change, finds that this table doesn’t exist and throws out an error – and by the looks of it stops replicating altogether. That’s extremely ungood!

    It means you have to play catch-up with every server and set the master log coordinates manually again. This can get ugly, and I had to do this twice. I’m sure there’s another way to avoid this, but sadly I don’t know how at this moment.

    There is a project which may help called MMM (or MySQL Multi Master Replication Manager): http://mysql-mmm.org/ – looks extremely complicated to setup so I’m going to leave this for another long winter night.

  2. Hi, why do you 2 commands to add Master 1 for write queries and read queries? Why don’t use combine them into 1 command with (read=>1, write=>1) instead of (read=>1, write=>0) and (read=>0, write=>1)

    1. Both work fine for the example, but in a production environment it is plausible to have a master purely for writes and never for reads (and let the slaves do that) hence I split it up. It’s just for clarity really.

Add your voice!