Load Balancing MariaDB Galera Cluster Using MariaDB MaxScale With Encrypt Replication Ubuntu 20.04/22.04

Load Balancing Database MariaDB Galera Cluster menggunakan MariaDB MaxScale dengan Koneksi Replikasi Terenkripsi pada Ubuntu 20.24/22.04

MariaDB Galera Cluster

  • db01 : 192.168.88.11
  • db02 : 192.168.88.12
  • db03 : 192.168.88.13
  • maxscale : 192.168.88.14

Konfigurasi MariaDB Galera Cluster

  • Edit file hosts pada semua node (db01, db02, db03)
nano /etc/hosts

127.0.0.1 localhost
127.0.1.1 ubuntu
192.168.88.11   db01.sys-ops.id db01
192.168.88.12   db02.sys-ops.id db02
192.168.88.13   db03.sys-ops.id db03
  • Update repository semua node (db01, db02, db03)
apt update && apt upgrade -y && apt install net-tools rsync lsof -y && reboot
  • Install MariaDB 10.5 pada semua node (db01, db02, db03) untuk ubuntu 20.04
apt install software-properties-common -y
apt-key adv --fetch-keys 'https://mariadb.org/mariadb_release_signing_key.asc'
add-apt-repository 'deb [arch=amd64] http://mariadb.mirror.globo.tech/repo/10.5/ubuntu focal main'
apt update && apt install mariadb-server mariadb-client -y
  • Jika menggunakan ubuntu 22.04 maka akan emnggunakan mariadb versi terbaru 10.6
apt install mariadb-client mariadb-server -y
  • Jalankan mysql_secure_installation pada semua node (db01, db02, db03)
mysql_secure_installation

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 node
touch /var/log/mariadb.log
chown mysql:mysql /var/log/mariadb.log
  • Stop service mariadb pada semua node
systemctl stop mariadb

  • Buat self-signed TLS certificate pada node db01
mkdir /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:pass2022
openssl rsa -in server-key-enc.pem -out server-key.pem -passin pass:pass2022 -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:pass2022
openssl rsa -in client-key-enc.pem -out client-key.pem -passin pass:pass2022 -passout pass:
cat server-cert.pem client-cert.pem > ca.pem
openssl verify -CAfile ca.pem client-cert.pem server-cert.pem
  • Copy semua file .pem dari node db01 ke node db02 dan db03
