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.
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.
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.
