{"id":2733,"date":"2024-12-12T13:08:17","date_gmt":"2024-12-12T13:08:17","guid":{"rendered":"https:\/\/bynatree.com\/?p=2733"},"modified":"2024-12-12T13:08:17","modified_gmt":"2024-12-12T13:08:17","slug":"load-balancing-in-mysql-8-0-using-haproxy","status":"publish","type":"post","link":"https:\/\/divaind.com\/ie1\/2024\/12\/12\/load-balancing-in-mysql-8-0-using-haproxy\/","title":{"rendered":"Load balancing in mysql 8.0 using HAPROXY"},"content":{"rendered":"

Introduction to HAPROXY:<\/b><\/h2>\n

HAProxy, or High Availability Proxy<\/strong>, is an open-source load balancer and proxy server for TCP and HTTP applications.<\/strong> 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.<\/span><\/p><\/blockquote>\n

\"\"<\/p>\n\n\n\n
\n

HAPROXY Features<\/b><\/p>\n<\/td>\n

\n

Use Cases of HAProxy<\/b><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/p>\n

Importance of HAProxy<\/b>:<\/span><\/h3>\n
    \n
  • Load Balancing: <\/b>HAProxy effectively divides incoming network traffic over several backend servers. This ensures that no one server becomes a bottleneck, resulting in effective resource use.<\/span><\/li>\n
  • Various Algorithms<\/b>: 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.<\/span><\/li>\n<\/ul>\n

    High Availability:<\/b><\/h4>\n
      \n
    • Failover Mechanism: <\/b>HAProxy detects server failures and sends traffic to healthy servers, ensuring the application’s continuous availability.<\/span><\/li>\n
    • Health Checks: <\/b>Regular health checks on backend servers help in early detection of issues, maintaining the reliability of the infrastructure.<\/span><\/li>\n<\/ul>\n

      Scalability:<\/b><\/h4>\n
        \n
      • Horizontal Scaling:<\/b> 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.<\/span><\/li>\n<\/ul>\n

        Security:<\/b><\/h4>\n
          \n
        • Access Control: <\/b>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.<\/span><\/li>\n
        • SSL Termination: <\/b>enhances the speed of backend servers and streamlines certificate management by offloading SSL\/TLS encryption and decryption.<\/span><\/li>\n<\/ul>\n

          Flexibility and Customization:<\/b><\/h4>\n
            \n
          • Layer 4 and Layer 7 Capabilities:<\/b> 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.<\/span><\/li>\n<\/ul>\n

            Mysql Cluster:<\/b><\/h2>\n

            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.
            \n\"\"
            \n<\/span><\/p>\n

            Instance 'mysql-01.sys1:3306' 192.168.42.128 HAPROXY\nInstance 'mysql-02.sys2:3306' 192.168.42.129 PRIMARY\/secondary \nInstance 'mysql-03.sys3:3306' 192.168.42.130 SECONDARY\/primary<\/pre>\n
            Step 01:Checking network connection in linux in all three node<\/span><\/h3>\n
            [root@mysql-02 etc]# netstat -ntlp\nActive Internet connections (only servers)\nProto Recv-Q Send-Q Local Address           Foreign Address         State       PID\/Program name\ntcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      893\/sshd\ntcp        0      0 127.0.0.1:27017         0.0.0.0:*               LISTEN      1149\/mongod\ntcp        0      0 192.168.42.129:3306     0.0.0.0:*               LISTEN      4833\/mysqld\ntcp6       0      0 :::22                   :::*                    LISTEN      893\/sshd\ntcp6       0      0 :::33060                :::*                    LISTEN      4833\/mysqld\ntcp6       0      0 :::33061                :::*                    LISTEN      4833\/mysqld<\/pre>\n
            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.
            \n<\/span><\/h3>\n
            [root@mysql-03 centos]# mysql -u root -p\nEnter password:\nWelcome to the MySQL monitor.  Commands end with ; or \\g.\nYour MySQL connection id is 21\nServer version: 8.0.40 MySQL Community Server - GPL\n\nCopyright (c) 2000, 2024, Oracle and\/or its affiliates.\n\nOracle is a registered trademark of Oracle Corporation and\/or its\naffiliates. Other names may be trademarks of their respective\nowners.\n\nType 'help;' or '\\h' for help. Type '\\c' to clear the current input statement.\n\nmysql> CREATE USER 'replica_user'@'192.168.42.129' IDENTIFIED WITH 'mysql_native_password' BY 'password';\nQuery OK, 0 rows affected (0.14 sec)\n\nmysql> GRANT REPLICATION SLAVE ON *.* TO 'replica_user'@'192.168.42.129';\nQuery OK, 0 rows affected (0.03 sec)\n\nmysql> Flush Privileges;\nQuery OK, 0 rows affected (0.06 sec)<\/pre>\n
            Step 03: Setup replication between two nodes <\/span>mysql-02 and mysql-03 <\/span>using below command and similar process on both nodes. For the replication setup checkout our blog<\/span><\/h3>\n
            Checking status of master in Primary node:
            \n<\/span><\/p>\n
            mysql> SHOW MASTER STATUS;\n+------------------+----------+--------------+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------+\n| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                                                                                                                               |\n+------------------+----------+--------------+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------+\n| mysql-bin.000001 |      884 |              |                  | a3cb60af-cc36-11ee-bbbf-000c29c89eea:1-12,\nd2cdf9cf-9dc8-11ef-a63f-000c29c89eea:1-797:1000104-1002233,\nf9652e79-8d88-11ef-ace3-000c291f7cd6:1-3 |\n+------------------+----------+--------------+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------+\n1 row in set (0.01 sec)<\/pre>\n
            Stop slave on Secondary node and change master configuration in secondary and created table for verifying replication between two nodes:<\/span><\/h4>\n
            mysql> STOP SLAVE;\nQuery OK, 0 rows affected, 2 warnings (0.02 sec)\n\nmysql> 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;\nQuery OK, 0 rows affected, 9 warnings (0.34 sec)\n\nmysql> START SLAVE;\nQuery OK, 0 rows affected, 1 warning (0.88 sec)\n\nmysql> CREATE DATABASE TEST_DB;\nQuery OK, 1 row affected (0.06 sec)\n\nmysql> CREATE TABLE TEST_DB.REPLICATION (`id` varchar(40));\nQuery OK, 0 rows affected (0.45 sec)<\/pre>\n
            On other node mysql-02 follow same steps for multi master replication:<\/span><\/h4>\n
            [root@mysql-02 centos]# mysql -u root -p\nEnter password:\nWelcome to the MySQL monitor.  Commands end with ; or \\g.\nYour MySQL connection id is 23\nServer version: 8.0.40 MySQL Community Server - GPL\n\nCopyright (c) 2000, 2024, Oracle and\/or its affiliates.\n\nOracle is a registered trademark of Oracle Corporation and\/or its\naffiliates. Other names may be trademarks of their respective\nowners.\n\nType 'help;' or '\\h' for help. Type '\\c' to clear the current input statement.\n\nmysql> stop slave;\nQuery OK, 0 rows affected, 2 warnings (0.02 sec)\n\nmysql> 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;\nQuery OK, 0 rows affected, 9 warnings (0.29 sec)\n\nmysql> start slave;\nQuery OK, 0 rows affected, 1 warning (1.09 sec)\n\nmysql> SHOW MASTER STATUS;\n+------------------+----------+--------------+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------+\n| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                                                                                                                               |\n+------------------+----------+--------------+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------+\n| mysql-bin.000001 |      884 |              |                  | 4f1a04d0-83b7-11ef-a968-000c298767f1:1-3,\na3cb60af-cc36-11ee-bbbf-000c29c89eea:1-12,\nd2cdf9cf-9dc8-11ef-a63f-000c29c89eea:1-797:1000104-1002233 |\n+------------------+----------+--------------+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------+\n1 row in set (0.01 sec)<\/pre>\n
            Verify Earlier created table in a node03 in other node using below command:<\/span><\/p>\n
            mysql> SHOW TABLES IN TEST_DB;\n+-------------------+\n| Tables_in_TEST_DB |\n+-------------------+\n| REPLICATION       |\n+-------------------+\n1 row in set (0.20 sec)<\/pre>\n
            Step 04:Installed HAPROXY in all the three nodes and set up connectivity between all the three nodes.<\/span><\/h3>\n
            [root@mysql-01 log]# yum install haproxy\nakopytov_sysbench                                                                                                             569  B\/s | 1.0 kB     00:01\nakopytov_sysbench-source                                                                                                      444  B\/s | 1.0 kB     00:02\nDependencies resolved.\n==============================================================================================================================================================\n Package                             Architecture                       Version                                   Repository                             Size\n==============================================================================================================================================================\nInstalling:\n haproxy                             x86_64                             1.8.27-5.el8                              appstream                             1.4 M\n\nTransaction Summary\n==============================================================================================================================================================\nInstall  1 Package\n\nTotal download size: 1.4 M\nInstalled size: 4.2 M\nIs this ok [y\/N]: y\nDownloading Packages:\nhaproxy-1.8.27-5.el8.x86_64.rpm                                                                                               1.0 MB\/s | 1.4 MB     00:01\n--------------------------------------------------------------------------------------------------------------------------------------------------------------\nTotal                                                                                                                         1.0 MB\/s | 1.4 MB     00:01\nRunning transaction check\nTransaction check succeeded.\nRunning transaction test\nTransaction test succeeded.\nRunning transaction\n  Preparing        :                                                                                                                                      1\/1\n  Running scriptlet: haproxy-1.8.27-5.el8.x86_64                                                                                                          1\/1\n  Installing       : haproxy-1.8.27-5.el8.x86_64                                                                                                          1\/1\n  Running scriptlet: haproxy-1.8.27-5.el8.x86_64                                                                                                          1\/1\n  Verifying        : haproxy-1.8.27-5.el8.x86_64                                                                                                          1\/1\n\nInstalled:\n  haproxy-1.8.27-5.el8.x86_64\n\nComplete!<\/pre>\n
            Verify the location of the HAPROXY configuration file below location. \/etc\/haproxy\/<\/span><\/h4>\n
            [root@mysql-01 etc]# cd \/etc\/haproxy\/\n[root@mysql-01 haproxy]# ll\ntotal 4\ndrwxr-xr-x 2 root root    6 Jul 26  2022 conf.d\n-rw-r--r-- 1 root root 3284 Jul 26  2022 haproxy.cfg<\/pre>\n
            Edit the haproxy.cfg file using vi editor using below commands and added entries for load balancing and #http for using tcp module<\/span><\/h4>\n
            mode tcp<\/span>
            \n<\/span>\u00a0 \u00a0 server\u00a0 mysql-2 192.168.42.129:3306 check<\/span><\/p>\n
            \u00a0\u00a0\u00a0\u00a0server\u00a0 mysql-3 192.168.42.130:3306 check<\/span><\/p>\n
            [root@mysql-01 haproxy]# vi haproxy.cfg\n    option forwardfor       except 127.0.0.0\/8\n    option                  redispatch\n    retries                 3\n    timeout http-request    10s\n    timeout queue           1m\n    timeout connect         10s\n    timeout client          1m\n    timeout server          1m\n    timeout http-keep-alive 10s\n    timeout check           10s\n    maxconn                 3000\n\n#---------------------------------------------------------------------\n# main frontend which proxys to the backends\n#---------------------------------------------------------------------\nfrontend main\n    bind *:5000\n    acl url_static       path_beg       -i \/static \/images \/javascript \/stylesheets\n    acl url_static       path_end       -i .jpg .gif .png .css .js\n\n    use_backend static          if url_static\n    default_backend             app\n\n#---------------------------------------------------------------------\n# static backend for serving up images, stylesheets and such\n#---------------------------------------------------------------------\nbackend static\n    balance     roundrobin\n    server      static 127.0.0.1:4331 check\n\n#---------------------------------------------------------------------\n# round robin balancing between the various backends\n#---------------------------------------------------------------------\nbackend app\n    mode tcp\n    option mysql-check user haproxy_check\n    balance     roundrobin\n    server  mysql-2 192.168.42.129:3306 check\n    server  mysql-3 192.168.42.130:3306 check<\/pre>\n
            Started HAPROXY service in all the three nodes.<\/span><\/h4>\n
            [root@mysql-01 haproxy]# systemctl start haproxy<\/pre>\n
            Step 05: Created HAproxy user in the two nodes using below commands<\/span><\/h3>\n
            mysql> CREATE USER 'haproxy_root'@'localhost' IDENTIFIED BY 'Mysql@@123';\nQuery OK, 0 rows affected (0.26 sec)\n\nmysql> GRANT ALL PRIVILEGES ON *.* TO 'haproxy_root'@'localhost' WITH GRANT OPTION;\nQuery OK, 0 rows affected (0.08 sec)\n\nmysql> FLUSH PRIVILEGES;\nQuery OK, 0 rows affected (0.06 sec)<\/pre>\n
            Step 06:Verifying HAproxy Working(load balancing) between nodes:<\/span><\/h3>\n
            FOR i IN `seq 1 6 `;\n\ndo mysql - h 127.0 .0 .1 - P 5000 - u haproxy_root - p****** - e \"show variables like 'server_id'\";\n\ndone<\/pre>\n
             <\/p>\n
            [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\nmysql: [Warning] Using a password on the command line interface can be insecure.\n+---------------+------------+\n| Variable_name | Value      |\n+---------------+------------+\n| server_id     | 3290040949 |\n+---------------+------------+\nmysql: [Warning] Using a password on the command line interface can be insecure.\n+---------------+-------+\n| Variable_name | Value |\n+---------------+-------+\n| server_id     | 2     |\n+---------------+-------+\nmysql: [Warning] Using a password on the command line interface can be insecure.\n+---------------+------------+\n| Variable_name | Value      |\n+---------------+------------+\n| server_id     | 3290040949 |\n+---------------+------------+\nmysql: [Warning] Using a password on the command line interface can be insecure.\n+---------------+-------+\n| Variable_name | Value |\n+---------------+-------+\n| server_id     | 2     |\n+---------------+-------+<\/pre>\n
            Conclusion:<\/span><\/h3>\n
            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.<\/span><\/p>\n
            \u00a0<\/span><\/p>\n
            \u00a0<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"
            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…<\/p>\n","protected":false},"author":1,"featured_media":2736,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[24],"tags":[32,34,36,39,41,42,43,53,54,57,59,61,159,202,222],"class_list":["post-2733","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-mysql","tag-databaseloadbalancing","tag-databaseperformance","tag-databasescaling","tag-haproxy","tag-itinfrastructure","tag-loadbalancing","tag-mysql8","tag-scalability","tag-servermanagement","tag-sqltips","tag-techblog","tag-techtips","tag-highavailability","tag-mysql","tag-mysqlperformance","category-24","description-off"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/posts\/2733","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/comments?post=2733"}],"version-history":[{"count":0,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/posts\/2733\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/media\/2736"}],"wp:attachment":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/media?parent=2733"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/categories?post=2733"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/tags?post=2733"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}