Mysql – Need help achieving HAProxy active/passive configuration for thesql

high-availabilityMySQL

I'm using HAProxy for mysql database backends. The database had to be master-slave. The goal is to point all mysql clients at the proxy and for all requests to go to mysql server #1 and only go to mysql server #2 if #1 is down.

There is an understanding and acceptance that when we are failed over to node 2, the database will be read only.

One thing I tried was setting the weight of node 1 to 256 and this kind of works but I don't want one out of 256 database calls going to node2; I need all calls to get directed to node1 unless node1 is actually down.

Best Answer

The answer is to use the check backup option to second backend node in the conf file like so:

listen mysql-cluster bind 0.0.0.0:3306 mode tcp option mysql-check user haproxy_check balance roundrobin server mysql-1 10.51.120.28:3306 check server mysql-2 10.51.123.25:3306 check backup

OBSERVATION #1

You have max_connect_errors to 1000000 (One Million). If you ever reach that many consecutive connection failures, you would simply run FLUSH HOSTS; to clear up blocked connections. Given the high value of that setting, you will never need to run FLUSH HOSTS;

OBSERVATION #2

I am glad you mentioned your TIME_WAITs. I wrote this post in ServerFault back on Feb 01, 2012 : MySQL lowering wait_timeout value to lower number of open connections. This is a bit of a hack but it is a necessary evil in this instance.

In short, just run this on every PXC node

SEC_TO_TIMEWAIT=1
echo ${SEC_TO_TIMEWAIT} > /proc/sys/net/ipv4/tcp_tw_recycle
echo ${SEC_TO_TIMEWAIT} > /proc/sys/net/ipv4/tcp_tw_reuse

This will make TIME_WAITs timeout in 1 second. You have to run these lines every time a node reboots.

I got this idea from this post: How to forcibly close a socket in TIME_WAIT?
The accepted answer has a pictorial representation of when a TIME_WAIT comes into existence.
The answer with the idea that I liked is the one I am now suggesting.

MySQL – Design for Active/Passive Master-Master Setup

I will start with only 2 server with an active/passive master-master setup and a loadbalancer in front. I don't see any benifits for the cluster and I don't want a 3rd node or the active master waiting for any other nodes.

Best Answer

Related Solutions

Mysql – Percona Cluster Failed with Haproxy : Connection Limit Exceeded

OBSERVATION #1

OBSERVATION #2

MySQL – Design for Active/Passive Master-Master Setup

Related Question