Comments on: Using HAProxy for MySQL failover and redundancy

By: Josh Brown

Josh Brown — Mon, 30 Nov 2009 22:42:14 +0000

Thanks Alex. Luckily both of those cases I can accommodate. I didn’t quite give my overall use case, but basically I will have two HA Proxy MySQL vips, one for writes (2 nodes DRBD cluster), and N for reads (1 for now as a MySQL replication slave using the methods you spoke of above). Then using the JDBC layer in the application we can redirect all writes to one vip and all reads to the other.

By: Alex

Alex — Sat, 28 Nov 2009 21:52:16 +0000

Hi Josh,

You have to be aware that DRBD has a limit of 2 nodes so that could be problematic if you want more than that. It’s great if you can deal with one server being “unuseable” all the time because the 2nd server basically acts as a failover only, so you’re not really load-balancing requests or distributing the load.

Finally, DRBD can be really useful when both your servers are on the same local LAN because the replication is block-level. It won’t do well over a WAN or unreliable VPN connection.

By: Josh Brown

Josh Brown — Thu, 26 Nov 2009 01:21:51 +0000

This is a great article. I am going to use a part of this, only instead of doing replication via MySQL Master/Slave, I am going to use DRBD with Heartbeat. I think DRBD/Hearbeat is a better choice because of the automatic reconciliation after a failure. Either way, love it.

By: Alex

Alex — Mon, 23 Nov 2009 23:26:21 +0000

Hi,

Thank you. Yes there is missing the “global” and “defaults” section of the haproxy configuration.

The config I posted is only an example of how to perform certain complex tasks and monitoring with haproxy. The ‘listen’ section can be split in two by using a ‘frontend’ and ‘backend’. This allows multiple frontends to forward to the same backend.

Although it’s outdated, the following document has some good standard configurations you can use to start from:

http://haproxy.1wt.eu/download/1.3/doc/haproxy-en.txt

By: macir

macir — Sat, 14 Nov 2009 12:09:10 +0000

Hi Alex,

thanx for this great document. I am newbie to haproxy. I am a bir confused, in other configuration there is “listen” section, but in your config there isn’t. And my haproxy starts without errors, but don’t work. Is there something incomplete in haproxy.cfg?

thanks again…

By: Alex Williams

Alex Williams — Thu, 12 Nov 2009 17:29:53 +0000

Laurent: I’m not familiar with SQL Relay, although it looks interesting. It seems to perform the same functionality as the “backend cluster_db_read” which I have configured, with a few extra options. I guess it really depends on your needs. I would be curious to see the performance impact of an additional layer of routing added between haproxy, the web, and db servers.

As for the replication check script, you can try this: http://forge.mysql.com/tools/tool.php?id=6

By: Laurent Spagnol

Laurent Spagnol — Fri, 06 Nov 2009 11:41:15 +0000

Hi,

Thanks for your great article.
I am working on a similary solution.

I’ll probably use “SQL Relay” as fronted, before HAproxy.
“SQL Relay” will be used for routing W request on “master pool” and R request on “slave pool”
http://sqlrelay.sourceforge.net/

(Replication use “master instance” of HAproxy)

What do you think about it ?

L.S.

By: David Cheng

David Cheng — Mon, 02 Nov 2009 22:35:34 +0000

Hello,

Could you publish mysqlchk_replication.sh script in your article?

Thanks a lot.

–David

By: Hervé COMMOWICK

Hervé COMMOWICK — Mon, 02 Nov 2009 11:45:55 +0000

Hello,
it seems it lacks the mysqlchk_replication.sh script in your article, can you provide it ?

Thanks in advance.

Hervé.

By: Istvan Podor

Istvan Podor — Wed, 09 Sep 2009 21:03:19 +0000

Alex: You are right. Actually I already recommended this to someone. So back to basics, great article :))

With mmm I use maatkit to verify, actually with multi-master i have “cronjob-like” daily/weekly report (replication could become an enemy.. :) ). I would try this too where I work now, but I just have no resources :(