haproxy mysql load balancing

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: 192.168.185.147
Mysql Master IP: 192.168.185.143
Mysql Slave IP: 192.168.185.145

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.

[[email protected] ~]# mysql -u root -p -e "INSERT INTO mysql.user (Host,User) values ('192.168.185.147','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.

[[email protected] ~]# mysql -u root -p -e "GRANT ALL PRIVILEGES ON *.* TO 'haproxy_check_rb'@'192.168.185.147' 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                | 127.0.0.1 |
| haproxy_check_rb    | 192.168.185.147 |
| haproxy_check       | 192.168.185.147 |
| slave_user          | 192.168.185.145 |
| root                | ::1 |
| root                | localhost |
+------------------+-----------------+
8 rows in set (0.01 sec)

HAProxy Configuration:

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

[[email protected] ~]# yum install mariadb -y

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

 [[email protected] ~]# yum install mysql-client -y

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

[[email protected] ~]# mysql -h 192.168.185.143 -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. 

[[email protected] ~]# cd /etc/haproxy/
[[email protected] haproxy]# cp haproxy.cfg haproxy.cfg-back
[[email protected] haproxy]# vim haproxy.cfg

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

#---------------------------------------------------------------------
# Global settings
#---------------------------------------------------------------------
global
# 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 127.0.0.1 local2

chroot /var/lib/haproxy
pidfile /var/run/haproxy.pid
maxconn 4000
user haproxy
group haproxy
daemon

# 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
#---------------------------------------------------------------------
defaults
mode http
log global
## option httplog
option dontlognull
option http-server-close
## option forwardfor except 127.0.0.0/8
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
    bind  0.0.0.0:3307
    mode http
    stats enable
    stats uri /
##   stats realm Strictly\ Private
    stats auth haproxy:yourpassword
listen mysql-cluster
bind 192.168.185.147:3306
## bind 127.0.0.1:3306
mode tcp
option mysql-check user haproxy_check
balance roundrobin
server mysql-master 192.168.185.143:3306 check
server mysql-slave 192.168.185.145:3306 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.

[[email protected] ~]# haproxy -c -f /etc/haproxy/haproxy.cfg
Configuration file is valid
[[email protected] ~]# 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. 

[[email protected] 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 [0.0.0.0:3306]
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.

[[email protected] ]# setsebool haproxy_connect_any on
[[email protected] ]# 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.

[[email protected] ~]# mysql -h 192.168.185.147 -u haproxy_check_rb -pyourpassword -e "show variables like 'server_id'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id        | 1 |
+---------------+-------+
[[email protected] ~]# mysql -h 192.168.185.147 -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

2 COMMENTS

LEAVE A REPLY

Please enter your comment!
Please enter your name here