MySQL load balancing with HAProxy allows the users to load balance MySQL among the available database notes.

As part of our MySQL Support, Bobcares responds to all inquiries, big or small.

Let’s take a closer look at how to load balance MySQL using HAProxy.

Benefits of using HAProxy

The user will gain the following benefits by using HAProxy in the load balancer tier:

1. All applications access the cluster via one single IP address or hostname. The topology of the database cluster will be under the mask of HAProxy.
2. Load balancing MySQL connections among the available DB notes.
3. Without altering the applications, a user can remove or add database nodes.
4. HAProxy puts new connections in a queue after the MySQL database connection limit. Throttling database connection requests in this manner is a clever method to prevent overload.

MySQL Load Balancing with HAProxy

Consider that there are three master servers, two slave servers, and one HAProxy server as shown below.

HAProxy — haip
Master server - mip
Slave1 server - s1ip
Slave2 server - s2ip

Create users on MySQL servers

Create an HAProxy user first. The ‘haproxy-user, helps to determine whether the instances are running or not, the ‘haproxy-user will’. Almost a health checkup. Given that we are adding the user to a MySQL database, replication is ignoring this. Create the haproxy-user on each MySQL server. If the MySQL database were not ignored, the command would only be done on the master, and the user would need to be created on all slaves.

mysql -u root -pUSE mysql;INSERT INTO user (HOST, USER) VALUES('', 'haproxy_user');flush privileges;

Next, we have to  create haproxy_root We can use it to access the database from the HAProxy server and has the same degree of access as the root user. Although it is better to create another user, we can use root. Execute the commands on each MySQL server.

GRANT ALL PRIVILEGES ON *.* TO 'haproxy_root'@'' IDENTIFIED BY 'haproxy_root_pass' WITH GRANT OPTION;flush privileges;

Install MySQL-client on HAProxy server

To install MySQL- client on the Haproxy server, run the commandsudo apt-get install mysql-client. Confirm after installation by running the mysql command.

Install and Configure HAProxy

Firstly, install haproxy on the HAProxy serversudo apt-get install haproxy. It must be enabled because by default it is off. So, enabling it will allow it to start whenever the server is rebooted. Configure the file /etc/default/haproxy and type in ENABLED=1.

After that, we have to back up and edit or configure the existing configuration file as shown below.

sudo cp /etc/haproxy/haproxy.cfg{,.original}

The final configuration file and details will be shown below.

log 127.0.0.1 local0
chroot /var/lib/haproxy
maxconn 100
user haproxy
group haproxy

defaults
log global
option tcplog
retries 2
timeout client 30m
timeout connect 4s
timeout server 30m
timeout check 5s

listen stats
mode http
bind *:9201
stats enable
stats uri /stats
stats realm Strictly\Private
stats auth kanyi:test

listen mysql-cluster
bind :3306
mode tcp
option mysql-check user haproxy_user
balance roundrobin
server master 192.168.56.102:3306 check
server slave1 192.168.56.103:3306 check
server slave2 192.168.56.104:3306 check

Here, including the ‘listen stats’ enables viewing of the configuration or the setup on the browser. Use the Roundrobin algorithm and check the haproxy_user on MySQL-cluster and proceed to the servers.

After this process, make sure that the configuration is correct before resuming the HAProxy servicehaproxy -c -V -f /etc/haproxy/haproxy.cfg. If struck with an error, fix them until the files are completely valid. After this, restart the HAProxysudo service haproxy restart.

Testing Load Balancing and Fail-over

Do quick testing on the HAProxy server and look up the server-id. The results from all of the MySQL servers will display in a Roundrobin fashion. For example, as shown below.

mysql -h 127.0.0.1 -u haproxy_root -p -e "show variables like 'server_id'";

ragrant@haproxy:/vagrant$ mysql ch 127.0.0.1 -u haproxy_root -P -e "show variables like 'server_id'"; Enter password:
------+-------+
Variable_name Value 1
----------------+-------+
server_id 13
------+-----
ragrant@haproxy:/vagrant$ mysql -h 127.0.0.1 -u haproxy_root -P -e "show variables like 'server_id'"; Inter password:
------+-------+
Variable_name Value
--------+-------+
_server_id2
--------------+-------+
ragrant@haproxy:/vagrant$ mysql -h 127.0.0.1 fu haproxy_root -p -e "show variables like 'server_id""; Inter password:
-----+-------+
Variable_name Value |
-----------+-------+
server_id 13 |
------+-------+
ragrant@haproxy:/vagrant$ mysql -h 127.0.0.1 -u haproxy_root -p -e "show variables like 'server_id'"; Inter password:
------+-------+
Variable_name Value |
------+-------+
server_id | 1 |
----------+-------+
Fagrant@haproxy:/vagrant

This is the final step in the process, of MySQL load balancing with HAProxy.

[Need assistance with similar queries? We are here to help]

Conclusion

To conclude a user can easily set a load-balanced MySQL cluster with HAProxy by installing MySQL-client on an HAProxy server and configuring the HAProxy server.