Clustering Database with MariaDB Galera Cluster on CentOS 7

Konfigurasi Database Cluster dengan MariaDB Galera Cluster pada CentOS 7

MariaDB Galera Cluster

  • 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

  • Update repository package & install repo epel
yum update -y && yum install epel-release nano wget -y
  • Install MariaDB pada semua node, Tambahkan repository MariaDB 10.5
nano /etc/yum.repos.d/MariaDB.repo
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.5/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
  • Install MariaDB 10.5
yum install MariaDB-client MariaDB-server -y

  • Install Rsync dan lsof pada semua node
yum install rsync lsof -y
  • Tambahkan file hosts pada semua node
nano /etc/hosts

#Server Database
192.168.88.105  db01.server.kita
192.168.88.104  db02.server.kita
192.168.88.103  db03.server.kita
  • Set SELinux Permissive pada semua node
setenforce 0

  • Allow beberapa port berikut pada firewall (jika menggunakan firewalld) pada semua node
firewall-cmd --permanent --zone=public --add-port=3306/tcp
firewall-cmd --permanent --zone=public --add-port=4567/tcp
firewall-cmd --permanent --zone=public --add-port=4568/tcp
firewall-cmd --permanent --zone=public --add-port=4444/tcp
firewall-cmd --permanent --zone=public --add-port=4567/udp
firewall-cmd --permanent --zone=public --add-source=192.168.88.105/32
firewall-cmd --permanent --zone=public --add-source=192.168.88.104/32
firewall-cmd --permanent --zone=public --add-source=192.168.88.103/32
firewall-cmd --reload
firewall-cmd --list-all

public (active)
  target: default
  icmp-block-inversion: no
  interfaces: enp0s3
  sources: 192.168.88.105/32 192.168.88.104/32 192.168.88.103/32
  services: dhcpv6-client ssh
  ports: 3306/tcp 4567/tcp 4568/tcp 4444/tcp 4567/udp
  protocols:
  masquerade: no
  forward-ports:
  source-ports:
  icmp-blocks:
  rich rules:

  • Buat file log untuk log mariadb pada semua node
touch /var/log/mariadb.log
chown mysql:mysql /var/log/mariadb.log

  • Jalankan service mariadb dan secure installation pada semua node
systemctl start mariadb
systemctl enable --now mariadb
mysql_secure_installation
Enter current password for root (enter for none):
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
Thanks for using MariaDB!

Konfigurasi Galera Cluster


  • Setting Galera Cluster pada node 1, edit file /etc/my.cnf.d/server.cnf
nano /etc/my.cnf.d/server.cnf
# this is only for the mysqld standalone daemon
[mysqld]
log_error=/var/log/mariadb.log

# Tweak Setting
innodb-file-per-table = 1
innodb-buffer-pool-size = 512M
innodb-buffer-pool-chunk-size = 128M
innodb-io-capacity = 1000
innodb-io-capacity-max = 10000
innodb-read-io-threads = 8
innodb-write-io-threads = 8
max_connections = 300
thread-cache-size = 300
interactive_timeout = 600
wait_timeout = 600
query-cache-size = 256M
tmp-memory-table-size = 32M
tmp-table-size = 32M
open-files-limit = 40000
table-definition-cache = 5000
table-open-cache = 10000
binlog-cache-size = 1M
thread_stack = 1M
sort_buffer_size = 2M
read_buffer_size = 1M
read_rnd_buffer_size = 1M

[galera]
# Mandatory settings
wsrep_on=ON
wsrep_provider=/usr/lib64/galera-4/libgalera_smm.so
wsrep_cluster_name="galeracluster-icmp_my_id"
wsrep_cluster_address="gcomm://192.168.88.105,192.168.88.104,192.168.88.103"
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
wsrep_sst_method=rsync
wsrep_node_address="192.168.88.105"
wsrep_node_name="node_1"
bind-address=0.0.0.0
  • Setting Galera Cluster pada node 2, edit file /etc/my.cnf.d/server.cnf
# this is only for the mysqld standalone daemon
[mysqld]
log_error=/var/log/mariadb.log

# Tweak Setting
innodb-file-per-table = 1
innodb-buffer-pool-size = 512M
innodb-buffer-pool-chunk-size = 128M
innodb-io-capacity = 1000
innodb-io-capacity-max = 10000
innodb-read-io-threads = 8
innodb-write-io-threads = 8
max_connections = 300
thread-cache-size = 300
interactive_timeout = 600
wait_timeout = 600
query-cache-size = 256M
tmp-memory-table-size = 32M
tmp-table-size = 32M
open-files-limit = 40000
table-definition-cache = 5000
table-open-cache = 10000
binlog-cache-size = 1M
thread_stack = 1M
sort_buffer_size = 2M
read_buffer_size = 1M
read_rnd_buffer_size = 1M

