What is HAProxy and how to use it?

HAProxy is a free, very fast and reliable solution offering high availability, load balancing, and proxying for TCP and HTTP-based applications. It is particularly suited for very high traffic web sites and powers quite a number of the world's most visited ones. Over the years it has become the de-facto standard opensource load balancer and is now shipped with most mainstream Linux distributions. Its most common use is to improve the performance and reliability of a server environment by distributing the workload across multiple servers (e.g. web, application, database).

In the example below we will configure one HAProxy server to load balance traffic to three back-end MySQL servers. We will configure a total of two MySQL users, one to perform a health check and verify services and another to access MySQL for data retrieval. HAProxy must also perform basic health checks on the database server. When enabled, HAProxy attempts to establish a connection with the node and parses its response or any errors to determine if the node is operational.

Steps to configure a HAProxy server

Step 1: Install the haproxy and MySQL client packges on the HAProxy server.

[root@localhost ~]# yum -y install haproxy mysql

Step 2: Run the setsebool utility to enable or disable Booleans. Booleans allow parts of SELinux policy to be changed at runtime to allow services access to use certain ports without reloading or recompiling the SELinux policy.

[root@localhost ~]# setsebool -P haproxy_connect_any=1

Step 3: Configure the haproxy.cfg file to include the below.

[root@localhost ~]# vi /etc/haproxy/haproxy.cfg

# Load Balancing for Galera Cluster
listen galera
    balance roundrobin
    mode tcp
    option tcpka
    option mysql-check user haproxy_check
    server mysql01 check weight 1
    server mysql02 check weight 1
    server mysql03 check weight 1

Step 4: Enable the haproxy service to start at boot and start immediately.

[root@localhost ~]# systemctl enable haproxy
[root@localhost ~]# systemctl start haproxy

Step 5: Uninstall the validate_password plugin and create two users for the HAProxy. The first user will be used by HAProxy to check the status of a server. The second MySQL user is needed with root privileges when accessing the MySQL cluster from HAProxy. Replace haproxy and password as you see fit and repeat on each MySQL node.

[root@localhost ~]# mysql -u root -p
MySQL [(none)]> uninstall plugin validate_password;
MySQL [(none)]> CREATE USER 'haproxy_check'@''; 
MySQL [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'haproxy_root'@'' IDENTIFIED BY 'password' WITH GRANT OPTION; 

NOTE: If using MariaDB and MySQL 5.6, user creations and permissions do not replicate to the cluster. Replication currently only works with the InnoDB and XtraDB storage engines. Multi-master replication cannot support non-transactional storage engines, such as MyISAM. The system tables use MyISAM. This means that any changes you make to the system tables directly, such as in the above example with an INSERT statement, remain on the node in which they were issued.

Verify the Configuration

Now that the configuration is finished lets verify our load balancing deployment. Run the below query a few times to verify load balancing is working. With each subsequent query the server_id should be load balanced between the three SQL nodes.

[root@localhost ~]# mysql -h -u haproxy_root -p -e "show variables like 'server_id'"

| Variable_name | Value |
| server_id     | 1     |

| Variable_name | Value |
| server_id     | 2     |

| Variable_name | Value |
| server_id     | 3     |

Related Posts

Cisco Networking

BGP Load Sharing

Load balancing with BGP is not possible in a multihomed environment with two ISPs. BGP selects only the single best path to a destination among the BGP paths that are learned from different ASs, which Read more…

Application Containers

Docker container management using Rancher

A container management platform is a solution used to o create cloud-native, distributed applications and package legacy applications that were not originally designed for virtual environments. Container management software simplifies the process of adding or Read more…


Install MySQL Galera Cluster on Centos 7

MySQL Galera Cluster is a synchronous multi-master cluster, available on Linux only, and only supports the XtraDB/InnoDB storage engines . It is designed to provide high availability and high throughput with low latency, while allowing Read more…