Introduction to HAPROXY:
HAProxy, or High Availability Proxy, is an open-source load balancer and proxy server for TCP and HTTP applications. It’s well-known for its performance, dependability, and vast feature set. HAProxy is an essential component of current web designs, guaranteeing high availability, scalability, and effective distribution of client requests across server resources.
| HAPROXY Features | Use Cases of HAProxy |
Importance of HAProxy:
- Load Balancing: HAProxy effectively divides incoming network traffic over several backend servers. This ensures that no one server becomes a bottleneck, resulting in effective resource use.
- Various Algorithms: Supports a variety of load balancing methods, including round-robin, least connections, and source IP hash, allowing for specialized traffic management based on the application’s unique requirements.
High Availability:
- Failover Mechanism: HAProxy detects server failures and sends traffic to healthy servers, ensuring the application’s continuous availability.
- Health Checks: Regular health checks on backend servers help in early detection of issues, maintaining the reliability of the infrastructure.
Scalability:
- Horizontal Scaling: HAProxy makes it easy to scale applications by adding extra servers to the backend pool. This adaptability accommodates increasing traffic needs without requiring substantial architectural adjustments.
Security:
- Access Control: HAProxy makes it easy to scale applications by adding extra servers to the backend pool. This adaptability accommodates increasing traffic needs without requiring substantial architectural adjustments.
- SSL Termination: enhances the speed of backend servers and streamlines certificate management by offloading SSL/TLS encryption and decryption.
Flexibility and Customization:
- Layer 4 and Layer 7 Capabilities: HAProxy offers versatility in managing a range of protocols and use cases by functioning at both Layer 4 (TCP) and Layer 7 (HTTP) of the OSI model.
Mysql Cluster:
For HA PROXY setup we created a Mysql cluster having HAPROXY setup on mysql-01 and multi master replication between mysql-02 and mysql-03.
| 1 2 3 | Instance 'mysql-01.sys1:3306' 192.168.42.128 HAPROXY Instance 'mysql-02.sys2:3306' 192.168.42.129 PRIMARY/secondary Instance 'mysql-03.sys3:3306' 192.168.42.130 SECONDARY/primary |
Step 01:Checking network connection in linux in all three node
| 1 2 3 4 5 6 7 8 9 | [root@mysql-02 etc]# netstat -ntlp Active Internet connections (only servers) Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 893/sshd tcp 0 0 127.0.0.1:27017 0.0.0.0:* LISTEN 1149/mongod tcp 0 0 192.168.42.129:3306 0.0.0.0:* LISTEN 4833/mysqld tcp6 0 0 :::22 :::* LISTEN 893/sshd tcp6 0 0 :::33060 :::* LISTEN 4833/mysqld tcp6 0 0 :::33061 :::* LISTEN 4833/mysqld |
Step 02:Created user and given same grants on both node mysql-02 and mysql-03 with cross user IP 192.168.42.130 & 192.168.42.129 respectively.
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | [root@mysql-03 centos]# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 21 Server version: 8.0.40 MySQL Community Server - GPL Copyright (c) 2000, 2024, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> CREATE USER 'replica_user'@'192.168.42.129' IDENTIFIED WITH 'mysql_native_password' BY 'password'; Query OK, 0 rows affected (0.14 sec) mysql> GRANT REPLICATION SLAVE ON *.* TO 'replica_user'@'192.168.42.129'; Query OK, 0 rows affected (0.03 sec) mysql> Flush Privileges; Query OK, 0 rows affected (0.06 sec) |
Step 03: Setup replication between two nodes mysql-02 and mysql-03 using below command and similar process on both nodes. For the replication setup checkout our blog
Checking status of master in Primary node:
| 1 2 3 4 5 6 7 8 9 | mysql> SHOW MASTER STATUS; +------------------+----------+--------------+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------+ | mysql-bin.000001 | 884 | | | a3cb60af-cc36-11ee-bbbf-000c29c89eea:1-12, d2cdf9cf-9dc8-11ef-a63f-000c29c89eea:1-797:1000104-1002233, f9652e79-8d88-11ef-ace3-000c291f7cd6:1-3 | +------------------+----------+--------------+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec) |
Stop slave on Secondary node and change master configuration in secondary and created table for verifying replication between two nodes:
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 | mysql> STOP SLAVE; Query OK, 0 rows affected, 2 warnings (0.02 sec) mysql> CHANGE MASTER TO master_host='192.168.42.129', master_port=3306, master_user='replica_user', master_password='password', master_log_file=' mysql-bin.000001', master_log_pos=884; Query OK, 0 rows affected, 9 warnings (0.34 sec) mysql> START SLAVE; Query OK, 0 rows affected, 1 warning (0.88 sec) mysql> CREATE DATABASE TEST_DB; Query OK, 1 row affected (0.06 sec) mysql> CREATE TABLE TEST_DB.REPLICATION (`id` varchar(40)); Query OK, 0 rows affected (0.45 sec) |
On other node mysql-02 follow same steps for multi master replication:
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 | [root@mysql-02 centos]# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 23 Server version: 8.0.40 MySQL Community Server - GPL Copyright (c) 2000, 2024, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> stop slave; Query OK, 0 rows affected, 2 warnings (0.02 sec) mysql> CHANGE MASTER TO master_host='192.168.42.130', master_port=3306, master_user='replica_user', master_password='password', master_log_file='mysql-bin.000001', master_log_pos=884; Query OK, 0 rows affected, 9 warnings (0.29 sec) mysql> start slave; Query OK, 0 rows affected, 1 warning (1.09 sec) mysql> SHOW MASTER STATUS; +------------------+----------+--------------+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------+ | mysql-bin.000001 | 884 | | | 4f1a04d0-83b7-11ef-a968-000c298767f1:1-3, a3cb60af-cc36-11ee-bbbf-000c29c89eea:1-12, d2cdf9cf-9dc8-11ef-a63f-000c29c89eea:1-797:1000104-1002233 | +------------------+----------+--------------+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec) |
Verify Earlier created table in a node03 in other node using below command:
| 1 2 3 4 5 6 7 | mysql> SHOW TABLES IN TEST_DB; +-------------------+ | Tables_in_TEST_DB | +-------------------+ | REPLICATION | +-------------------+ 1 row in set (0.20 sec) |
Step 04:Installed HAPROXY in all the three nodes and set up connectivity between all the three nodes.
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 | [root@mysql-01 log]# yum install haproxy akopytov_sysbench 569 B/s | 1.0 kB 00:01 akopytov_sysbench-source 444 B/s | 1.0 kB 00:02 Dependencies resolved. ============================================================================================================================================================== Package Architecture Version Repository Size ============================================================================================================================================================== Installing: haproxy x86_64 1.8.27-5.el8 appstream 1.4 M Transaction Summary ============================================================================================================================================================== Install 1 Package Total download size: 1.4 M Installed size: 4.2 M Is this ok [y/N]: y Downloading Packages: haproxy-1.8.27-5.el8.x86_64.rpm 1.0 MB/s | 1.4 MB 00:01 -------------------------------------------------------------------------------------------------------------------------------------------------------------- Total 1.0 MB/s | 1.4 MB 00:01 Running transaction check Transaction check succeeded. Running transaction test Transaction test succeeded. Running transaction Preparing : 1/1 Running scriptlet: haproxy-1.8.27-5.el8.x86_64 1/1 Installing : haproxy-1.8.27-5.el8.x86_64 1/1 Running scriptlet: haproxy-1.8.27-5.el8.x86_64 1/1 Verifying : haproxy-1.8.27-5.el8.x86_64 1/1 Installed: haproxy-1.8.27-5.el8.x86_64 Complete! |
Verify the location of the HAPROXY configuration file below location. /etc/haproxy/
| 1 2 3 4 5 | [root@mysql-01 etc]# cd /etc/haproxy/ [root@mysql-01 haproxy]# ll total 4 drwxr-xr-x 2 root root 6 Jul 26 2022 conf.d -rw-r--r-- 1 root root 3284 Jul 26 2022 haproxy.cfg |
Edit the haproxy.cfg file using vi editor using below commands and added entries for load balancing and #http for using tcp module
mode tcp
server mysql-2 192.168.42.129:3306 check
server mysql-3 192.168.42.130:3306 check
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 | [root@mysql-01 haproxy]# vi haproxy.cfg option forwardfor except 127.0.0.0/8 option redispatch retries 3 timeout http-request 10s timeout queue 1m timeout connect 10s timeout client 1m timeout server 1m timeout http-keep-alive 10s timeout check 10s maxconn 3000 #--------------------------------------------------------------------- # main frontend which proxys to the backends #--------------------------------------------------------------------- frontend main bind *:5000 acl url_static path_beg -i /static /images /javascript /stylesheets acl url_static path_end -i .jpg .gif .png .css .js use_backend static if url_static default_backend app #--------------------------------------------------------------------- # static backend for serving up images, stylesheets and such #--------------------------------------------------------------------- backend static balance roundrobin server static 127.0.0.1:4331 check #--------------------------------------------------------------------- # round robin balancing between the various backends #--------------------------------------------------------------------- backend app mode tcp option mysql-check user haproxy_check balance roundrobin server mysql-2 192.168.42.129:3306 check server mysql-3 192.168.42.130:3306 check |
Started HAPROXY service in all the three nodes.
| 1 | [root@mysql-01 haproxy]# systemctl start haproxy |
Step 05: Created HAproxy user in the two nodes using below commands
| 1 2 3 4 5 6 7 8 | mysql> CREATE USER 'haproxy_root'@'localhost' IDENTIFIED BY 'Mysql@@123'; Query OK, 0 rows affected (0.26 sec) mysql> GRANT ALL PRIVILEGES ON *.* TO 'haproxy_root'@'localhost' WITH GRANT OPTION; Query OK, 0 rows affected (0.08 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.06 sec) |
Step 06:Verifying HAproxy Working(load balancing) between nodes:
| 1 2 3 4 5 | FOR i IN `seq 1 6 `; do mysql - h 127.0 .0 .1 - P 5000 - u haproxy_root - p****** - e "show variables like 'server_id'"; done |
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | [root@mysql-01 haproxy]# for i in `seq 1 4`; do mysql -h 127.0.0.1 -P 5000 -u haproxy_root -pMysql@@123 -e "show variables like 'server_id'"; done mysql: [Warning] Using a password on the command line interface can be insecure. +---------------+------------+ | Variable_name | Value | +---------------+------------+ | server_id | 3290040949 | +---------------+------------+ mysql: [Warning] Using a password on the command line interface can be insecure. +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id | 2 | +---------------+-------+ mysql: [Warning] Using a password on the command line interface can be insecure. +---------------+------------+ | Variable_name | Value | +---------------+------------+ | server_id | 3290040949 | +---------------+------------+ mysql: [Warning] Using a password on the command line interface can be insecure. +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id | 2 | +---------------+-------+ |
Conclusion:
HAProxy, a load balancing tool, optimizes MySQL operations using the round-robin technique. It distributes client requests across multiple MySQL servers, enhancing system resilience and resource utilization. This adaptability and dependability enable businesses to grow and operate efficiently under changing load conditions.