[galera]
# Mandatory settings
wsrep_on=ON
wsrep_provider=/usr/lib64/galera-4/libgalera_smm.so
wsrep_cluster_name="galeracluster-icmp_my_id"
wsrep_cluster_address="gcomm://192.168.88.105,192.168.88.104,192.168.88.103"
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
wsrep_sst_method=rsync
wsrep_node_address="192.168.88.104"
wsrep_node_name="node_2"
bind-address=0.0.0.0
  • Setting Galera Cluster pada node 3, edit file /etc/my.cnf.d/server.cnf
# this is only for the mysqld standalone daemon
[mysqld]
log_error=/var/log/mariadb.log

# Tweak Setting
innodb-file-per-table = 1
innodb-buffer-pool-size = 512M
innodb-buffer-pool-chunk-size = 128M
innodb-io-capacity = 1000
innodb-io-capacity-max = 10000
innodb-read-io-threads = 8
innodb-write-io-threads = 8
max_connections = 300
thread-cache-size = 300
interactive_timeout = 600
wait_timeout = 600
query-cache-size = 256M
tmp-memory-table-size = 32M
tmp-table-size = 32M
open-files-limit = 40000
table-definition-cache = 5000
table-open-cache = 10000
binlog-cache-size = 1M
thread_stack = 1M
sort_buffer_size = 2M
read_buffer_size = 1M
read_rnd_buffer_size = 1M

[galera]
# Mandatory settings
wsrep_on=ON
wsrep_provider=/usr/lib64/galera-4/libgalera_smm.so
wsrep_cluster_name="galeracluster-icmp_my_id"
wsrep_cluster_address="gcomm://192.168.88.105,192.168.88.104,192.168.88.103"
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
wsrep_sst_method=rsync
wsrep_node_address="192.168.88.103"
wsrep_node_name="node_3"
bind-address=0.0.0.0

  • Stop service mariadb pada semua node
systemctl stop mariadb
  • Jalankan Galera Cluster pada node 1, cukup di jalankan hanya pada node 1 saja
[root@db01 icmp.my.id]# galera_new_cluster
  • Masukan ke dalam database mariadb pada node 1, lalu verifikasi jumlah node, akan terdapat 1 node yang aktif yaitu node 1
[root@db01 icmp.my.id]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 7
Server version: 10.5.13-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show status like 'wsrep_cluster_size';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 1     |
+--------------------+-------+
1 row in set (0.037 sec)
  • Jalankan service mariadb pada node 2 dan node 3
[root@db02 icmp.my.id]# systemctl start mariadb
[root@db03 icmp.my.id]# systemctl start mariadb
  • Cek kembali status node yang aktif pada node 1, node aktif pada galera cluster sudah menjadi 3 node
MariaDB [(none)]> show status like 'wsrep_cluster_size';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 3     |
+--------------------+-------+
1 row in set (0.047 sec)

  • 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:    6a1f102a-13a3-11e7-b710-b2876418a643
seqno:   -1
safe_to_bootstrap: 1
  • Jika semua node mariadb-nya mati semua (stoped), jalankan kembali galera_new_cluster pada node 1 kemudian start service mariadb pada node 2 dan node 3

  • Cek Verifikasi glera replication traffic pada port 4567 di semua node, semua node terkoneksi satu sama lain
[root@db01 icmp.my.id]# lsof -i:4567
COMMAND    PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mariadbd 12833 mysql    8u  IPv4  38560      0t0  TCP *:tram (LISTEN)
mariadbd 12833 mysql    9u  IPv4  38561      0t0  TCP db01.server.kita:54754->db03.server.kita:tram (ESTABLISHED)
mariadbd 12833 mysql   10u  IPv4  38562      0t0  TCP db01.server.kita:44826->db02.server.kita:tram (ESTABLISHED)
[root@db02 icmp.my.id]# lsof -i:4567
COMMAND   PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mariadbd 9984 mysql    8u  IPv4  35534      0t0  TCP *:tram (LISTEN)
mariadbd 9984 mysql    9u  IPv4  35535      0t0  TCP db02.server.kita:41114->db03.server.kita:tram (ESTABLISHED)
mariadbd 9984 mysql   15u  IPv4  36187      0t0  TCP db02.server.kita:tram->db01.server.kita:44826 (ESTABLISHED)
[root@db03 icmp.my.id]# lsof -i:4567
COMMAND   PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mariadbd 9871 mysql    8u  IPv4  34864      0t0  TCP *:tram (LISTEN)
mariadbd 9871 mysql   24u  IPv4  35089      0t0  TCP db03.server.kita:tram->db01.server.kita:54754 (ESTABLISHED)
mariadbd 9871 mysql   49u  IPv4  34982      0t0  TCP db03.server.kita:tram->db02.server.kita:41114 (ESTABLISHED)

Pengujian MariaDB Galera Cluster


  • Buat database icmp_my_id dan tabel webdata pada node 1
