HAProxy for MySQL Master-Slave Replication

Question

We are having MySQL MASTER-SLAVE Replication setup and everything is working fine.

Currently all load (reads/writes) are going to MASTER server. Our application is having 99% reads and 1% writes.

We thought of distributing load (only reads) to both Master and Slave. So we thought of using HAProxy to distribute the load to both MySQL servers.

Our requirement is all writes to be redirected to only Master server and reads to be distributed between Master and Slave servers.

Answer 1

I have implemented the same for my project. I have two DB Server ( DB01, DB02 ) behind the Ha-Proxy (LB01). I hit ha-proxy assuming a DB from my application. in my application I distributed the database queries as read on 3307 and write on 3306 port.

in haproxy.cfg (configuration file HAPROXY) two LISTENER as :

listen mysql-cluster
    bind  *:3306
    mode tcp
    balance roundrobin
    option mysql-check user mast_ha
    server DB01 10.x.x.x:3306 check maxconn 100000 


listen mysql-cluster-replica
    bind  *:3307
    mode tcp
    option mysql-check user mast_ha
    server DB02 10.x.x.x:3306 check maxconn 100000

And Distrubuted mysql call from application by making two jdbc template , one for read and another one for write.