Load Balancing MariaDB Galera Cluster using MariaDB MaxScale with Encrypt Connection on AlmaLinux / RockyLinux

Load Balancing Database MariaDB Galera Cluster menggunakan MariaDB MaxScale dengan Koneksi Replikasi Terenkripsi pada AlmaLinux 8 / RockyLinux 8

  • db01 : 192.168.100.204
  • db02 : 192.168.100.158
  • db03 : 192.168.100.36
  • maxscale : 192.168.100.53
  • web : 192.168.100.217

Pre-Requirement

  • Edit file hosts pada semua server database (db01, db02, db03)
cat <<EOF | tee /etc/hosts
#Server Database
127.0.0.1   localhost
192.168.100.204  db01.sys-ops.id
192.168.100.158  db02.sys-ops.id
192.168.100.36   db03.sys-ops.id
EOF
  • Setting hostname pada semua server
hostnamectl set-hostname lb.sys-ops.id
hostnamectl set-hostname db01.sys-ops.id
hostnamectl set-hostname db02.sys-ops.id
hostnamectl set-hostname db03.sys-ops.id
hostnamectl set-hostname web.sys-ops.id
  • Disable selinux dan firewalld pada semua server
setenforce 0
sed -i 's/^SELINUX=.*/SELINUX=permissive/g' /etc/selinux/config

systemctl disable --now firewalld
  • Update system dan install tools pendukung semua server
yum update -y && yum install epel-release -y
yum install nano curl wget htop nano net-tools yum-utils unzip rsync lsof -y

Install MariaDB Server 10.11

  • Install MariaDB 10.11 pada semua server database (db01, db02, db03)
curl -LsS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash -s -- --mariadb-server-version=10.11
yum install MariaDB-client MariaDB-server -y
  • Cek versi database dan aktifkan service mariadb pada semua server database (db01, db02, db03)
systemctl enable --now mariadb

mariadb -V
---<output>---
mariadb  Ver 15.1 Distrib 10.11.4-MariaDB, for Linux (x86_64) using readline 5.1
  • Jalankan mariadb-secure-installation pada semua server database (db01, db02, db03)
mariadb-secure-installation
---<output>---
   Enter current password for root (enter for none):  <enter>
   Switch to unix_socket authentication [Y/n] n
   Change the root password? [Y/n] Y
   Remove anonymous users? [Y/n] Y
   Disallow root login remotely? [Y/n] Y
   Remove test database and access to it? [Y/n] Y
   Reload privilege tables now? [Y/n] Y
  • Buat file mariadb.log pada semua server database (db01, db02, db03)
touch /var/log/mariadb.log
chown mysql:mysql /var/log/mariadb.log
  • Stop service mariadb pada semua server database (db01, db02, db03)
systemctl stop mariadb

Konfigurasi SSL

  • Buat self-signed TLS certificate pada server db01
mkdir -p /etc/ssl/mysql/ && cd /etc/ssl/mysql/
openssl req -x509 -newkey rsa:2048 -keyout server-key-enc.pem -out server-cert.pem -subj '/DC=com/DC=sysops/CN=server' -passout pass:pass2023
openssl rsa -in server-key-enc.pem -out server-key.pem -passin pass:pass2023 -passout pass:
openssl req -x509 -newkey rsa:2048 -keyout client-key-enc.pem -out client-cert.pem -subj '/DC=com/DC=sysops/CN=client' -passout pass:pass2023
openssl rsa -in client-key-enc.pem -out client-key.pem -passin pass:pass2023 -passout pass:
cat server-cert.pem client-cert.pem > ca.pem
openssl verify -CAfile ca.pem client-cert.pem server-cert.pem
  • Buat folder /etc/ssl/mysql/ pada server db02 dan db03
  • Copy semua file *.pem dari server db01 ke server db02 dan db03
mkdir -p /etc/ssl/mysql/

