.::: Install HAProxy Load Balancer to MariaDB MySQL :::.

 1. Preparation
https://teguhth.blogspot.com/2022/03/how-to-create-ha-high-availability.html
wsrep_cluster_name="cluster1" , 0.0.0.0:3306 -> detect ip loopback & statistic
mariadb01 mariadb02 haproxy
[root@mariadb01 ~]# cat /etc/my.cnf.d/server.cnf
#
......................
# * Galera-related settings
#
[galera]
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://10.10.10.51,10.10.10.52"
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
wsrep_cluster_name="cluster1"
wsrep_sst_method=rsync
wsrep_node_address= "10.10.10.51"
wsrep_node_name="mariadb01"

#wsrep_sst_auth=teguh:triharto
 
.........
[root@mariadb01 ~]#

2. enable in all server
[root@mariadb01 ~]# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
10.10.10.51 mariadb01
10.10.10.52 mariadb02
10.10.10.130 haproxy node3 NodeH
[root@mariadb01 ~]#

3. enable access user haproxy from mariadb01 & mariadb02
mysql -u root -p -e "INSERT INTO mysql.user (Host,User) values ('10.10.10.130','haproxy_check'); FLUSH PRIVILEGES;"
mysql -u root -p -e "GRANT ALL PRIVILEGES ON *.* TO 'haproxy_root'@'10.10.10.130' IDENTIFIED BY 'root' WITH GRANT OPTION; FLUSH PRIVILEGES;"

4. Install HAProxy & enable service
[root@haproxy data]# yum install haproxy -y
[root@haproxy data]# systemctl enable haproxy
Created symlink from /etc/systemd/system/multi-user.target.wants/haproxy.service to /usr/lib/systemd/system/haproxy.service.
[root@haproxy data]# systemctl start haproxy
[root@haproxy data]#

5. Configure HaProxy & restart haproxy

[root@haproxy data]# cat /etc/haproxy/haproxy.cfg
global
    log 127.0.0.1 local0 notice
    user haproxy
    group haproxy

defaults
    mode                    http
    log                     global
    retries 2
    timeout connect         3000
    timeout client          5000
    timeout server          5000
    maxconn                 3000

listen cluster1 0.0.0.0:3306
   # bind 127.0.0.1:3306
    mode tcp
    option mysql-check user haproxy_check
    balance roundrobin
    server mariadb01 10.10.10.51:3306 check weight 1
    server mariadb02 10.10.10.52:3306 check weight 2

[root@haproxy data]# systemctl restart haproxy

6. Testing Load Balancing dan Failover (cause using roundrobin)

[root@haproxy ~]# mysql -h 127.0.0.1 -u haproxy_root -p -e "select @@hostname;"
Enter password:
+------------+
| @@hostname |
+------------+
| mariadb01  |
+------------+
[root@haproxy ~]# mysql -h 127.0.0.1 -u haproxy_root -p -e "select @@hostname;"
Enter password:
+------------+
| @@hostname |
+------------+
| mariadb02  |
+------------+
[root@haproxy ~]#

7. testing using stop service mariadb01 or mariadb02
8. additional listen statistic & restart haproxy

[root@haproxy ~]#
.......

listen stats 0.0.0.0:9000
## HAProxy stats web gui running on port 9000 - username haproxy_root, password root.
        mode http
        stats enable
        stats uri /stats
        stats realm HAProxy\ Statistics
        stats auth haproxy_root:root
        stats admin if TRUE

[root@haproxy ~]#
9. check http://10.10.10.130:9000/stats
http://10.10.10.130:9000/stats

 

 

log yang benar

 

[root@loadbalancer haproxy]# cat /etc/haproxy/haproxy.cfg
#---------------------------------------------------------------------
# Example configuration for a possible web application.  See the
# full configuration options online.
#
#   http://haproxy.1wt.eu/download/1.4/doc/configuration.txt
#
#---------------------------------------------------------------------

#---------------------------------------------------------------------
# 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

#---------------------------------------------------------------------
# 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         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 *: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
    balance     roundrobin
    server  app1 127.0.0.1:5001 check
    server  app2 127.0.0.1:5002 check
    server  app3 127.0.0.1:5003 check
    server  app4 127.0.0.1:5004 check
listen galeracluster 0.0.0.0:3306
    bind *:3306
    mode tcp
    option mysql-check
    balance roundrobin
    server teguhth01 10.10.10.11:3306 check weight 1
    server teguhth02 10.10.10.12:3306 check weight 2
[root@loadbalancer haproxy]#

 

mysql -h 127.0.0.1 -u haproxy_root -p -e "select @@hostname;"

mysql -h 127.0.0.1 -p 3307 -u haproxy_root -p -e "select @@hostname;" 

 

sample lain

 

listen galeracluster 0.0.0.0:3306
    bind *:3306
    mode tcp
    option mysql-check
    balance roundrobin
    server teguhth01 10.10.10.11:3306 check weight 1
    server teguhth02 10.10.10.12:3306 check weight 2
    server teguhth03 10.10.10.13:3306 check weight 3

listen gtidcluster 0.0.0.0:3307
    bind *:3307
    mode tcp
    option mysql-check
    balance roundrobin
    server teguhth01 10.10.10.31:3306 check weight 1
    server teguhth02 10.10.10.32:3306 check weight 2
    server teguhth03 10.10.10.33:3306 check weight 3


listen stats 0.0.0.0:9000
## HAProxy stats web gui running on port 9000 - username haproxy_root, password root.
        mode http
        stats enable
        stats uri /stats
        stats realm HAProxy\ Statistics
        stats auth haproxy_root:root
        stats admin if TRUE