Using haproxy

Haproxy can add a useful layer, allowing to balance and failover the connection between two mysql servers. This is a client provided configuration file:

1. pxdbproxy haproxy config

global

       log 127.0.0.1 local2
       chroot /var/lib/haproxy
       pidfile /var/run/haproxy.pid
       maxconn 4000
       user haproxy
       group haproxy
       daemon
       stats socket /var/lib/haproxy/stats

defaults

       mode tcp
       log global
       option tcplog
       option dontlognull
       option logasap
       option http-server-close
       option redispatch
       retries 3
       timeout connect 1s
       timeout client 10s
       timeout server 10s

2. BEGIN local mysql proxy definition

listen mysql-proxy

       bind 127.0.0.1:3306
       balance roundrobin
       option httpchk
       option tcpka
       default-server port 3307 inter 2s downinter 5s rise 3 fall 2 slowstart 60s
       server pbx-db01 172.24.9.99:3306 check
       server pbx-db02 172.24.9.100:3306 check backup
       timeout client 30m
       timeout server 30m