scp -r /etc/ssl/mysql/*.pem [email protected]:/etc/ssl/mysql/
scp -r /etc/ssl/mysql/*.pem [email protected]:/etc/ssl/mysql/
  • Edit permission file pada directory /etc/ssl/mysql di semua server database (db01, db02, db03)
chown mysql:mysql /etc/ssl/mysql/ -R
chmod 400 /etc/ssl/mysql/*
chmod 700 /etc/ssl/mysql/

Konfigurasi Galera Cluster

  • Edit file /etc/my.cnf.d/server.cnf pada semua server database (db01, db02, db03)
nano /etc/my.cnf.d/server.cnf
  • Edit dan sesuaikan pada bagian baris berikut:
  • wsrep_cluster_name > nama cluster
  • wsrep_cluster_address > IP address semua server database
  • wsrep_node_address > ip address server database
  • wsrep_node_name > nama node
[mysqld]
log_error=/var/log/mariadb.log
slow_query_log = ON
slow_query_log_file = /var/log/mariadb-slow.log
long_query_time = 30

innodb-file-per-table = 1
innodb-buffer-pool-size = 1G
innodb-buffer-pool-chunk-size = 128M
innodb-buffer-pool-instances = 8
innodb-log-file-size = 64M
innodb-io-capacity = 5000
innodb-io-capacity-max = 50000
innodb-read-io-threads = 8
innodb-write-io-threads = 8
innodb-thread-concurrency = 32
innodb_large_prefix = ON

max_connections = 300
thread-cache-size = 300
interactive_timeout = 600
wait_timeout = 600

max-allowed-packet = 32M
key-buffer-size = 128M
query-cache-size = 256M
query-cache-limit = 64M
max-heap-table-size = 32M
tmp-memory-table-size = 32M
tmp-table-size = 32M

open-files-limit = 50000
table-definition-cache = 5000
table-open-cache = 15000
binlog-cache-size = 1M
thread_stack = 1M
join-buffer-size = 1M

myisam_sort_buffer_size = 64M
sort_buffer_size = 2M
read_buffer_size = 1M
read_rnd_buffer_size = 1M

ssl-ca=/etc/ssl/mysql/ca.pem
ssl-cert=/etc/ssl/mysql/server-cert.pem
ssl-key=/etc/ssl/mysql/server-key.pem

[client]
ssl-ca=/etc/ssl/mysql/ca.pem
ssl-cert=/etc/ssl/mysql/client-cert.pem
ssl-key=/etc/ssl/mysql/client-key.pem

[galera]
# Mandatory settings
wsrep_on=ON
wsrep_provider=/usr/lib64/galera-4/libgalera_smm.so
wsrep_cluster_name="galeracluster"
wsrep_cluster_address="gcomm://192.168.100.204,192.168.100.158,192.168.100.36"
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
wsrep_sst_method=rsync
wsrep_node_address="192.168.100.204"
wsrep_node_name="node_1"
bind-address=0.0.0.0
wsrep_provider_options="gcache.size = 512M; gcache.page_size = 256M; gcache.recover = yes; socket.ssl_key=/etc/ssl/mysql/server-key.pem;socket.ssl_cert=/etc/ssl/mysql/server-cert.pem;socket.ssl_ca=/etc/ssl/mysql/ca.pem"

Aktifkan Galera Cluster

  • Jalankan galera cluster pada server db01, pastikan tidak ada error
 galera_new_cluster
  • Jalankan service mariadb pada server db02 dan db03, pastikan tidak ada error
systemctl start mariadb

  • Jika pada saat menjalankan galera cluster (galera_new_cluster) terdapat error, pastikan safe_to_bootstrap bernilai 1
nano /var/lib/mysql/grastate.dat
# GALERA saved state
version: 2.1
uuid:    6c796f10-fc26-11ec-a9f3-2a3bce8befd4
seqno:   -1
safe_to_bootstrap: 1

Verifikasi Galera Cluster

  • Verifikasi galera replication traffic pada semua server database (db01, db02, db03)
lsof -i:4567
---<output>---
COMMAND   PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mariadbd 5541 mysql    8u  IPv4 426802      0t0  TCP *:tram (LISTEN)
mariadbd 5541 mysql   25u  IPv4 431937      0t0  TCP db01.sys-ops.id:tram->db03.sys-ops.id:50074 (ESTABLISHED)
mariadbd 5541 mysql   50u  IPv4 428757      0t0  TCP db01.sys-ops.id:tram->db02.sys-ops.id:46202 (ESTABLISHED)
  • Verifikasi status SSL pada server db01
MariaDB [(none)]> status
--------------
mariadb  Ver 15.1 Distrib 10.11.4-MariaDB, for Linux (x86_64) using readline 5.1

Connection id:          8
Current database:
Current user:           root@localhost
SSL:                    Cipher in use is TLS_AES_256_GCM_SHA384
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server:                 MariaDB
Server version:         10.11.4-MariaDB MariaDB Server
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8mb3
Conn.  characterset:    utf8mb3
UNIX socket:            /var/lib/mysql/mysql.sock
Uptime:                 1 min 21 sec

Threads: 3  Questions: 17  Slow queries: 0  Opens: 26  Open tables: 0  Queries per second avg: 0.209
--------------

MariaDB [(none)]> SHOW VARIABLES LIKE 'have_ssl';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_ssl      | YES   |
+---------------+-------+
  • Verifikasi status cluster, pastikan WSREP_CLUSTER_SIZE bernilai 3, karena hanya ada 3 node
MariaDB [(none)]> SELECT * FROM information_schema.global_status WHERE variable_name IN ('WSREP_CLUSTER_STATUS','WSREP_LOCAL_STATE_COMMENT','WSREP_CLUSTER_SIZE','WSREP_EVS_REPL_LATENCY','WSREP_EVS_DELAYED','WSREP_READY','WSREP_CONNECTED');
+---------------------------+----------------+
| VARIABLE_NAME             | VARIABLE_VALUE |
+---------------------------+----------------+
| WSREP_LOCAL_STATE_COMMENT | Synced         |
| WSREP_EVS_DELAYED         |                |
| WSREP_EVS_REPL_LATENCY    | 0/0/0/0/0      |
| WSREP_CLUSTER_SIZE        | 3              |
| WSREP_CLUSTER_STATUS      | Primary        |
| WSREP_CONNECTED           | ON             |
| WSREP_READY               | ON             |
+---------------------------+----------------+
  • Verifikasi status galera cache dan galera ssl
MariaDB [(none)]> show variables like 'wsrep_provider_options'\G
*************************** 1. row ***************************
Variable_name: wsrep_provider_options
        Value: base_dir = /var/lib/mysql/; base_host = 192.168.100.204; base_port = 4567; cert.log_conflicts = no; cert.optimistic_pa = yes; 
debug = no; evs.auto_evict = 0; evs.causal_keepalive_period = PT1S; evs.debug_log_mask = 0x1; evs.delay_margin = PT1S; evs.delayed_keep_period = PT30S; 
evs.inactive_check_period = PT0.5S; evs.inactive_timeout = PT15S; evs.info_log_mask = 0; evs.install_timeout = PT7.5S; evs.join_retrans_period = PT1S; 
evs.keepalive_period = PT1S; evs.max_install_timeouts = 3; evs.send_window = 4; evs.stats_report_period = PT1M; evs.suspect_timeout = PT5S; evs.use_aggregate = true; 
evs.user_send_window = 2; evs.version = 1; evs.view_forget_timeout = P1D; gcache.dir = /var/lib/mysql/; gcache.keep_pages_size = 0; gcache.keep_plaintext_size = 256M; 
gcache.mem_size = 0; gcache.name = galera.cache; gcache.page_size = 256M; gcache.recover = yes; gcache.size = 512M; gcomm.thread_prio = ; gcs.fc_debug = 0; gcs.fc_factor = 1.0; 
gcs.fc_limit = 16; gcs.fc_master_slave = no; gcs.fc_single_primary = no; gcs.max_packet_size = 64500; gcs.max_throttle = 0.25; gcs.recv_q_hard_limit = 9223372036854775807; 
gcs.recv_q_soft_limit = 0.25; gcs.sync_donor = no; gmcast.listen_addr = ssl://0.0.0.0:4567; gmcast.mcast_addr = ; gmcast.mcast_ttl = 1; gmcast.peer_timeout = PT3S; gmcast.segment = 0; 
gmcast.time_wait = PT5S; gmcast.version = 0; ist.recv_addr = 192.168.100.204; pc.announce_timeout = PT3S; pc.checksum = false; pc.ignore_quorum = false; pc.ignore_sb = false; 
pc.linger = PT20S; pc.npvo = false; pc.recovery = true; pc.version = 0; pc.wait_prim = true; pc.wait_prim_timeout = PT30S; pc.weight = 1; protonet.version = 0; repl.causal_read_timeout = PT30S; 
repl.commit_order = 3; repl.key_format = FLAT8; repl.max_ws_size = 2147483647; repl.proto_max = 10; socket.checksum = 2; socket.recv_buf_size = auto; socket.send_buf_size = auto; socket.ssl = YES; 
socket.ssl_ca = /etc/ssl/mysql/ca.pem; socket.ssl_cert = /etc/ssl/mysql/server-cert.pem; socket.ssl_cipher = ; socket.ssl_compression = YES; socket.ssl_key = /etc/ssl/mysql/server-key.pem

Pengujian Replikasi MariaDB Galera Cluster

  • Masuk ke server db01 lalu buat database
mysql -u root -p -e 'CREATE DATABASE sys_ops_id;'
  • Masuk ke server db02 dan db03 lalu cek database
mysql -u root -p -e 'SHOW DATABASES;'
  Enter password:
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys_ops_id         |
+--------------------+

Install dan Konfigurasi MariaDB Maxscale

  • Install repository mariadb 10.11
curl -LsS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash -s -- --mariadb-server-version=10.11
  • Cek versi package maxscale yang tersedia
yum info maxscale
---<output>--- 
Available Packages
Name         : maxscale
Version      : 23.02.2
Release      : 1.rhel.8
Architecture : x86_64
Size         : 92 M
Source       : maxscale-23.02.2-1.rhel.8.src.rpm
Repository   : mariadb-maxscale
Summary      : MaxScale - An intelligent database proxy
License      : MariaDB BSL 1.1
  • Install mariadb maxscale
yum install maxscale -y
  • Konfigurasi maxscale pada file /etc/maxscale.cnf
mv /etc/maxscale.cnf /etc/maxscale.cnf.bak
nano /etc/maxscale.cnf
  • Edit dan sesuaikan pada baris address, servers, user, password
#Global MaxScale Settings
[maxscale]
threads=auto
admin_host            = 0.0.0.0
admin_port            = 8989
admin_secure_gui      = false

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

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

[db03]
type=server
address=192.168.100.36
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=3s

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

[Galera-ReadWrite-Service]
type=service
router=readwritesplit
servers=db01,db02,db03
user=lb_user
password=lb_P@ssw0rd!

#Galera cluster listener
[Galera-RoundRobin-Listener]
type=listener
service=Galera-RoundRobin-Service
protocol=MariaDBClient
address=0.0.0.0
port=3306

[Galera-ReadWrite-Listener]
type=listener
service=Galera-ReadWrite-Service
protocol=MariaDBClient
address=0.0.0.0
port=3307
  • Verifikasi file konfigurasi maxscale.cnf , pastikan tidak ada error dan muncul keterangan Configuration was successfully verified.
maxscale -c -U maxscale
---<output>---
Configuration file : /etc/maxscale.cnf
Log directory      : /var/log/maxscale
Data directory     : /var/lib/maxscale
Module directory   : /usr/lib64/maxscale
Service cache      : /var/cache/maxscale

2023-08-06 12:00:36   notice : Configuration file: /etc/maxscale.cnf
2023-08-06 12:00:36   notice : Log directory: /var/log/maxscale
2023-08-06 12:00:36   notice : Data directory: /var/lib/maxscale
2023-08-06 12:00:36   notice : Module directory: /usr/lib64/maxscale
2023-08-06 12:00:36   notice : Service cache: /var/cache/maxscale
2023-08-06 12:00:36   notice : Module 'qc_sqlite' loaded from '/usr/lib64/maxscale/libqc_sqlite.so'.
2023-08-06 12:00:36   notice : Query classification results are cached and reused. Memory used per thread: 131.05MiB
2023-08-06 12:00:36   notice : Password encryption key file '/var/lib/maxscale/.secrets' not found, using configured passwords as plaintext.
2023-08-06 12:00:36   notice : Configuration was successfully verified.
2023-08-06 12:00:36   notice : MaxScale is shutting down.
2023-08-06 12:00:36   notice : Stopped MaxScale REST API
2023-08-06 12:00:36   notice : All workers have shut down.
2023-08-06 12:00:36   notice : MaxScale shutdown completed.

Hubungkan Galera Cluster dengan MaxScale

  • Login ke dalam database server db01, tambahkan user baru untuk menghubungkan antara node galera cluster dengan server maxscale. user: lb_user , password: lb_P@ssw0rd!
create user 'lb_user'@'192.168.100.53' identified by 'lb_P@ssw0rd!';
grant select on mysql.user to 'lb_user'@'192.168.100.53';
grant select on mysql.roles_mapping to 'lb_user'@'192.168.100.53';
grant select on mysql.db to 'lb_user'@'192.168.100.53';
grant select on mysql.tables_priv to 'lb_user'@'192.168.100.53';
grant select on mysql.columns_priv to 'lb_user'@'192.168.100.53';
grant select on mysql.procs_priv to 'lb_user'@'192.168.100.53';
grant select on mysql.proxies_priv to 'lb_user'@'192.168.100.53';
grant show databases on *.* to 'lb_user'@'192.168.100.53';
flush privileges;
  • Aktifkan service maxscale, lalu cek status maxscale pastikan tidak ada error
systemctl enable --now maxscale
systemctl status maxscale
  • Cek status server server mariadb, status Master ada pada db01
maxctrl list servers
┌────────┬─────────────────┬──────┬─────────────┬─────────────────────────┬──────┬────────────────┐
│ Server │ Address         │ Port │ Connections │ State                   │ GTID │ Monitor        │
├────────┼─────────────────┼──────┼─────────────┼─────────────────────────┼──────┼────────────────┤
│ db01   │ 192.168.100.204 │ 3306 │ 0           │ Master, Synced, Running │      │ Galera-Monitor │
├────────┼─────────────────┼──────┼─────────────┼─────────────────────────┼──────┼────────────────┤
│ db02   │ 192.168.100.158 │ 3306 │ 0           │ Slave, Synced, Running  │      │ Galera-Monitor │
├────────┼─────────────────┼──────┼─────────────┼─────────────────────────┼──────┼────────────────┤
│ db03   │ 192.168.100.36  │ 3306 │ 0           │ Slave, Synced, Running  │      │ Galera-Monitor │
└────────┴─────────────────┴──────┴─────────────┴─────────────────────────┴──────┴────────────────┘
  • Cek status service galera cluster
maxctrl list services
┌───────────────────────────┬────────────────┬─────────────┬───────────────────┬──────────────────┐
│ Service                   │ Router         │ Connections │ Total Connections │ Targets          │
├───────────────────────────┼────────────────┼─────────────┼───────────────────┼──────────────────┤
│ Galera-ReadWrite-Service  │ readwritesplit │ 0           │ 0                 │ db01, db02, db03 │
├───────────────────────────┼────────────────┼─────────────┼───────────────────┼──────────────────┤
│ Galera-RoundRobin-Service │ readconnroute  │ 0           │ 0                 │ db01, db02, db03 │
└───────────────────────────┴────────────────┴─────────────┴───────────────────┴──────────────────┘
  • Cek status listener galera cluster
maxctrl list listeners
┌────────────────────────────┬──────┬─────────┬─────────┬───────────────────────────┐
│ Name                       │ Port │ Host    │ State   │ Service                   │
├────────────────────────────┼──────┼─────────┼─────────┼───────────────────────────┤
│ Galera-ReadWrite-Listener  │ 3307 │ 0.0.0.0 │ Running │ Galera-ReadWrite-Service  │
├────────────────────────────┼──────┼─────────┼─────────┼───────────────────────────┤
│ Galera-RoundRobin-Listener │ 3306 │ 0.0.0.0 │ Running │ Galera-RoundRobin-Service │
└────────────────────────────┴──────┴─────────┴─────────┴───────────────────────────┘
  • Cek status galera monitor
maxctrl list monitors
┌────────────────┬─────────┬──────────────────┐
│ Monitor        │ State   │ Servers          │
├────────────────┼─────────┼──────────────────┤
│ Galera-Monitor │ Running │ db01, db02, db03 │
└────────────────┴─────────┴──────────────────┘
  • Cek user maxscale
maxctrl list users
┌───────┬──────┬────────────┬─────────┬──────────────┬───────────────────────┐
│ Name  │ Type │ Privileges │ Created │ Last Updated │ Last Login            │
├───────┼──────┼────────────┼─────────┼──────────────┼───────────────────────┤
│ admin │ inet │ admin      │         │              │ 8/6/2023, 12:09:51 PM │
└───────┴──────┴────────────┴─────────┴──────────────┴───────────────────────┘

Pengujian Failover Galera Cluster

  • Verifikasi dengan stop service mariadb pada db01
systemctl stop mariadb
  • Cek status server node mariadb, status Master berpindah dari db01 ke db02
maxctrl list servers
┌────────┬─────────────────┬──────┬─────────────┬─────────────────────────┬──────┬────────────────┐
│ Server │ Address         │ Port │ Connections │ State                   │ GTID │ Monitor        │
├────────┼─────────────────┼──────┼─────────────┼─────────────────────────┼──────┼────────────────┤
│ db01   │ 192.168.100.204 │ 3306 │ 0           │ Down                    │      │ Galera-Monitor │
├────────┼─────────────────┼──────┼─────────────┼─────────────────────────┼──────┼────────────────┤
│ db02   │ 192.168.100.158 │ 3306 │ 0           │ Master, Synced, Running │      │ Galera-Monitor │
├────────┼─────────────────┼──────┼─────────────┼─────────────────────────┼──────┼────────────────┤
│ db03   │ 192.168.100.36  │ 3306 │ 0           │ Slave, Synced, Running  │      │ Galera-Monitor │
└────────┴─────────────────┴──────┴─────────────┴─────────────────────────┴──────┴────────────────┘
  • Jalankan kembali service mariadb pada server db01, lalu cek kembali status server mariadb, status Master berpindah kembali dari server db02 ke server db01
maxctrl list servers
┌────────┬─────────────────┬──────┬─────────────┬─────────────────────────┬──────┬────────────────┐
│ Server │ Address         │ Port │ Connections │ State                   │ GTID │ Monitor        │
├────────┼─────────────────┼──────┼─────────────┼─────────────────────────┼──────┼────────────────┤
│ db01   │ 192.168.100.204 │ 3306 │ 0           │ Master, Synced, Running │      │ Galera-Monitor │
├────────┼─────────────────┼──────┼─────────────┼─────────────────────────┼──────┼────────────────┤
│ db02   │ 192.168.100.158 │ 3306 │ 0           │ Slave, Synced, Running  │      │ Galera-Monitor │
├────────┼─────────────────┼──────┼─────────────┼─────────────────────────┼──────┼────────────────┤
│ db03   │ 192.168.100.36  │ 3306 │ 0           │ Slave, Synced, Running  │      │ Galera-Monitor │
└────────┴─────────────────┴──────┴─────────────┴─────────────────────────┴──────┴────────────────┘

MariaDB MaxScale Web UI

  • Akses MaxScale GUI dengan url: http://ip_address_maxscale:8989
  • user: admin , password: mariadb
  • Tampilan MaxScale Web UI 23.02


Pengujian Final

  • Buat database baru dengan nama sys_ops_db pada server db01
  • Grant access user_sys_ops ke server web dan maxscale
  • Buat tabel webdata dan input data
CREATE DATABASE sys_ops_db;
GRANT ALL PRIVILEGES ON sys_ops_db.* TO 'user_sys_ops'@'192.168.100.217' IDENTIFIED BY 'pass_sys_ops';
GRANT ALL PRIVILEGES ON sys_ops_db.* TO 'user_sys_ops'@'192.168.100.53' IDENTIFIED BY 'pass_sys_ops';
FLUSH PRIVILEGES;
USE sys_ops_db;
CREATE TABLE `sys_ops_db`.`webdata` (`id` INT NOT NULL AUTO_INCREMENT, `data_base` VARCHAR(20) NOT NULL, `webserver` VARCHAR(20) NOT NULL, PRIMARY KEY (`id`)) ENGINE = InnoDB;
INSERT INTO `webdata` (`id`, `data_base`, `webserver`) VALUES ('1', 'MariaDB', 'Nginx');
INSERT INTO `webdata` (`id`, `data_base`, `webserver`) VALUES ('2', 'MySQL', 'Apache');

SELECT * FROM webdata;
+----+-----------+-----------+
| id | data_base | webserver |
+----+-----------+-----------+
|  1 | MariaDB   | Nginx     |
|  2 | MySQL     | Apache    |
+----+-----------+-----------+

SELECT User, Host FROM mysql.user;
+--------------+-----------------+
| User         | Host            |
+--------------+-----------------+
| PUBLIC       |                 |
| user_sys_ops | 192.168.100.217 |
| lb_user      | 192.168.100.53  |
| user_sys_ops | 192.168.100.53  |
| mariadb.sys  | localhost       |
| mysql        | localhost       |
| root         | localhost       |
+--------------+-----------------+
  • Install dan Konfigurasi Web Server
  • Install Nginx pada server web
yum module reset nginx -y
yum module enable nginx:1.22 -y
yum install nginx -y
systemctl enable --now nginx
  • Install PHP 8.2 pada server web
rpm -Uvh http://rpms.famillecollet.com/enterprise/remi-release-8.rpm
yum module reset php -y
yum module enable php:remi-8.2 -y
yum install -y php php-common php-mcrypt php-cli php-gd php-curl php-mysql php-xml php-mbstring php-zip php-ldap php-xmlrpc php-curl php-fileinfo php-intl php-libvirt php-devel php-fpm php-pecl-memcache php-pecl-memcached php-pecl-apcu php-pdo php-redis php-sqlite3 php-opcache php-soap php-apcu php-imagick
  • Setting file /etc/php.ini
file_uploads = On
upload_max_filesize = 1000M
post_max_size = 1000M
max_file_uploads = 20
memory_limit = 512M
max_input_time = 300
max_execution_time = 300
date.timezone = Asia/Jakarta
display_errors = On
  • Setting php-fpm pada file /etc/php-fpm.d/www.conf
[www] >> [sys-ops.id]
user = nginx
group = nginx
listen = /run/php-fpm/www.sock
listen.allowed_clients = 127.0.0.1
pm.max_children = 30
pm.start_servers = 10
pm.min_spare_servers = 10
pm.max_spare_servers = 30
pm.process_idle_timeout = 30s
  • Setting Virtual Host
  • Buat directory pada /var/www/
mkdir /var/www/lb.sideka.my.id
  • Download file sample web
wget https://github.com/sideka-cloud/sample-web/archive/refs/heads/main.zip
unzip main.zip
cp -r sample-web-main/* /var/www/lb.sideka.my.id/
chown -R nginx:nginx /var/www/
  • Buat file konfigurasi virtual host pada directory /etc/httpd/conf.d/
server {
   listen 80;
   server_name lb.sideka.my.id;
   root /var/www/lb.sideka.my.id;
   access_log /var/log/nginx/lb.sideka.my.id-access.log  combined;
   error_log /var/log/nginx/lb.sideka.my.id-error.log;

   location / {
       index index.php index.htm index.html;
       try_files $uri $uri/ /index.php?$args;
   }

   add_header X-Frame-Options "SAMEORIGIN";
   add_header X-Content-Type-Options "nosniff";
   add_header X-Xss-Protection "1; mode=block";
   add_header Referrer-Policy strict-origin-when-cross-origin;
   add_header Strict-Transport-Security "max-age=15552000; includeSubDomains;preload";

   location ~* /\.(?!well-known\/) {
       deny all;
   }

   location ~\.(ini|log|conf)$ {
      deny all;
   }

   location ~ \.php$ {
      include /etc/nginx/fastcgi_params;
      fastcgi_pass unix:/run/php-fpm/www.sock;
      fastcgi_index index.php;
      fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
      include fastcgi_params;
      fastcgi_read_timeout 300;
      proxy_read_timeout 600;
   }
}
  • Restart service nginx dan php-fpm
systemctl restart nginx php-fpm
  • Akses dan verifikasi sample web
  • Akan terlihat bahwa record data yang sebelumnya dibuat bisa diakses
  • Koneksi server database diarahkan ke db01
  • Lakukan pengujian failover dengan stop server db01 dan terlihat koneksi berpindah ke db02

herdiana3389

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