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 on 192.168.0.150:
$ ./s9s_haproxy --install -i 1 -n 192.168.0.101,192.168.0.102 -h192.168.0.150
Install HAProxy on 192.168.0.151:
$ ./s9s_haproxy --install -i 1 -n 192.168.0.101,192.168.0.102 -h192.168.0.151
Don’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 -P3307
You 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 -s
Add/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.104
Then 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.104
In 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.104
Remove the Load Balancer
If you for whatever reason want to remove HAProxy you can do:
$ ./s9s_haproxy --delete -i 1 -h 192.168.0.151
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