Okay… MySQL Cluster (the storage engine) kind of sucks IMO. It’s terribly annoying that you can’t alter the DB schema of anything running it (even more annoying is that you can’t alter the schema of a database that’s NOT using ndbcluster, but just exists in the same mysqld process). So I think I’m done with it (at least until they fix that and some other annoying thing).

So now I’m back to designing a MySQL Cluster using traditional storage engines (MyISAM and InnoDB). So let’s start with 4 DB servers and circular replication. (A -> B -> C -> D -> A). Okay… no problem there, especially now that MySQL has the following two variables (since 5.0.2) to prevent AUTO_INCREMENT collisions:

auto_increment_increment
auto_increment_offset

Okay, cool… just pipe MySQL client connections through the load balancers, and let it handle the failover/load balancing if needed.

But here’s the problem… if one of the servers fail, the replication chain is broken. For example if server B fails, C and D would never see anything that happened on server A. Not good.

So what about sending the replication network traffic through the load balancers as well? Then you could setup something like so: C replicates from B normally, but if B fails, then C replicates from A (automatically happening by routing replication traffic through load balancer).

Now I’m curious is if the load balancer network routing is fast enough to handle the interconnectivity of all the DB servers. I guess I’ll know soon enough (hopefully all the new equipment will be installed tomorrow or Friday).

Update

You know what would make everything really easy? If you could have more than 1 master server.