I have 2 database servers.  One server runs a script that updates about 8 million products.  This takes a long time   And I can’t use MySQL master/slave because the data is not production worthy until the script fully completes.  Instead of using Master/Slave, I push the binary files from database server 1 to database server 2 when the script completes.  So each of the databases will be turned off at some point.  Therefore, I need to use HAProxy to forward the requests to the working server when the other one is turned off.  And when the product script is not running and binary files are not being pushed, I want both of them running in the cluster.  So here is application flow:

web server requests -> haproxy -> database servers 1 & 2

In order to make this work we need HAProxy installed and we need a health check script for both databases, so HAProxy knows when one is not running (no requests will be forwarded to this server).  Here are the steps:

First I need to install haproxy on my web server (CentOS Linux), so that it can forward the requests to the 2 MySQL instances.  Go here:

http://haproxy.1wt.eu/#down

Download the latest stable source and make it.

You dont have to ./configure and make.  Just “make install”.  Next step is to set up a config file.  Most people choose /etc/haproxy.cf

Here is a what my final config looks like:

global
        log 127.0.0.1 daemon debug
        stats socket /tmp/stats
        maxconn 4096
        pidfile /var/run/haproxy.pid
        daemon
defaults
        log global
        mode tcp
        option dontlognull retries 3 option redispatch
        maxconn 2000
        contimeout 5000
        clitimeout 50000
        srvtimeout 50000
listen  MySQL XX.XX.XX.XX:3305
        mode tcp
        option  httpchk
        stats enable
        stats uri /haproxy-stats
        balance roundrobin
        server mysql_slave XX.XX.XX.XX:3306 check port 9200 inter 12000 rise 3 fall 3
        server mysql_slave XX.XX.XX.XX:3306 check port 9200 inter 12000 rise 3 fall 3

Of course, you need to replace XX.XX.XX.XX with your server IPs.  The first IP in the “listen” section is the IP address where HAProxy is installed.  The last 2 lines in this file are where you put the IP addresses for your database servers.

After your config is set up, you can start haproxy with this command:

Then use your SQL client (I am using SQLyog) to login to mysql using port 3305.  If you are able to login, then that means that HAProxy is listening on port 3305 and forwarding the requests to the slaves.

The next thing you need to do is add some health checks, so that if one of the slaves goes down, then HAProxy will stop forwarding requests to the non-working slave.  For this I referenced a tutorial written by Unai Rodriquez  - Having HAProxy check mysql status through a xinetd script Essentially, you just create a xinetd service that listens on port 9200 and returns an HTTP status 200 or 500 depending on the state of the MySQL server.  HAProxy stops forwarding requests if it sees a status 500.

You can test this by stopping and starting MySQL and watching http://yourhost/haproxy-stats.  This will show if the servers are up or down.  After you verify that it is working correctly, it is time to change your Ruby on Rails (or whatever framework you are using) configuration to use port 3305.  Deploy your code, restart your app server and you should have  a working mysql cluster.

Tags: clustering, HAProxy, load balancing, MySQL