Replication Integrity and Automatic Master Promotion

We’ve been working on automatic master promotion for MySQL using lbpool and I’m reminded of a few ideas we had after reading about MMM.

The MySQL Master-Master replication (often in active-passive mode) is popular pattern used by many companies using MySQL for scale out. Most of the companies would have some internal scripts to handle things as automatic fallback and slave cloning but no Open Source solution was made available.

Few months ago we were asked to implement such solution for one of the customers and they kindly agreed to let us release things under GPL2 License, and we gave them reduced rate for being Open Source friendly.

We actually considered a dual master approach but abandoned because it was non ideal.

As far as master availability goes it seems like a solid solution and I certainly don’t blame Peter for heading in this direction.

It has a few flaws though:

* It requires extra hardware thats sitting in standby and not being used (more money and higher capital expenditures)

* There’s only one machine to act as the standby master. If you have 10 slaves you should be able to fail five times and still be operational.

The trick though is trying to figure out HOW a master should be promoted from a slave. It’s not easy.

lbpool 2.0

The next generation of lbpool will do the following:

* implement Paxos so the clients can do master promotion themselves without any central coordination. With Paxos they can reach consensus that the master has failed and promote a new one.

* When a master fails one of the lbpool clients will take control and update all the existing slaves to start reading from the binary replication log of the new master.

The only way you can safely do this though is to use a trick with MySQL replication.

You tell MySQL to have *every* MySQL box read from the same replication position.

You can even tell MySQL to replicate from the same positions internally so this is pretty easy to setup.

Then if a master fails you can tell the other slaves to read from the same position in the new master.

Replication Checksums

Now that all the slaves are using the same replication offsets you can implement a pretty interesting feature.

MySQL supports a CHECKSUM option when creating tables. There’s a performance hit but since the CPU on most DB servers is essentially idle you should have plenty of headroom.

The MySQL protocol can now be extended to include the resulting checksum for that table on every statement. If the checksum differs when it makes it to the slave we know that the data is corrupt on the slave.

Right now MySQL can become corrupt if you were to do an UPDATE TABLE FOO SET BAR = 0 on a slave when that command isn’t run on the master.

  1. I’m curious about a few of the things you wrote. I thought table checksums were only for MyISAM tables, and I didn’t know about checking the checksum on the slave. Are these new features I don’t know about?

  2. Hey.

    Yes. Checksum is only for MyISAM. Good point. I should have pointed that out

    Also… this feature doesn’t exist in mysql replication. I’m just saying it should :)


  3. benjamin

    having five read slaves does me very little good if my app is knocking the master’s dick in the dirt with writes.

    m-m replication grants access to more CPU and IO to handle those writes, and enables scaling out with multiple replication clusters:

    S S S S S S

    granted, most apps are read-heavy and do not grow to the point where this is called for, but some do, and some are also write-heavy right from the start.

  4. Benjamin.

    I’m only talking about one facet here – master promotion. I’m NOT talking about sharding. MMM has sharding but so do we. I’m specifically talking about how to handle the case when a master fails. That master can be in a federation if necessary.


%d bloggers like this: