Haproxy mysql failover load balancing

Solution 1:

Remove the check port 9200s then your backup option should work. You seem to be mixing different examples, the mysql-check works on the standard port wheares there is another common example that uses an http check where they setup a xinetd process answering on port 9200 which runs a seperate check script.

Solution 2:

You have master-master replication working, but even so I don't think using HAProxy for MySQL load balancing is the right choice.

HAProxy is great, but when operating as a TCP level load balancer it cannot have any notion of the SQL state. Behavior with long-running SQL transactions is unclear and error-prone; this is something you shouldn't be happy with.

Your master-master setup has the write capacity of a single node (since all writes need to be duplicated). Thus what you are scaling up with your setup are the reads & connections. A more common and IMHO much better setup is:

1. Hang some read-only MySQL slaves from the master-master pair.
2. Change your application code to send all writes to the master, and almost all reads (where possible) to one of the read-only slaves.
3. Be smart about establishing connections to MySQL. Preferably re-use connections from a connection pool.

Something like MySQL proxy or other connection handling middleware might also work well in your case.

"High Performance MySQL" is a really good book with practical suggestions on how to scale MySQL. If you read this book I think you'll see more clearly which designs are common and proven for your specific situation.

Solution 3:

This config does exactly what you want :)

global
       log 127.0.0.1 local0
       maxconn 4096
       user haproxy
       group haproxy
       daemon

defaults
       log global
       mode tcp
       option tcplog
       option dontlognull
       retries 3
       option redispatch
       maxconn 2000
       contimeout 4000
       clitimeout 50000
       srvtimeout 30000
       stats enable
       stats scope .

frontend mysql_cluster
       bind 3.3.3.3:3307
       #bind *:3307
       default_backend mysql_cluster

backend mysql_cluster
       mode tcp
       option mysql-check
       balance roundrobin
       server db01_1.1.1.1       1.1.1.1:3306      weight 1        check port 3306
       server db02_2.2.2.2       2.2.2.2:3306      weight 100      check port 3306 backup


listen stats 3.3.3.3:10000
       mode http
       option httpclose
       balance roundrobin
       stats uri /
       stats realm Haproxy\ Statistics
       #stats auth user:pass

Solution 4:

For a true master-master MySQL cluster in combination with haproxy, try using codership Galera or percona XtraDB Cluster.