MySQL Clustering

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.

4 thoughts on “MySQL Clustering”

  1. I’m thinking we just cluster your brain; by pass the intergalactic link and we be all good? Maybe not. Clones! Many clones is the answer.

  2. so … you figure how fast is the load balancer network routing or if this circular replication is a good solution for both failover and load balance concepts ?

    i asking because i am an beginner in mysql admin and i must come in the next month with a good failover solution for mysql …. i say good because in a few month after that maybe i need to transform it in a load balance with failover solution … because the web application is huge i don’t think that is time for converting it to mysql 5.x …. so i am stuck with mysql 4.1

    thank

  3. I’m reading your stuff on mysql clustering from last year.

    Dude, what are the limits to the clustering thing in mysql?
    And also, I don’t see any real change in the way the master slave thing works, just the way the data storage is clustered. you have to replicate between groups the same old way. Yes?

    At what point do you say ‘let’s just buy oracle’?

    Thanks.

Leave a Reply

Your email address will not be published.