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.

Answer 2

We have successfully implemented our DB architecture for a very intensive read/write application. We have one MASTER where all read/writes operations take place and 2 SLAVES (A, B) where all the reads take places. Usually, the master-slave replication assumes that reads go on slaves and all other things (reads and writes) on master. In other words, slaves balances the reads.

We put an HAProxy after the WebServer serving read request. The WebServer connects to slaves through the HAProxy. The HAProxy checks the status of the slaves and balances requests among master and slaves. For easy the configuration, we put the DB servers on separated LANs. The configuration of HAProxy is very simple: It is just enough to use the default configuration and change the listen statement. For example:

listen slaves 10.8.214.14:3306
    balance     roundrobin
    option tcpka
    mode tcp
    option mysql-check user haproxy
    server  master   10.8.214.12:3306   check weight 1
    server  slave1   10.8.214.11:3306   check weight 1
    server  slave2   10.8.214.13:3306   check weight 1

Remember also to enable haproxy at boot.

If you want to go a step ahead, you could integrate the monitoring of slaves status (SQL errors or synchronization problems) by using some script. Ha proxy can use whatever agent you want. Take a look here and here

If you have a very intensive write applications I suggest to use the right storage engine, like TokuDB which scales very well with database size.