MySQL Load Balancing using HAProxy

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.

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.

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

MySQL Load Balancing using HAProxy

Published by Joe Conklin on September 26, 2015

What is HAProxy and how to use it?

Steps to configure a HAProxy server

Verify the Configuration

Related Posts

Cisco Networking

BGP Load Sharing

Application Containers

Docker container management using Rancher

CentOS

Install MySQL Galera Cluster on Centos 7