HA with DRBD and/or Replication

[adrian]

Hi,

I’m looking into making a highly available, scalable MySQL database and, although I have looked at MySQL Cluster, I am not convinced it should be automatically considered as a solution. You’ll have to forgive me if I say something stupid - I’m new to HA…

I was thinking of using DRBD with heartbeat to provide a “hot standby” server. Heartbeat provides us with a nice failover procedure. I’m assuming (with no actual proof) that DRBD can’t scale out as readily as MySQL Replication. I could, of course, be entirely wrong with that assumption.

If we go with DRBD to provide one active server with one hot standby, I can see 2 ways to implement load balancing for performance:

1. We have slaves replicate from the active server, and then load balance across the read-only slaves…
2. Or, to reduce demands on the active server, have slaves replicate from the hot standby. Obviously, if the hot standby goes down, then it’s a simple task to get the slaves to start replicating from the master.

Is there another configuration I have missed?

If DRBD can scale out well, then obviously we can just chain together a bunch of servers and load balance across them, but that doesn’t feel right.

I’m ignoring (for now) the associated problems with replication (slaves are potentially out of sync etc) and I’m assuming that directing read-only queries to the slaves is taken care of…

Any suggestions you have would be appreciated.

Thanks for your time,

Adrian

— EDIT

Another option is to forget DRBD all together and just use replication to create our active/hot standby server but I like the simplicity/reliability that DRBD appears to present.

[debug]

Adrian,

Haven’t you looked at mysql-proxy?
It can help to have load-balanced highly-available system.

Mysql-proxy can do load balancing between several backends. Some of backends can be read-only (option --proxy-read-only-backend-addresses=host:port), and others can be read-write (option --proxy-backend-addresses=host:port).

So I see several usage cases:

1. you have two servers in master-master replication, both can be used for reads and writes (–proxy-backend-addresses=host1:port --proxy-backend-addresses=host2:port). If one of servers goes down, then mysql-proxy notices it, and sends queries only to another one.
2. You have two servers in master-slave replication (–proxy-backend-addresses=<master_host:port> --proxy-read-only-backend-addresses=<slave_host:port>)
   inserts and updates are being sent to master, and selects - to both master and slave (depending on number of connections on both servers). If master goes down - then you have to change slave server to act as stand-alone server).
3. More advanced structure - for example, two read-write servers, and two read-only(slaves). But I cannot advised something more exact, without knowing details more details.

[adrian]

Hi,

Thank you for your reply.

I knew of mysql-proxy, but I didn’t realise it was so advanced - automatically detecting when hosts are down and redirecting queries appropriately. I also didn’t realise you could nominate certain backends as “read-only”. Presumably, mysql-proxy sees that the query is a read-only query and can therefore distribute it across read-only backends… very nice.

One problem with using mysql-proxy and replication is that a read-only server (a slave) experiences a delay in receiving INSERTs. I can see situations where it’s imperative to avoid that delay, and go straight to the master - I assume that has to be written at the application level.

Replication with mysql-proxy could certainly provide a scalable solution, but master-master replication doesn’t satisfy our high availability requirements (because of the potential delay in executing the relay logs); hence the suggestion of DRBD.

However, after all this, I am still currently investigating MySQL Cluster as a solution. I haven’t had time to look at mysql-proxy/MM replication/DRBD…

Thank you for your time.