scp -r /etc/ssl/mysql/*.pem [email protected]:/etc/ssl/mysql/
  [email protected]'s password:
ca.pem                                         100% 1750   901.8KB/s   00:00
client-cert.pem                                100%  875   416.5KB/s   00:00
client-key-enc.pem                             100% 1062   497.7KB/s   00:00
client-key.pem                                 100%  887   461.7KB/s   00:00
server-cert.pem                                100%  875   487.1KB/s   00:00
server-key-enc.pem                             100% 1062   649.2KB/s   00:00
server-key.pem                                 100%  891   427.2KB/s   00:00

scp -r /etc/ssl/mysql/*.pem [email protected]:/etc/ssl/mysql/
  [email protected]'s password:
ca.pem                                         100% 1750   584.8KB/s   00:00
client-cert.pem                                100%  875   588.8KB/s   00:00
client-key-enc.pem                             100% 1062   788.2KB/s   00:00
client-key.pem                                 100%  887   520.5KB/s   00:00
server-cert.pem                                100%  875   419.7KB/s   00:00
server-key-enc.pem                             100% 1062   637.6KB/s   00:00
server-key.pem                                 100%  891   565.5KB/s   00:00
  • Edit permission file pada directory /etc/ssl/mysql di semua node
chown mysql:mysql /etc/ssl/mysql/ -R
chmod 400 /etc/ssl/mysql/*
chmod 700 /etc/ssl/mysql/
ls -al

total 36
drwx------ 2 mysql mysql 4096 Jul  6 04:42 ./
drwxr-xr-x 6 root  root  4096 Jul  6 04:14 ../
-r-------- 1 mysql mysql 1750 Jul  6 04:42 ca.pem
-r-------- 1 mysql mysql  875 Jul  6 04:42 client-cert.pem
-r-------- 1 mysql mysql 1062 Jul  6 04:42 client-key-enc.pem
-r-------- 1 mysql mysql  887 Jul  6 04:42 client-key.pem
-r-------- 1 mysql mysql  875 Jul  6 04:41 server-cert.pem
-r-------- 1 mysql mysql 1062 Jul  6 04:41 server-key-enc.pem
-r-------- 1 mysql mysql  891 Jul  6 04:42 server-key.pem

  • Edit file /etc/mysql/mariadb.conf.d/60-galera.cnf pada semua node (db01, db02, db03)
nano /etc/mysql/mariadb.conf.d/60-galera.cnf
  • Edit dan sesuaikan pada bagian baris berikut:
  • wsrep_cluster_name > nama cluster
  • wsrep_cluster_address > IP address semua node
  • wsrep_node_address > ip address node
  • 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/lib/galera/libgalera_smm.so
wsrep_cluster_name="galeracluster_sys-ops-id"
wsrep_cluster_address="gcomm://192.168.88.11,192.168.88.12,192.168.88.13"
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
wsrep_sst_method=rsync
wsrep_node_address="192.168.88.11"
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"

  • Jalankan galera cluster pada node db01, pastikan tidak ada error
 galera_new_cluster
  • Jalankan service mariadb pada node 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 node (db01, db02, db03)
lsof -i:4567

COMMAND   PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mariadbd 1010 mysql    8u  IPv4  24530      0t0  TCP *:4567 (LISTEN)
mariadbd 1010 mysql   10u  IPv4  24532      0t0  TCP db01.sys-ops.id:45858->db02.sys-ops.id:4567 (ESTABLISHED)
mariadbd 1010 mysql   11u  IPv4  24533      0t0  TCP db01.sys-ops.id:53776->db03.sys-ops.id:4567 (ESTABLISHED)
  • Verifikasi status SSL pada semua node
MariaDB [(none)]> status
--------------
mysql  Ver 15.1 Distrib 10.5.16-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

Connection id:          44
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.5.16-MariaDB-1:10.5.16+maria~focal mariadb.org binary distribution
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:            /run/mysqld/mysqld.sock
Uptime:                 10 min 14 sec

Threads: 4  Questions: 2258  Slow queries: 0  Opens: 38  Open tables: 28  Queries per second avg: 3.677
--------------

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.88.11; 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.88.11; 
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.backend = asio; 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
  • Cek log mariadb, koneksi replikasi sudah menggunakan ssl
journalctl -eu mariadb
tail -f /var/log/mariadb.log

2022-07-06  8:47:23 0 [Note] WSREP: (b42d08ca-ba3c, 'ssl://0.0.0.0:4567') turning message relay requesting off

Pengujian MariaDB Galera Cluster

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

Konfigurasi MariaDB Maxscale

  • Install mariadb maxscale 6.4.0
apt update && apt upgrade -y && apt install net-tools -y
curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash
apt install maxscale -y
  • Atau bisa menggunakan package berikut:
===MaxScale version 6.4===
### RockyLinux 8
https://dlm.mariadb.com/2718703/MaxScale/6.4.5/packages/rhel/8/x86_64/maxscale-6.4.5-1.rhel.8.x86_64.rpm
### CentOS 7
https://dlm.mariadb.com/2717686/MaxScale/6.4.5/yum/centos/7/x86_64/maxscale-6.4.5-1.rhel.7.x86_64.rpm
### Ubuntu 20.04
https://dlm.mariadb.com/2718737/MaxScale/6.4.5/packages/ubuntu/focal/x86_64/maxscale-6.4.5-1.ubuntu.focal.x86_64.deb
### Ubuntu 22.04
https://dlm.mariadb.com/2718767/MaxScale/6.4.5/packages/ubuntu/jammy/x86_64/maxscale-6.4.5-1.ubuntu.jammy.x86_64.deb
------------------------------------------------------------------------------------------------------------
===MaxScale version 23.02===
### RockyLinux 8
https://dlm.mariadb.com/2896813/MaxScale/23.02.1/rhel/8/x86_64/maxscale-23.02.1-1.rocky.8.x86_64.rpm
### CentOS 7
https://dlm.mariadb.com/2897454/MaxScale/23.02.1/yum/centos/7/x86_64/maxscale-23.02.1-1.rhel.7.x86_64.rpm
### Ubuntu 20.04
https://dlm.mariadb.com/2898504/MaxScale/23.02.1/packages/ubuntu/focal/x86_64/maxscale-23.02.1-1.ubuntu.focal.x86_64.deb
### Ubuntu 22.04
https://dlm.mariadb.com/2898530/MaxScale/23.02.1/packages/ubuntu/jammy/x86_64/maxscale-23.02.1-1.ubuntu.jammy.x86_64.deb
  • Konfigurasi maxscale
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

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

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

[db03]
type=server
address=192.168.88.13
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-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

  • Login ke node db01, tambahkan user baru untuk menghubungkan antara node galera cluster dengan server maxscale. user: lb_user , password: lb_P@ssw0rd!
MariaDB [(none)]> create user 'lb_user'@'192.168.88.14' identified by 'lb_P@ssw0rd!';
MariaDB [(none)]> grant select on mysql.user to 'lb_user'@'192.168.88.14';
MariaDB [(none)]> grant select on mysql.roles_mapping to 'lb_user'@'192.168.88.14';
MariaDB [(none)]> grant select on mysql.db to 'lb_user'@'192.168.88.14';
MariaDB [(none)]> grant select on mysql.tables_priv to 'lb_user'@'192.168.88.14';
MariaDB [(none)]> grant select on mysql.columns_priv to 'lb_user'@'192.168.88.14';
MariaDB [(none)]> grant select on mysql.procs_priv to 'lb_user'@'192.168.88.14';
MariaDB [(none)]> grant select on mysql.proxies_priv to 'lb_user'@'192.168.88.14';
MariaDB [(none)]> grant show databases on *.* to 'lb_user'@'192.168.88.14';
MariaDB [(none)]> flush privileges;
  • Masih di node 1 mariadb (db01), tambahkan juga user baru dengan privileges Full akses agar bisa terhubung secara remote dari manapun. user: supergalera , password: P@ssw0rd!
MariaDB [(none)]> create user supergalera@'%' identified by 'P@ssw0rd!';
MariaDB [(none)]> grant show databases on *.* to supergalera@'%';
MariaDB [(none)]> flush privileges;

  • Verifikasi file konfigurasi maxscale.cnf , pastikan tidak ada error dan muncul keterangan Configuration was successfully verified.
maxscale -c -U maxscale
...
2022-07-06 16:08:07   notice : MaxScale started with 2 worker threads.
2022-07-06 16:08:07   notice : Loading /etc/maxscale.cnf.
2022-07-06 16:08:07   notice : Module 'readwritesplit' loaded from '/usr/lib/x86_64-linux-gnu/maxscale/libreadwritesplit.so'.
2022-07-06 16:08:07   notice : Module 'readconnroute' loaded from '/usr/lib/x86_64-linux-gnu/maxscale/libreadconnroute.so'.
2022-07-06 16:08:07   notice : Module 'galeramon' loaded from '/usr/lib/x86_64-linux-gnu/maxscale/libgaleramon.so'.
2022-07-06 16:08:07   notice : Configuration was successfully verified.
2022-07-06 16:08:07   notice : MaxScale is shutting down.
2022-07-06 16:08:07   notice : Stopped MaxScale REST API
2022-07-06 16:08:07   notice : All workers have shut down.
2022-07-06 16:08:07   notice : MaxScale shutdown completed.
  • Enable service maxscale, lalu cek status maxscale pastikan tidak ada error
systemctl enable --now maxscale
systemctl status maxscale
  • Cek status server node mariadb , status Master ada pada db01
maxctrl list servers
┌────────┬───────────────┬──────┬─────────────┬─────────────────────────┬──────┐
│ Server │ Address       │ Port │ Connections │ State                   │ GTID │
├────────┼───────────────┼──────┼─────────────┼─────────────────────────┼──────┤
│ db01   │ 192.168.88.11 │ 3306 │ 0           │ Master, Synced, Running │      │
├────────┼───────────────┼──────┼─────────────┼─────────────────────────┼──────┤
│ db02   │ 192.168.88.12 │ 3306 │ 0           │ Slave, Synced, Running  │      │
├────────┼───────────────┼──────┼─────────────┼─────────────────────────┼──────┤
│ db03   │ 192.168.88.13 │ 3306 │ 0           │ Slave, Synced, Running  │      │
└────────┴───────────────┴──────┴─────────────┴─────────────────────────┴──────┘
  • Cek status service galera cluster
maxctrl list services
┌───────────────────────────┬────────────────┬─────────────┬───────────────────┬──────────────────┐
│ Service                   │ Router         │ Connections │ Total Connections │ Targets          │
├───────────────────────────┼────────────────┼─────────────┼───────────────────┼──────────────────┤
│ Galera-RoundRobin-Service │ readconnroute  │ 0           │ 0                 │ db01, db02, db03 │
├───────────────────────────┼────────────────┼─────────────┼───────────────────┼──────────────────┤
│ Galera-ReadWrite-Service  │ readwritesplit │ 0           │ 0                 │ db01, db02, db03 │
└───────────────────────────┴────────────────┴─────────────┴───────────────────┴──────────────────┘
  • Cek status listener galera cluster
maxctrl list listeners
┌────────────────────────────┬──────┬─────────┬─────────┬───────────────────────────┐
│ Name                       │ Port │ Host    │ State   │ Service                   │
├────────────────────────────┼──────┼─────────┼─────────┼───────────────────────────┤
│ Galera-RoundRobin-Listener │ 3306 │ 0.0.0.0 │ Running │ Galera-RoundRobin-Service │
├────────────────────────────┼──────┼─────────┼─────────┼───────────────────────────┤
│ Galera-ReadWrite-Listener  │ 3307 │ 0.0.0.0 │ Running │ Galera-ReadWrite-Service  │
└────────────────────────────┴──────┴─────────┴─────────┴───────────────────────────┘
  • Cek status galera monitor
maxctrl list monitors
┌────────────────┬─────────┬──────────────────┐
│ Monitor        │ State   │ Servers          │
├────────────────┼─────────┼──────────────────┤
│ Galera-Monitor │ Running │ db01, db02, db03 │
└────────────────┴─────────┴──────────────────┘
  • Cek versi module maxscale yang sedang digunakan, versi maxscale: 6.4.0
 maxctrl list modules
┌─────────────────┬─────────────────┬─────────┐
│ Module          │ Type            │ Version │
├─────────────────┼─────────────────┼─────────┤
│ maxscale        │ maxscale        │ 6.4.0   │
├─────────────────┼─────────────────┼─────────┤
│ servers         │ servers         │ 6.4.0   │
├─────────────────┼─────────────────┼─────────┤
│ galeramon       │ Monitor         │ V2.0.0  │
├─────────────────┼─────────────────┼─────────┤
│ MariaDBAuth     │ Authenticator   │ V2.1.0  │
├─────────────────┼─────────────────┼─────────┤
│ MariaDBProtocol │ Protocol        │ V1.1.0  │
├─────────────────┼─────────────────┼─────────┤
│ qc_sqlite       │ QueryClassifier │ V1.0.0  │
├─────────────────┼─────────────────┼─────────┤
│ readconnroute   │ Router          │ V2.0.0  │
├─────────────────┼─────────────────┼─────────┤
│ readwritesplit  │ Router          │ V1.1.0  │
└─────────────────┴─────────────────┴─────────┘

  • 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 │
├────────┼───────────────┼──────┼─────────────┼─────────────────────────┼──────┤
│ db01   │ 192.168.88.11 │ 3306 │ 0           │ Down                    │      │
├────────┼───────────────┼──────┼─────────────┼─────────────────────────┼──────┤
│ db02   │ 192.168.88.12 │ 3306 │ 0           │ Master, Synced, Running │      │
├────────┼───────────────┼──────┼─────────────┼─────────────────────────┼──────┤
│ db03   │ 192.168.88.13 │ 3306 │ 0           │ Slave, Synced, Running  │      │
└────────┴───────────────┴──────┴─────────────┴─────────────────────────┴──────┘
  • Jalankan kembali service mariadb pada node db01, lalu cek kembali status server node mariadb, status Master berpindah kembali dari node db02 ke node db01
maxctrl list servers
┌────────┬───────────────┬──────┬─────────────┬─────────────────────────┬──────┐
│ Server │ Address       │ Port │ Connections │ State                   │ GTID │
├────────┼───────────────┼──────┼─────────────┼─────────────────────────┼──────┤
│ db01   │ 192.168.88.11 │ 3306 │ 0           │ Master, Synced, Running │      │
├────────┼───────────────┼──────┼─────────────┼─────────────────────────┼──────┤
│ db02   │ 192.168.88.12 │ 3306 │ 0           │ Slave, Synced, Running  │      │
├────────┼───────────────┼──────┼─────────────┼─────────────────────────┼──────┤
│ db03   │ 192.168.88.13 │ 3306 │ 0           │ Slave, Synced, Running  │      │
└────────┴───────────────┴──────┴─────────────┴─────────────────────────┴──────┘
  • Akses mariadb lewat server maxscale dengan user: supergalera, koneksi database terlihat diarahkan ke server db01
mysql -h 192.168.88.14 -P 3306 -u supergalera -p

MariaDB [(none)]> show variables like 'hostname';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| hostname      | db01  |
+---------------+-------+

MariaDB [(none)]> SELECT USER(), CURRENT_USER();
+---------------------------+----------------+
| USER()                    | CURRENT_USER() |
+---------------------------+----------------+
| [email protected] | supergalera@%  |
+---------------------------+----------------+

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys_ops_id         |
+--------------------+

Update

  • Install MariaDB MaxScale 23.02
curl -LsS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash -s -- --mariadb-server-version=10.10
apt install maxscale -y
  • Aktifkan MaxScale GUI
  • Tambahkan baris berikut dibawah [maxscale]
  • Pada parameter monitor_interval menggunakan satuan detik, contoh: monitor_interval=3s
[maxscale]
threads=auto
admin_host            = 0.0.0.0
admin_port            = 8989
admin_secure_gui      = false
  • Akses MaxScale GUI dengan url: http://ip_address_server:8989
  • user: admin , password: mariadb
  • Add new user: maxctrl create user sysopsuser sysopspass –type admin
  • Tampilan MaxScale GUI 23.02

herdiana3389

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