High Availability WordPress Using HAProxy and MaxScale With Galera Cluster on CentOS 7

High Availability WordPress menggunakan HAProxy dan MaxScale dengan Galera Cluster pada CentOS 7

  • Load balancer HAProxy: lb.server.kita > 192.168.88.110
  • Web server 01: web01.server.kita > 192.168.88.109
  • Web server 02: web02.server.kita > 192.168.88.108
  • Web server 03: web03.server.kita > 192.168.88.107
  • Load balancer MaxScale: lb-db.server.kita > 192.168.88.106
  • Database node-1: db01.server.kita > 192.168.88.105
  • Database node-2: db02.server.kita > 192.168.88.104
  • Database node-3: db03.server.kita > 192.168.88.103


  • Buat database di salah satu node Galera Cluster, bisa di node db01, db02, atau db03
  • Pastikan database tersebut di Grant user ke masing-masing IP Load balancer MaxScale, Webserver web01, web02, dan web03.
  • Nama database: db_ha_wp > Nama user: user_ha_wp > Password user: pass_ha_wp
[root@db01 web01.server.kita]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 11
Server version: 10.5.13-MariaDB MariaDB Server


MariaDB [(none)]> CREATE DATABASE db_ha_wp;
Query OK, 1 row affected (0.008 sec)

MariaDB [(none)]> GRANT ALL PRIVILEGES ON db_ha_wp.* TO 'user_ha_wp'@'192.168.88.109' IDENTIFIED BY 'pass_ha_wp';
Query OK, 0 rows affected (0.009 sec)

MariaDB [(none)]> GRANT ALL PRIVILEGES ON db_ha_wp.* TO 'user_ha_wp'@'192.168.88.108' IDENTIFIED BY 'pass_ha_wp';
Query OK, 0 rows affected (0.009 sec)

MariaDB [(none)]> GRANT ALL PRIVILEGES ON db_ha_wp.* TO 'user_ha_wp'@'192.168.88.107' IDENTIFIED BY 'pass_ha_wp';
Query OK, 0 rows affected (0.008 sec)

MariaDB [(none)]> GRANT ALL PRIVILEGES ON db_ha_wp.* TO 'user_ha_wp'@'192.168.88.106' IDENTIFIED BY 'pass_ha_wp';
Query OK, 0 rows affected (0.012 sec)

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.008 sec)

MariaDB [(none)]> SELECT User,Host FROM mysql.user;
+-------------+----------------+
| User        | Host           |
+-------------+----------------+
| supergalera | %              |
| lb_user     | 192.168.88.106 |
| user_ha_wp  | 192.168.88.106 |
| user_ha_wp  | 192.168.88.107 |
| user_ha_wp  | 192.168.88.108 |
| user_ha_wp  | 192.168.88.109 |
| mariadb.sys | localhost      |
| mysql       | localhost      |
| root        | localhost      |
+-------------+----------------+
9 rows in set (0.001 sec)

  • Konfigruasi load balancer haproxy
global
   log /dev/log local0
   log /dev/log local1 notice
   chroot /var/lib/haproxy
   stats timeout 30s
   user haproxy
   group haproxy
   daemon

resolvers dns
   nameserver public-1 192.168.88.100:53
   hold valid 1s

defaults
   log global
   mode http
   option httplog
   option dontlognull
   timeout connect 10000
   timeout client 30000
   timeout server 30000
   maxconn 50000
   fullconn 50000
   retries 10

listen stats
   bind *:8080 ssl crt /etc/ssl/server.kita.pem
   stats enable
   stats uri /
   stats realm Haproxy\ Statistics
   stats auth admin:admin
   stats refresh 5s

frontend http_front
   mode http
   bind *:80
#   redirect scheme https if !{ ssl_fc }
   http-response set-header Access-Control-Allow-Origin "*"
   http-response set-header Access-Control-Allow-Headers "Origin, X-Requested-With, Content-Type, Accept, Authorization, JSNLog-RequestId, activityId, applicationId, applicationUserId, channelId, senderId, sessionId"
   http-response set-header Access-Control-Max-Age 3628800
   http-response set-header Access-Control-Allow-Methods "GET, DELETE, OPTIONS, POST, PUT"
   option forwardfor
   default_backend http_back

