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 10.1.0.1:3306
    balance roundrobin
    mode tcp
    option tcpka
    option mysql-check user haproxy_check
    server mysql01 10.1.0.11:3306 check weight 1
    server mysql02 10.1.0.12:3306 check weight 1
    server mysql03 10.1.0.13:3306 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'@'10.1.0.1'; 
MySQL [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'haproxy_root'@'10.1.0.1' IDENTIFIED BY 'password' WITH GRANT OPTION; 
MySQL [(none)]> FLUSH PRIVILEGES;

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

Leave a Reply

Your email address will not be published. Required fields are marked *

Related Posts

Cisco Networking

BGP Load Sharing

What is load sharing and why to use it?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 Read more…

Application Containers

Docker container management using Rancher

What is container management and why to use it?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 Read more…

CentOS

Install MySQL Galera Cluster on Centos 7

What is MySQL cluster and how to use it?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 Read more…