How to Setup HAProxy Load Balancer on Ubuntu 18.04 & 16.04

HAProxy is a very fast and reliable solution for high availability, load balancing, It supports TCP and HTTP-based applications. Nowadays maximizing Database up-time is very crucial for heavy traffic DB. This is not possible with single server setup. Then we need some high availability environment that can easily manage with single server failure.

Our Setup will be in top of the Galera cluster with mysql Galera Cluster and to achieve this you should allow the access permeation in the Galera Cluster server by using the below command , also you have to confirm mysql service is running all the service

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION

This article will help you to setup HAProxy load balancing environment on Ubuntu, Debian and LinuxMint. This will configure a Layer 4 Load Balancing (Transport Layer). Which will balance load and transfer requests to different-2 servers based on IP address and port numbers.

Network Details Below is our network server. There are 3 Galera running with mysql and listening on port 3306 and one HAProxy server.

Web Server Details: Server 1: galera-cluster-1 10.10.10.4 Server 2: galera-cluster-2 10.10.10.5 Server 3: galera-cluster-3 10.10.10.6

HAProxy Server: HAProxy: haproxy 10.10.10.7

Step 1 – Install HAProxy

Now start the setup. your HAProxy server as a privileged user and install HAProxy using following commands.

sudo add-apt-repository ppa:vbernat/haproxy-1.8
sudo apt-get update
sudo apt-get install haproxy

Step 2 – Configure HAProxy Load Balancing

Now edit haproxy default configuration file /etc/haproxy/haproxy.cfg and start configuration.

sudo vi /etc/haproxy/haproxy.cfg

Default Settings:

You will find some default configuration like below. If you don’t have enough idea about this, you can keep as it is.

global
	log /dev/log	local0
	log /dev/log	local1 notice
	chroot /var/lib/haproxy
	stats socket /run/haproxy/admin.sock mode 660 level admin
	stats timeout 30s
	user haproxy
	group haproxy
	daemon	# Default SSL material locations
	ca-base /etc/ssl/certs
	crt-base /etc/ssl/private	# Default ciphers to use on SSL-enabled listening sockets.
	# For more information, see ciphers(1SSL). This list is from:
	#  https://hynek.me/articles/hardening-your-web-servers-ssl-ciphers/
	ssl-default-bind-ciphers ECDH+AESGCM:DH+AESGCM:ECDH+AES256::RSA+AES:RSA+3DES:!aNULL:!MD5:!DSS
	ssl-default-bind-options no-sslv3defaults
	log	global
	mode	http
	option	httplog
	option	dontlognull
        timeout connect 5000
        timeout client  50000
        timeout server  50000
	errorfile 400 /etc/haproxy/errors/400.http
	errorfile 403 /etc/haproxy/errors/403.http
	errorfile 408 /etc/haproxy/errors/408.http
	errorfile 500 /etc/haproxy/errors/500.http
	errorfile 502 /etc/haproxy/errors/502.http
	errorfile 503 /etc/haproxy/errors/503.http
	errorfile 504 /etc/haproxy/errors/504.http

Adding HAProxy Listener: Now tell HAProxy to where to listen for new connections. As per below configuration HAProxy will list on port 3306 of 10.10.10.7 ip address.

listen galera
     bind 10.10.10.7:3306
     balance source
     mode tcp
     option tcpka
     server galera-cluster-1 10.10.20.4:3306 check weight 1
     server galera-cluster-2 10.10.20.5:3306 check weight 1
     server galera-cluster-3 10.10.20.6:3306 check weight 1

Step 3 – Final HAProxy Configuration File

The final configuration file may look like below:

global
  log /dev/log  local0
  log /dev/log  local1 notice
  chroot /var/lib/haproxy
  stats socket /run/haproxy/admin.sock mode 660 level admin
  stats timeout 30s
  user haproxy
  group haproxy
  daemon  # Default SSL material locations
  ca-base /etc/ssl/certs
  crt-base /etc/ssl/private 
  # Default ciphers to use on SSL-enabled listening sockets.
  # For more information, see ciphers(1SSL). This list is from:
  #  https://hynek.me/articles/hardening-your-web-servers-ssl-ciphers/
  ssl-default-bind-ciphers ECDH+AESGCM:DH+AESGCM:ECDH+AES256::RSA+AES:RSA+3DES:!aNULL:!MD5:!DSS
  ssl-default-bind-options no-sslv3

defaults
  log  global
  mode  http
  option  httplog
  option  dontlognull
  timeout connect 5000
  timeout client  50000
  timeout server  50000
  errorfile 400 /etc/haproxy/errors/400.http
  errorfile 403 /etc/haproxy/errors/403.http
  errorfile 408 /etc/haproxy/errors/408.http
  errorfile 500 /etc/haproxy/errors/500.http
  errorfile 502 /etc/haproxy/errors/502.http
  errorfile 503 /etc/haproxy/errors/503.http
  errorfile 504 /etc/haproxy/errors/504.http

listen galera
     bind 10.10.10.7:3306
     balance source
     mode tcp
     option tcpka
     server galera-cluster-1 10.10.20.4:3306 check weight 1
     server galera-cluster-2 10.10.20.5:3306 check weight 1
     server galera-cluster-3 10.10.20.6:3306 check weight 1

Step 4 – Restart HAProxy

Now you have made all necessary changes in your HAProxy server. Now verify configuration file before restarting service using the following command.

$ haproxy -c -f /etc/haproxy/haproxy.cfg

If above command returned output as configuration file is valid then restart HAProxy service

$ sudo systemctl restart haproxy

Step 5 – Verify HAProxy Setting

At this stage, we have a full functional HAProxy setup. At each server node. use the below command , So we can easily differentiate between servers .

mysql -uroot -p -h10.10.10.7 -e "show variables like 'wsrep_node_name' ;"

output at the first hit :

+-----------------+------------------+
| Variable_name  | Value |
+-----------------+------------------+
| wsrep_node_name | galara-cluster-1 |
+-----------------+------------------+
mysql -uroot -p -h10.10.10.7 -e "show variables like 'wsrep_node_name' ;"

output at the second hit :

Enter password: 
+-----------------+------------------+
| Variable_name  | Value |
+-----------------+------------------+
| wsrep_node_name | galera-cluster-2 |
+-----------------+------------------+
mysql -uroot -p -h10.10.10.7 -e "show variables like 'wsrep_node_name' ;"

output at the first hit :

Enter password: 
+-----------------+------------------+
| Variable_name  | Value |
+-----------------+------------------+
| wsrep_node_name | galera-cluster-3 |
+-----------------+------------------+