blog
Using HAProxy with Your Database Cluster
Severalnines s9s_haproxy script allows you to install HAProxy and loadbalance over the MySQL servers in your cluster. To learn more about HAProxy and SQL load balancing, see this HAProxy Tutorial.
By defauly s9s_haproxy installs HAProxy on a server of your choice, most likely an application server, and then creates a loadbalancing set including all MySQL servers in the cluster.
** Updated on 10th June 2013: s9s_haproxy script has been upgraded to support installation of Keepalived.
But in some cases you want to loadbalance only on a subset of the servers. E.g, if you have a Galera cluster, consisting of 3 servers, you want to loadbalance your application load on two of the servers, and run for example administrative queries, like reporting or analytical queries, on the third one.
Consider you have the following servers:
- 192.168.0.100 (clustercontrol)
- 192.168.0.150 (app_server_1)
- 192.168.0.151 (app_server_2)
- 192.168.0.101 (db_server_1 – galera)
- 192.168.0.102 (db_server_2 – galera)
- 192.168.0.103 (db_server_3 – galera)
We want to loadbalance the requests from the app servers on db_server_1 and db_server_2. db_server_3 will be used for reporting.
This is possible to accomplish with s9s_haproxy. But first you need to get the latest release of it. All commands listed below must be executed on the ClusterControl server:
$ git clone https://github.com/severalnines/s9s-admin.git
$ cd s9s-admin/cluster/
Install HAproxy
Install HAProxy on 192.168.0.150:
$ ./s9s_haproxy --install -i 1 -n 192.168.0.101,192.168.0.102 -h192.168.0.150Install HAProxy on 192.168.0.151:
$ ./s9s_haproxy --install -i 1 -n 192.168.0.101,192.168.0.102 -h192.168.0.151Don’t forget to connect your applications to the loadbalancer port (33306 by default), on the application servers. If you want to use another listening port for the HAProxy than 33306, then specify -P :
$ ./s9s_haproxy --install -i 1 -n 192.168.0.101,192.168.0.102 -h192.168.0.151 -P3307You are recommended to not use 3306 in order to avoid potential conflicts with MySQL server (which use port 3306 by default).
If you have problems installing HAProxy, it is most likely because your repositories are using a too old version. Try to install it from source with -s flag, as examples below:
$ ./s9s_haproxy --install -i 1 -n 192.168.0.101,192.168.0.102 -h192.168.0.150 -s
$ ./s9s_haproxy --install -i 1 -n 192.168.0.101,192.168.0.102 -h192.168.0.151 -sAdd/Remove Nodes to the Load Balancer
Let’s say we need to scale out the cluster by adding another database server, 192.168.0.104 (db_server_4). We want this db server to be in the loadbalancer set as well. First we update the HAProxy on 192.168.0.150 to support the new server:
$ ./s9s_haproxy --add-node -i 1 -h 192.168.0.150 -n 192.168.0.104Then we need to update the HAProxy on 192.168.0.151:
$ ./s9s_haproxy --add-node -i 1 -h 192.168.0.151 -n 192.168.0.104In a similar way, you can later on remove the node from the loadbalancer:
$ ./s9s_haproxy --remove-node -i 1 -h 192.168.0.151 -n 192.168.0.104Remove the Load Balancer
If you for whatever reason want to remove HAProxy you can do:
$ ./s9s_haproxy --delete -i 1 -h 192.168.0.151Install Keepalived
To install Keepalived on both HAProxy nodes, with 192.168.0.150 become master to hold virtual IP (192.168.0.200) while 192.168.0.151 act as backup:
$ ./s9s_haproxy --install-keepalived -i 1 -x 192.168.0.150 -y 192.168.0.151 -v 192.168.0.200