frontend https_front
   mode http
   bind *:443 ssl crt /etc/ssl/server.kita.pem ssl-min-ver TLSv1.2 ssl-max-ver TLSv1.3 force-tlsv12 ciphers ECDHE-RSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-AES256-GCM-SHA384:DHE-RSA-AES128-GCM-SHA256:DHE-DSS-AES128-GCM-SHA256:kEDH+AESGCM:ECDHE-RSA-AES128-SHA256:ECDHE-ECDSA-AES128-SHA256:ECDHE-RSA-AES128-SHA:ECDHE-ECDSA-AES128-SHA:ECDHE-RSA-AES256-SHA384:ECDHE-ECDSA-AES256-SHA384:ECDHE-RSA-AES256-SHA:ECDHE-ECDSA-AES256-SHA:DHE-RSA-AES128-SHA256:DHE-RSA-AES128-SHA:DHE-DSS-AES128-SHA256:DHE-RSA-AES256-SHA256:DHE-DSS-AES256-SHA:DHE-RSA-AES256-SHA:AES128-GCM-SHA256:AES256-GCM-SHA384:AES128-SHA256:AES256-SHA256:AES128-SHA:AES256-SHA:AES:CAMELLIA:DES-CBC3-SHA:!aNULL:!eNULL:!EXPORT:!DES:!RC4:!MD5:!PSK:!aECDH:!EDH-DSS-DES-CBC3-SHA:!EDH-RSA-DES-CBC3-SHA:!KRB5-DES-CBC3-SHA
   http-response set-header Access-Control-Allow-Origin "*"
   http-response set-header Access-Control-Allow-Headers "Origin, X-Requested-With, Content-Type, Accept, Authorization, JSNLog-RequestId, activityId, applicationId, applicationUserId, channelId, senderId, sessionId"
   http-response set-header Access-Control-Max-Age 3628800
   http-response set-header Access-Control-Allow-Methods "GET, DELETE, OPTIONS, POST, PUT"
   option forwardfor
   default_backend http_back

backend http_back
   mode http
   balance roundrobin
   http-response set-header Strict-Transport-Security max-age=31536000
   http-request set-header X-Forwarded-Port %[dst_port]
   http-request add-header X-Forwarded-Proto https if { ssl_fc }
   http-response set-header Host lb.server.kita
   option forwardfor
   option abortonclose
   cookie SERVERID insert indirect nocache
   server server_web01 web01.server.kita:80 check cookie server_web01 resolvers dns inter 1000 check maxconn 10000
   server server_web02 web02.server.kita:80 check cookie server_web02 resolvers dns inter 1000 check maxconn 10000
   server server_web03 web03.server.kita:80 check cookie server_web03 resolvers dns inter 1000 check maxconn 10000
  • Konfigurasi load balancer maxscale
#Global MaxScale Settings
[maxscale]
threads=auto

#Define Server Nodes
[db01]
type=server
address=192.168.88.105
port=3306
protocol=MariaDBBackend

[db02]
type=server
address=192.168.88.104
port=3306
protocol=MariaDBBackend

[db03]
type=server
address=192.168.88.103
port=3306
protocol=MariaDBBackend

#Define Monitoring Service
[Galera-Monitor]
type=monitor
module=galeramon
servers=db01,db02,db03
user=lb_user
password=lb_P@ssw0rd!
monitor_interval=1000

#Define Galera Service
[Galera-Service]
type=service
router=readconnroute
router_options=synced
servers=db01,db02,db03
user=lb_user
password=lb_P@ssw0rd!

#Galera cluster listener
[Galera-Listener]
type=listener
service=Galera-Service
protocol=MariaDBClient
address=0.0.0.0
port=3306
  • Masing-masing php yang digunakan pada webserver adalah versi PHP 7.4
php -v
PHP 7.4.26 (cli) (built: Nov 16 2021 15:31:30) ( NTS )
Copyright (c) The PHP Group
Zend Engine v3.4.0, Copyright (c) Zend Technologies
  • Konfigurasi Lsyncd pada ketiga webserver web01, web02, web03 (semua bertindak sebagai Master)
[root@web01 web01.server.kita]# cat /etc/lsyncd.conf
----
-- User configuration file for lsyncd.
--
-- Simple example for default rsync, but executing moves through on the target.
--
-- For more examples, see /usr/share/doc/lsyncd*/examples/
--
--sync{default.rsyncssh, source="/var/www/html", host="localhost", targetdir="/tmp/htmlcopy/"}

settings {
        logfile = "/var/log/lsyncd/lsyncd.log",
        statusFile = "/tmp/lsyncd.stat",
        statusInterval = 1,
}

