Setting up HAProxy

Severalnines has created deployment scripts for HAProxy. These are available on GitHub.

The scripts work with any Galera or MySQL Cluster setup that has been deployed using the Severalnines Configurator.

The HAProxy scripts should be cloned into the install directory of the existing deployment package on the ClusterControl server. (The deployment package generated at www.severalnines.com/config). The HAProxy deploy command is executed from the ClusterControl server to install HAProxy on any host.

E.g., the following installs HAProxy on host 10.176.131.76, run the command from the Controller:

git clone git://github.com/severalnines/s9s-admin.git
cd s9s-admin/cluster
./s9s-haproxy --install -i 1 -h 10.176.131.76

By default, the HAProxy server will listen for connections on port 33306. In the example above, you can connect your application server(s) to 10.176.131.76:33306, and requests will be load balanced on the MySQL servers.

Do not forget to GRANT access from the HAProxy server to the MySQL Servers, because the MySQL Servers will see the HAProxy making the connections, not the Application server(s) itself. In the example above, issue on the MySQL Servers the access rights you wish:

GRANT select ON mydb.* TO 'appuser'@'10.176.31.76' IDENTIFIED BY 'password';

As discussed above, the HAProxy instance will be automatically setup to load-balance on the Galera nodes and perform health checks to direct traffic to fully functional Galera nodes. The install script also tunes the TCP stack of the instance where HAProxy is installed. You can install HAProxy on dedicated hosts, or collocate an HAProxy instance with each application server.

To install on MySQL Cluster you do on the Controller:

git clone git://github.com/severalnines/s9s-admin.git
cd s9s-admin/cluster
./s9s-haproxy --install -i 1 -h 10.176.131.76

Example Setup:

Consider you have three application servers and are using Galera on debian/ubuntu:

appserver01
appserver02
appserver03

We have Galera nodes on:

db01
db02
db03

And a ClusterControl server on:

cc01

Requirements:

You must be able to ssh without passwords to appserver01, appserver02, appserver03
You can use:
shared-ssh-keys.sh appserver0x
to setup password-less ssh to appserver0x
If you are not running as 'root', then the user must be able to do 'sudo' without typing a password on appserver0x.
See http://support.severalnines.com/entries/20614858-server-requirements-on-premise-amis-other-images how to setup password-less 'sudo'

We will now install HAProxy on each of the app servers. On cc01 (ClusterControl server) do;

git clone git://github.com/severalnines/s9s-admin.git
cd s9s-admin/cluster
./s9s-haproxy --install -i 1 -h appserver01 
./s9s-haproxy --install -i 1 -h appserver02
./s9s-haproxy --install -i 1 -h appserver03

You then have to GRANT access from appserver0x to the MySQL Servers. On Galera, you only need to GRANT on one server:

mysql -ucmon -p -hdb01 
mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'appuser'@'appserver01' IDENTIFIED BY 'apppassword'
mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'appuser'@'appserver02' IDENTIFIED BY 'apppassword'
mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'appuser'@'appserver03' IDENTIFIED BY 'apppassword'

You applications on the each of the appserver0x can now connect to the database using the following settings on the db connection:

host: 127.0.0.1
port: 33306
user: appuser
password: apppassword

Troubleshooting

http://support.severalnines.com/entries/22270622-HAProxy-Troubleshooting

Graham Green

October 31, 2012 23:14

One suggestion to this would be to add redundancy to this configuration by also configuring a 2nd HAProxy server for failover.

Tim Thompson

February 05, 2013 15:50

If your databases are behind a firewall, then you will have to open up a few things for this to work. One is ICMP Ping. Then 2 TCP ports, 3306 for mysql, and 9200 for Haproxy to run/query the scripts that are installed on the Galera/Mysql servers.

David Majchrzak

March 04, 2013 15:18

Seems that HAProxy is no longer in the Epel Repository, so the script fails when installing haproxy.

March 04, 2013 15:32

Workaround is to use official centos6 CR repository on the app servers/haproxy servers:

yum install centos-release-cr

Then edit: /etc/yum.repos.d/CentOS-CR.repo and set it enabled=0. We don't want all the latest unstable fixes.

You need to update the install-haproxy.sh script with from:

case $OS in
rhel|redhat)
PKG_MGR="yum install -y "

case $OS in
rhel|redhat)
PKG_MGR="yum install -y --enablerepo=cr "

Johan

March 04, 2013 15:38

Hi David,

Many thanks for this! We will incorporate your suggestions soon.

Best regards

March 04, 2013 16:43

Hi Daniel,

https://github.com/severalnines/haproxy

has been updated with your proposed changes. Thanks again.

Pablob

February 10, 2014 22:03

Hi, I have already installed cluster control with galera and two haproxies with a keepalive, but I need to install SSL on the haproxy, and current clustercontrol haproxy is 1.4 which I found out do not supports SSL implementation.

How could I update the script at the clustercontrol to instal the 1.5 version instead of the 1.4?

Thanks.

Ashraf Sharif

February 11, 2014 04:50

Hi Pablob,

Haproxy version 1.5 is yet to be released on stable line. But of course you can install/recompile to the version that you want. Just make sure to maintain the same path to the Haproxy binary and ClusterControl should able to provision the process (as listed in ClusterControl > Manage > Processes).

Regards,
Ashraf

February 11, 2014 06:53

Pablob,

In the s9s_haproxy script (installed in /usr/bin if you used the Configurator) on the controller you can change:

HAPROXY_SRC="haproxy-1.4.23.tar.gz"

to specify whatever version you want to use. However, 1.5 may require that you install ssl packages for devel, you may have to update the scripts more.

One 1.5 is released stable we will update the s9s_haproxy script.

johan

Setting up HAProxy

Example Setup:

Troubleshooting

Comments

Didn't find what you were looking for?