[root@db01 icmp.my.id]# mysql -u root -p

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

MariaDB [(none)]> use icmp_my_id;
Database changed

MariaDB [icmp_my_id]> CREATE TABLE `icmp_my_id`.`webdata` (`id` INT NOT NULL AUTO_INCREMENT, `data_base` VARCHAR(20) NOT NULL, `webserver` VARCHAR(20) NOT NULL, PRIMARY KEY (`id`)) ENGINE = InnoDB;
Query OK, 0 rows affected (0.024 sec)

MariaDB [icmp_my_id]> show tables;
+----------------------+
| Tables_in_icmp_my_id |
+----------------------+
| webdata              |
+----------------------+
1 row in set (0.001 sec)

MariaDB [icmp_my_id]> describe webdata;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| id        | int(11)     | NO   | PRI | NULL    | auto_increment |
| data_base | varchar(20) | NO   |     | NULL    |                |
| webserver | varchar(20) | NO   |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
3 rows in set (0.001 sec)
  • Tambahkan data ke dalam tabel webdata pada database icmp_my_id lewat node 2
[root@db02 icmp.my.id]# mysql -u root -p

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| icmp_my_id         |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.019 sec)

MariaDB [(none)]> use icmp_my_id;
Database changed

MariaDB [icmp_my_id]> INSERT INTO `webdata` (`id`, `data_base`, `webserver`) VALUES ('1', 'MySQL', 'Apache');
Query OK, 1 row affected (0.037 sec)

MariaDB [icmp_my_id]> INSERT INTO `webdata` (`id`, `data_base`, `webserver`) VALUES ('2', 'MariaDB', 'Nginx');
Query OK, 1 row affected (0.031 sec)

MariaDB [icmp_my_id]> INSERT INTO `webdata` (`id`, `data_base`, `webserver`) VALUES ('3', 'MsSQL', 'IIS');
Query OK, 1 row affected (0.004 sec)

MariaDB [icmp_my_id]> select * from webdata;
+----+-----------+-----------+
| id | data_base | webserver |
+----+-----------+-----------+
|  1 | MySQL     | Apache    |
|  2 | MariaDB   | Nginx     |
|  3 | MsSQL     | IIS       |
+----+-----------+-----------+
3 rows in set (0.005 sec)
  • Cek isi table webdata pada node 1 dan node 3
[root@db01 icmp.my.id]# mysql -u root -p -e 'SELECT * FROM icmp_my_id.webdata;'
Enter password:
+----+-----------+-----------+
| id | data_base | webserver |
+----+-----------+-----------+
|  1 | MySQL     | Apache    |
|  2 | MariaDB   | Nginx     |
|  3 | MsSQL     | IIS       |
+----+-----------+-----------+

[root@db03 icmp.my.id]# mysql -u root -p -e 'SELECT * FROM icmp_my_id.webdata;'
Enter password:
+----+-----------+-----------+
| id | data_base | webserver |
+----+-----------+-----------+
|  1 | MySQL     | Apache    |
|  2 | MariaDB   | Nginx     |
|  3 | MsSQL     | IIS       |
+----+-----------+-----------+
  • Hapus data pada tabel webdata dengan id=3 lewat node 3
[root@db03 icmp.my.id]# mysql -u root -p -e 'DELETE FROM icmp_my_id.webdata WHERE `webdata`.`id` = 3;'
Enter password:
  • Cek isi database pada node 1 dan node 2
[root@db01 icmp.my.id]# mysql -u root -p -e 'SELECT * FROM icmp_my_id.webdata;'
Enter password:
+----+-----------+-----------+
| id | data_base | webserver |
+----+-----------+-----------+
|  1 | MySQL     | Apache    |
|  2 | MariaDB   | Nginx     |
+----+-----------+-----------+

[root@db02 icmp.my.id]# mysql -u root -p -e 'SELECT * FROM icmp_my_id.webdata;'
Enter password:
+----+-----------+-----------+
| id | data_base | webserver |
+----+-----------+-----------+
|  1 | MySQL     | Apache    |
|  2 | MariaDB   | Nginx     |
+----+-----------+-----------+

  • Jika ingin menambahkan server node baru pada galera cluster, dengan cara menambahkan setiap IP dari node yang akan di jadikan anggota cluster pada /etc/hosts dan /etc/my.cnf.d/server.cnf
  • Matikan service mariadb pada semua node cluster kemudian edit file konfigurasi, lalu buat galera cluster baru dan start service mariadb semua node.
  • Jika salah satu node bermasalah/mati, cukup jalankan kembali service mariadb
  • Jika node master bermasalah/mati, lakukan stop semua service mariadb pada node slave, lalu jalankan mysqld -umysql –wsrep-new-cluster pada node master, kemudian jalankan kembali service mariadb pada semua node, lalu buat baru galera cluster

herdiana3389

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