sync {
        default.rsync,
        source = "/var/www/web01.server.kita/",
        target = "192.168.88.108:/var/www/web02.server.kita/",
        delay = 100,
}

sync {
        default.rsync,
        source = "/var/www/web01.server.kita/",
        target = "192.168.88.107:/var/www/web03.server.kita/",
        delay = 110,
}

rsync = {
        times = true,
        compress = true,
        update = true,
        perms = true,
        owner = true,
        group = true,
        xattrs = true,
        _extra = {"-a"},
        rsh = "/usr/bin/ssh -l root -i /root/.ssh/id_rsa"
}

  • Install WordPress lewat server load balancer haproxy (server.kita), setting file wp-config.php
// ** MySQL settings - You can get this info from your web host ** //
/** The name of the database for WordPress */
define( 'DB_NAME', 'db_ha_wp' );

/** MySQL database username */
define( 'DB_USER', 'user_ha_wp' );

/** MySQL database password */
define( 'DB_PASSWORD', 'pass_ha_wp' );

/** MySQL hostname */
define( 'DB_HOST', '192.168.88.106' );

/** Database charset to use in creating database tables. */
define( 'DB_CHARSET', 'utf8mb4' );

/** The database collate type. Don't change this if in doubt. */
define( 'DB_COLLATE', '' );

/**#@+

  • WordPress sudah di install dan ter-replikasi pada semua server web01, web02, web03
  • Pengujian, tambahkan post baru dan ganti theme wordpress

  • Cek status load balancer maxscale
  • Cek status load balancer haproxy
  • Cek Database pada semua node mariadb, database berhasil ter-replikasi dengan baik

  • Pengujian Denial of Services, cek utilisasi cpu dan ram dengan htop, semua webserver mengalami peak performance yang menandakan proses load balancing berjalan dengan lancar (didistribusikan merata ke server web)

  • Tes availability, matikan server web02, web03 dan database db02, db03
[root@lb admin]# ping web02.server.kita -c3
PING web02.server.kita (192.168.88.108) 56(84) bytes of data.
From server.kita (192.168.88.110) icmp_seq=1 Destination Host Unreachable
From server.kita (192.168.88.110) icmp_seq=2 Destination Host Unreachable
From server.kita (192.168.88.110) icmp_seq=3 Destination Host Unreachable

[root@lb admin]# ping web03.server.kita -c3
PING web03.server.kita (192.168.88.107) 56(84) bytes of data.
From lb.server.kita (192.168.88.110) icmp_seq=1 Destination Host Unreachable
From lb.server.kita (192.168.88.110) icmp_seq=2 Destination Host Unreachable
From lb.server.kita (192.168.88.110) icmp_seq=3 Destination Host Unreachable

[root@lb admin]# ping db02.server.kita -c3
PING db02.server.kita (192.168.88.104) 56(84) bytes of data.
From server.kita (192.168.88.110) icmp_seq=1 Destination Host Unreachable
From server.kita (192.168.88.110) icmp_seq=2 Destination Host Unreachable
From server.kita (192.168.88.110) icmp_seq=3 Destination Host Unreachable

[root@lb admin]# ping db03.server.kita -c3
PING db03.server.kita (192.168.88.104) 56(84) bytes of data.
From lb.server.kita (192.168.88.110) icmp_seq=1 Destination Host Unreachable
From lb.server.kita (192.168.88.110) icmp_seq=2 Destination Host Unreachable
From lb.server.kita (192.168.88.110) icmp_seq=3 Destination Host Unreachable
[root@lb-db admin]# maxctrl list servers
┌────────┬────────────────┬──────┬─────────────┬─────────────────────────┬──────┐
│ Server │ Address        │ Port │ Connections │ State                   │ GTID │
├────────┼────────────────┼──────┼─────────────┼─────────────────────────┼──────┤
│ db01   │ 192.168.88.105 │ 3306 │ 19          │ Master, Synced, Running │      │
├────────┼────────────────┼──────┼─────────────┼─────────────────────────┼──────┤
│ db02   │ 192.168.88.104 │ 3306 │ 0           │ Down                    │      │
├────────┼────────────────┼──────┼─────────────┼─────────────────────────┼──────┤
│ db03   │ 192.168.88.103 │ 3306 │ 0           │ Down                    │      │
└────────┴────────────────┴──────┴─────────────┴─────────────────────────┴──────┘
  • WordPress masih bisa di akses, dan varnish cache berfungsi dengan baik

herdiana3389

A system administrator with skills in system administration, virtualization, linux, windows, networking, cloud computing, container, etc.