Manage MariaDB Galera Cluster with ClusterControl on Ubuntu

Megatur MariaDB Galera Cluster dengan ClusterControl pada Ubuntu

  • Maxscale : 10.76.6.214
  • DB01 : 10.76.6.166
  • DB02 : 10.76.6.140
  • ClusterControl : 10.76.6.168
  • Spesifikasi server yang digunakan : 2 CPU 2GB RAM 20GB Disk

Konfigurasi MariaDB Galera Cluster (Ubuntu 22.04)

  • Install MariaDB 10.7 pada DB01 dan DB02
apt update && apt upgrade -y
apt install curl software-properties-common dirmngr gnupg2 rsync lsof net-tools -y
curl -LsS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash -s -- --mariadb-server-version=10.7
apt install mariadb-server mariadb-client -y
  • Cek versi mariadb
mysql --version
   mysql  Ver 15.1 Distrib 10.7.5-MariaDB, for debian-linux-gnu (x86_64) using  EditLine wrapper
  • Jalankan mysql_secure_installation pada DB01 dan DB02
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 DB01 dan DB02
touch /var/log/mariadb.log
chown mysql:mysql /var/log/mariadb.log
  • Stop service mariadb pada DB01 dan DB02
systemctl stop mariadb
  • Konfigurasi MariaDB Galera Cluster pada DB01 dan DB02
nano /etc/mysql/mariadb.conf.d/60-galera.cnf
  • DB01
