Problems with HAProxy as load balancer and 2 nodes on leastcon / roundrobin

MySQL & MariaDB Percona XtraDB Cluster 5.x
1

Hey everyone,

i am having a very unusual problem which caused me a split brain lol.

Following is the scenario:

1 HAProxy as load balancer with high availability and 2 DB Nodes

HA Proxy:

eth0: 192.168.10.20 (default gateway) 192.168.10.1
eth1: 192.168.1.20 (Application connects from this DMZ interface)
eth2: 10.10.10.20

HAProxy is being listened on all the ports although i tried binding it to an interface IP address as well but no luck

I followed [url]http://www.mysqlperformanceblog.com/2012/06/20/percona-xtradb-cluster-reference-architecture-with-haproxy/[/url] to configure

Following is my HAProxy cfg

global
log 127.0.0.1 local0
log 127.0.0.1 local1 notice
maxconn 4096
chroot /usr/share/haproxy
user root
group root
daemon
defaults
log global
mode http
option tcplog
option dontlognull
retries 3
option redispatch
maxconn 2000
contimeout 5000
clitimeout 50000
srvtimeout 50000
frontend pxc-front
bind *:3307
mode tcp
default_backend pxc-back
frontend stats-front
bind *:8080
mode http
default_backend stats-back
frontend pxc-onenode-front
bind *:33306
mode tcp
default_backend pxc-onenode-back
backend pxc-back
#source 0.0.0.0 usesrc clientip
mode tcp
balance leastconn
option httpchk
server c1 192.168.10.11:3306 check port 9200 inter 12000 rise 3 fall 3
server c2 192.168.10.12:3306 check port 9200 inter 12000 rise 3 fall 3
server c3 192.168.10.13:3306 check port 9200 inter 12000 rise 3 fall 3
backend stats-back
mode http
balance roundrobin
stats uri /haproxy/stats
stats auth pxcstats:secret
backend pxc-onenode-back
mode tcp
balance leastconn
option httpchk
server c1 192.168.10.11:3306 check port 9200 inter 12000 rise 3 fall 3
server c2 192.168.10.12:3306 check port 9200 inter 12000 rise 3 fall 3 backup
server c3 192.168.10.13:3306 check port 9200 inter 12000 rise 3 fall 3 backup

Now, the application server which has IP: 192.168.1.50 when it tries to connect to 3307 port of 192.168.1.20 i get connected with the user that had created earlier. It does get connected but when i check on the mysql console ‘\s’ it tells me that i am connected to 192.168.1.20 via TCP thats good but it shows me root@192.168.1.20.

This should not be case, it should show me root@192.168.1.50 since have many user accounts with same name and having different passwords on other hosts. This also creates problem of alot of open connections and tables on mysql servers which is not good.

Connections from clients are returned as if they coming from the load balancer

If i try to dump the database to check load balancing that works perfect.

Then i googled to find a solution and heard for HAPROXY with TPROXY with a kernel patch and iptables packet marking with a new chain. Then decided to compile haproxy with tproxy support since kernel 3.x already has tproxy enabled and asked to add “source 0.0.0.0 usesrc clientip” to the frontend but didn’t made any difference :frowning:

Also used GLB but no luck

Has anyone faced this issue before? Or knows the fix for this ?

Thanks.