RE: MySQL LB / Backup Config

Ben,

Thanks for the info. Yes I use heartbeat on my LBs but I guess I never considering using it on the MySQL servers, but it sounds like a good idea.

Brian

From: Ben Timby [mailto:btimby#gmail.com] Sent: Saturday, May 07, 2011 11:22 AM
To: Brian Carpio
Cc: haproxy#formilux.org
Subject: Re: MySQL LB / Backup Config

On Fri, May 6, 2011 at 5:41 PM, Brian Carpio <bcarpio#broadhop.com<mailto:bcarpio#broadhop.com>> wrote: Hi,

I have a very simple setup for doing load balancing for MySQL DBs.

listen mysql_proxy vip01:3306

        mode tcp
        option tcpka
        balance roundrobin
        server mysql01 mysql01:3306 weight 1 check inter 10s rise 1 fall 1
        server mysql02 mysql02:3306 weight 1 check inter 10s rise 1 fall 1 backup

I am using the backup option so that mysql02 ONLY begins to receive traffic if mysql01 is down. The problem with this however is that once mysql01 is back online it begins to receive traffic gain. I would like mysql02 to stay as the "primary" until mysql02 fails, so basically if mysql01 goes down mysql01 becomes "backup.

I didn't see much in the docs on how to do this, however i could have missed it

Brian, while HAProxy can load balance any protocol, my suggestion to you would be to look into Heartbeat to perform this task for you. It does not load balance like HAProxy, but allows a shared IP address to be migrated between your two nodes. Once you are using Heartbeat, you can adjust the "stickyness" of the MySQL resource to keep it from immediately failing back to the original primary node. For me, Heartbeat has worked very well with both MySQL and PostgreSQL. Not only can it migrate the IP address, but you can also put other scripts or services under it's control so that failing over can also toggle replication settings or anything else you need done.

I think in this case Heartbeat is the tool better suited for the job than HAProxy.

I personally use Heartbeat with the Pacemaker cluster resource manager. There are a ton of how-to articles for MySQL+Heartbeat out there. Received on 2011/05/07 19:49