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 |
+-----------------+------------------+