[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

[galera]
# Mandatory settings
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_name="cluster_sys-ops-id"
wsrep_cluster_address="gcomm://10.76.6.166,10.76.6.140"
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
wsrep_sst_method=rsync
wsrep_node_address="10.76.6.166"
wsrep_node_name="node_1"
bind-address=0.0.0.0
  • DB02
[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

[galera]
# Mandatory settings
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_name="cluster_sys-ops-id"
wsrep_cluster_address="gcomm://10.76.6.166,10.76.6.140"
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
wsrep_sst_method=rsync
wsrep_node_address="10.76.6.140"
wsrep_node_name="node_2"
bind-address=0.0.0.0
  • Jalankan galera cluster pada DB01, pastikan tidak ada error
galera_new_cluster
  • Jalankan service mariadb pada DB02, pastikan tidak ada error
systemctl start mariadb
  • Verifikasi Galera Cluster pada DB01 dan DB02, pastikan status antar node ESTABLISHED
lsof -i:4567
   COMMAND   PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
   mariadbd 3796 mysql    8u  IPv4  32348      0t0  TCP *:4567 (LISTEN)
   mariadbd 3796 mysql   49u  IPv4  32839      0t0  TCP db01:4567->10.76.6.140:43134 (ESTABLISHED)
-----------------
lsof -i:4567
   COMMAND   PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
   mariadbd 3793 mysql    8u  IPv4  32791      0t0  TCP *:4567 (LISTEN)
   mariadbd 3793 mysql   10u  IPv4  32793      0t0  TCP db02:43134->10.76.6.166:4567 (ESTABLISHED)
  • Masuk ke dalam mariadb console, pastikan status wsrep_local_state_comment : synced dengan wsrep_cluster_size : 2 (karena hanya ada 2 node saja)
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        | 2              |
| WSREP_CLUSTER_STATUS      | Primary        |
| WSREP_CONNECTED           | ON             |
| WSREP_READY               | ON             |
+---------------------------+----------------+

Konfigurasi Maxscale (Ubuntu 22.04)

  • Install MariaDB Maxscale 6.4.2 (terbaru)
apt update && apt upgrade -y && apt install net-tools -y
curl -LsS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash -s -- --mariadb-server-version=10.7
apt install maxscale -y
  • 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=10.76.6.166
port=3306
protocol=MariaDBBackend

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

#Define Monitoring Service
[Galera-Monitor]
type=monitor
module=galeramon
servers=db01,db02
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
user=lb_user
password=lb_P@ssw0rd!

[Galera-ReadWrite-Service]
type=service
router=readwritesplit
servers=db01,db02
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!
create user 'lb_user'@'10.76.6.214' identified by 'lb_P@ssw0rd!';
grant select on mysql.user to 'lb_user'@'10.76.6.214';
grant select on mysql.roles_mapping to 'lb_user'@'10.76.6.214';
grant select on mysql.db to 'lb_user'@'10.76.6.214';
grant select on mysql.tables_priv to 'lb_user'@'10.76.6.214';
grant select on mysql.columns_priv to 'lb_user'@'10.76.6.214';
grant select on mysql.procs_priv to 'lb_user'@'10.76.6.214';
grant select on mysql.proxies_priv to 'lb_user'@'10.76.6.214';
grant show databases on *.* to 'lb_user'@'10.76.6.214';
flush privileges;
  • Verifikasi file konfigurasi maxscale.cnf , pastikan tidak ada error dan muncul keterangan Configuration was successfully verified.
maxscale -c -U maxscale
...
   2022-09-14 13:06:09   notice : MaxScale started with 2 worker threads.
   2022-09-14 13:06:09   notice : Loading /etc/maxscale.cnf.
   2022-09-14 13:06:09   notice : Module 'readwritesplit' loaded from '/usr/lib/x86_64-linux-gnu/maxscale/libreadwritesplit.so'.
   2022-09-14 13:06:09   notice : Module 'readconnroute' loaded from '/usr/lib/x86_64-linux-gnu/maxscale/libreadconnroute.so'.
   2022-09-14 13:06:09   notice : Module 'galeramon' loaded from '/usr/lib/x86_64-linux-gnu/maxscale/libgaleramon.so'.
   2022-09-14 13:06:09   notice : Configuration was successfully verified.
   2022-09-14 13:06:09   notice : MaxScale is shutting down.
   2022-09-14 13:06:09   notice : Stopped MaxScale REST API
   2022-09-14 13:06:09   notice : All workers have shut down.
   2022-09-14 13:06:09   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   │ 10.76.6.166 │ 3306 │ 0           │ Master, Synced, Running │      │
├────────┼─────────────┼──────┼─────────────┼─────────────────────────┼──────┤
│ db02   │ 10.76.6.140 │ 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 │
├───────────────────────────┼────────────────┼─────────────┼───────────────────┼────────────┤
│ Galera-ReadWrite-Service  │ readwritesplit │ 0           │ 0                 │ db01, db02 │
└───────────────────────────┴────────────────┴─────────────┴───────────────────┴────────────┘
  • 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 │
└────────────────┴─────────┴────────────┘
  • Monitoring status koneksi dengan watch
watch -n 2 maxctrl list servers
<output>
Every 2.0s: maxctrl list servers            maxscale: Wed Sep 14 20:48:54 2022

┌────────┬─────────────┬──────┬─────────────┬─────────────────────────┬──────┐
│ Server │ Address     │ Port │ Connections │ State                   │ GTID │
├────────┼─────────────┼──────┼─────────────┼─────────────────────────┼──────┤
│ db01   │ 10.76.6.166 │ 3306 │ 8           │ Master, Synced, Running │      │
├────────┼─────────────┼──────┼─────────────┼─────────────────────────┼──────┤
│ db02   │ 10.76.6.140 │ 3306 │ 7           │ Slave, Synced, Running  │      │
└────────┴─────────────┴──────┴─────────────┴─────────────────────────┴──────┘

Konfigurasi ClusterControl (Ubuntu 20.04)

  • Install MySQL Server
apt update && apt upgrade -y
apt install mysql-server mysql-client -y
  • Install ClusterControl
wget http://www.severalnines.com/downloads/cmon/install-cc && chmod +x install-cc
S9S_CMON_PASSWORD=cmonP@ssw0rd! S9S_ROOT_PASSWORD=rootP@ssw0rd! S9S_DB_PORT=3306 HOST=10.76.6.168 INNODB_BUFFER_POOL_SIZE=1024 ./install-cc
-------------------------------------------------------
----Atau untuk community user, untuk lebih jelas silahkan daftar dulu----
wget -O install-cc https://severalnines.com/scripts/install-cc?nk%2FX4t6qjOHnTiCkKcL53XlYqjgW, && chmod +x install-cc
S9S_CMON_PASSWORD=cmonP@ssw0rd! S9S_ROOT_PASSWORD=rootP@ssw0rd! INNODB_BUFFER_POOL_SIZE=1024 ./install-cc  
  • Pastikan saat installasi tidak ada error
=> ClusterControl installation completed!
Open your web browser to http://10.76.6.168/clustercontrol and
enter an email address and new password for the default Admin User.

Determining network interfaces. This may take a couple of minutes. Do NOT press any key.
Public/external IP => http://103.169.7.130/clustercontrol
Installation successful. If you want to uninstall ClusterControl then run install-cc --uninstall.
  • Akses ClusterControl dengan url: http://ip_address/clustercontrol
  • Buat admin user baru untuk akses ke dalam dashboard ClusterControl
  • Tampilan dashboard ClusterControl
  • Buat ssh key pada server ClusterControl
  • Copy ssh key yang barusan sudah dibuat ke masing-masing server (ClusterControl, DB01, DB02, Maxscale)
ssh-keygen -t rsa
ssh-copy-id -i ~/.ssh/id_rsa [email protected]
ssh-copy-id -i ~/.ssh/id_rsa [email protected]
ssh-copy-id -i ~/.ssh/id_rsa [email protected]
ssh-copy-id -i ~/.ssh/id_rsa [email protected]
  • Import MariaDB Galera Cluster ke dalam ClusterControl
  • Pilih menu Import > MySQL Galera
  • Masukan user root, port ssh: 22, dan nama cluster: SYS-OPS.ID > Continue
  • Masukan port MariaDB: 3306, user mysql: root, password root mysql
  • Aktifkan/ceklis: Information_schema Queries, Automatic Node Discovery, Node AutoRecovery, Cluster AutoRecovery
  • Masukan IP server DB01: 10.76.6.166 > Import
  • Pastikan import MariaDB Galera Cluster berhasil dan tanpa error
  • Tampilan dashboard database cluster pada ClucterControl
  • Import Load Balancer Maxscale ke dalam ClusterControl
  • Masuk ke dalam database cluster > Manage > Load Balancer > Maxscale > Import Maxscale > masukan IP server maxscale: 10.76.6.214 > Import Maxscale
  • Pastikan import maxscale berhasil dan tanpa error
  • Cek topology ClusterControl
  • Aktifkan dashboard prometheus exporter pada ClusterControl
  • Masuk menu Dashboard > Enable Agent Based Monitoring > Data retention: 60 > Enable. Tunggu hingga proses installasi prometheus exporter selesai.

herdiana3389

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