Configure MySQL Load Balancing With HAProxy On Linux

Configure MySQL Load Balancing With HAProxy On Linux

We’re supposing you have already configured the Mysql-slave replication on Linux. We can configure HAProxy for MySQL database load balancing on Linux. In our scenario, we will have one HAProxy server and two MySQL servers that would behave like master and slave replicas.

If you don’t know, you may follow the above article. I will MariaDB in our case.

Check Out: How To Install and Configure Tor Browser On Ubuntu

HAProxy Server IP:
Mysql Master IP:
Mysql Slave IP:

All the machines should be reachable. Once you’re done with Master and Slave Mysql configuration. I will start to configure it to the HAProxy server.

Configure MySQL For HAProxy:

Now, we have to create a user for a health check the MySQL server on the HAProxy server.

[root@mysql-master ~]# mysql -u root -p -e "INSERT INTO mysql.user (Host,User) values ('','haproxy_check'); FLUSH PRIVILEGES;"
Enter password:

we will need to give the grant permission and create a user that will connect the MySQL server from the HAProxy server. This user can be used as a database load balancing.

[root@mysql-master ~]# mysql -u root -p -e "GRANT ALL PRIVILEGES ON *.* TO 'haproxy_check_rb'@'' IDENTIFIED BY 'yourpassword' WITH GRANT OPTION; FLUSH PRIVILEGES"
Enter password:

Check Out: Top 9 rm Useful Command With Examples On Linux

Login to the MySQL server and check the user-created with permissions.

MariaDB [(none)]> select user,host from mysql.user;
| user                | host |
| slave_user          | % |
| root                | |
| haproxy_check_rb    | |
| haproxy_check       | |
| slave_user          | |
| root                | ::1 |
| root                | localhost |
8 rows in set (0.01 sec)

HAProxy Configuration:

We need to install the MariaDB client on the HAProxy server. 

[root@haproxy-master ~]# yum install mariadb -y

If you have MySQL, you can install MySQL client using the below command.

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

Now we will check whether we’re able to access the database server from the HAProxy server. 

[root@haproxy-master ~]# mysql -h -u haproxy_check_rb -p
Enter password: 
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 29
Server version: 5.5.60-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> select @@hostname;
| @@hostname |
| mysql-master |
1 row in set (0.003 sec)

We can access the MySQL server using the user which we have created for HAProxy.

Check Out: Install Tomcat 10 Server Using Source Code On Linux 8

We will stop the master MySQL server service then check we’re able to login to the slave server. 

MariaDB [(none)]> select @@hostname;
| @@hostname |
| mysql-slave |
1 row in set (0.00 sec)

Everything is working perfectly with the MySQL server. Let’s start the HAProxy configuration for Load Balancing.

First, we will make a backup copy of an existing haproxy.conf file. 

[root@haproxy-master ~]# cd /etc/haproxy/
[root@haproxy-master haproxy]# cp haproxy.cfg haproxy.cfg-back
[root@haproxy-master haproxy]# vim haproxy.cfg

We have to start the configuration on the HAProxy server for MySQL load balancing.

# Global settings
# to have these messages end up in /var/log/haproxy.log you will
# need to:
# 1) configure syslog to accept network log events. This is done
# by adding the '-r' option to the SYSLOGD_OPTIONS in
# /etc/sysconfig/syslog
# 2) configure local2 events to go to the /var/log/haproxy.log
# file. A line like the following can be added to
# /etc/sysconfig/syslog
# local2.* /var/log/haproxy.log
log local2

chroot /var/lib/haproxy
pidfile /var/run/
maxconn 4000
user haproxy
group haproxy

# turn on stats unix socket
stats socket /var/lib/haproxy/stats

# utilize system-wide crypto-policies
ssl-default-bind-ciphers PROFILE=SYSTEM
ssl-default-server-ciphers PROFILE=SYSTEM

# common defaults that all the 'listen' and 'backend' sections will
# use if not designated in their block
mode http
log global
## option httplog
option dontlognull
option http-server-close
## option forwardfor except
option redispatch

retries 3
timeout http-request 10s
timeout queue 1m
timeout connect 3000
timeout client 6000
timeout server 6000
timeout http-keep-alive 10s
timeout check 10s
maxconn 3000

listen stats
    mode http
    stats enable
    stats uri /
##   stats realm Strictly\ Private
    stats auth haproxy:yourpassword
listen mysql-cluster
## bind
mode tcp
option mysql-check user haproxy_check
balance roundrobin
server mysql-master check
server mysql-slave check

Check Out: How To Configure Simple Notification Service(SNS) On AWS Cloud

Before running the command, we can check the configuration file using the below commands.

[root@haproxy-master ~]# haproxy -c -f /etc/haproxy/haproxy.cfg
Configuration file is valid
[root@haproxy-master ~]# systemctl restart haproxy

Once you configured the HAProxy to handle the MySQL request using the load balancer. you must be seeing the Bind socket error while starting the service. 

[root@haproxy-master haproxy]# systemctl status haproxy -l
● haproxy.service - HAProxy Load Balancer
Loaded: loaded (/usr/lib/systemd/system/haproxy.service; enabled; vendor preset: disabled)
Active: failed (Result: exit-code) since Fri 2020-04-17 11:06:35 EDT; 4s ago
Process: 5705 ExecStart=/usr/sbin/haproxy -Ws -f $CONFIG -p $PIDFILE (code=exited, status=1/FAILURE)
Process: 5704 ExecStartPre=/usr/sbin/haproxy -f $CONFIG -c -q (code=exited, status=0/SUCCESS)
Main PID: 5705 (code=exited, status=1/FAILURE)

Apr 17 11:06:35 haproxy-master systemd[1]: Starting HAProxy Load Balancer...
Apr 17 11:06:35 haproxy-master haproxy[5705]: [ALERT] 107/110635 (5705) : Starting proxy mysql-cluster: cannot bind socket []
Apr 17 11:06:35 haproxy-master systemd[1]: haproxy.service: Main process exited, code=exited, status=1/FAILURE
Apr 17 11:06:35 haproxy-master systemd[1]: haproxy.service: Failed with result 'exit-code'.
Apr 17 11:06:35 haproxy-master systemd[1]: Failed to start HAProxy Load Balancer.

So, it can be because of SELinux enabled on the machine or you can run the below command then restart the service. it should work.

[root@haproxy-master ]# setsebool haproxy_connect_any on
[root@haproxy-master ]# systemctl restart haproxy

Check Out: How To Install Podman and Run Container On Linux 8

Test the Configuration on HAProxy Server:

We need to test the load balancing on the HAProxy server using the below commands. we will use HAProxy server IP with user and password.

[root@haproxy-master ~]# mysql -h -u haproxy_check_rb -pyourpassword -e "show variables like 'server_id'"
| Variable_name | Value |
| server_id        | 1 |
[root@haproxy-master ~]# mysql -h -u haproxy_check_rb -pyourpassword -e "show variables like 'server_id'"
| Variable_name | Value |
| server_id       | 2 |

Now, it’s working perfectly. we’re able to access both MySQL server from HAProxy server using the load balancing worked.

We also enable the monitoring of HAProxy using the stats. you just need to hist the URL: haproxyip:3307

Enter the username and password defined in the configuration file of haproxy.cfg, you can see the stats.

You’re done

Share on:

I'm the founder of Curious Viral. I hope this blog will provide you complete information about Linux Technology & I would like to share my technical knowledge with you which I have learned during this period.

Other Posts You May Like...

2 thoughts on “Configure MySQL Load Balancing With HAProxy On Linux”

Leave a comment