MariaDB adalah sistem manajemen database relasional yang dikembangkan dari MySQL. MariaDB dikembangkan oleh komunitas pengembang yang sebelumnya berkontribusi untuk database MySQL.
Di MariaDB terdapat beberapa macam metode replikasi yang dapat digunakan dan yang paling familiar yaitu replikasi database mariadb _ master – slave _ dan _ master – master._
Jika Anda ingin menggunakan master – master mariadb maka Anda perlu tahu terlebih dahulu kelebihan dan kekurangan diantaranya:
# Kelebihan
1. Aplikasi dapat membaca (read) dari semua node master
2. Mendistribusikan beban tulis (write) ke semua node master
3. Simple, automatic dan quick failover
# Kekurangan
1. Konsisten secara longgar atau kurang kosisten.
2. Tidak sesederhana replikasi master-slave dalam segi konfigurasi dan penerapannya.
Beberapa sumber menyebutkan penggunaan database master – master akan meningkatkan performa dari website Anda yang menggunakan database server.
Untuk mengikuti tutorial kali ini pastikan Anda sudah mempunyai 2 VM dan sudah melakukan instalasi database MariaDB, jika belum silakan mengikuti tutorial berikut: _ Cara Instalasi Database MariaDB di CentOS 8 _
Berikut detail IP masing – masing VM yang akan digunakan
IP Apps01(master): 192.168.10.9
IP Apps02(master01): 192.168.10.18
Pastikan semua service database di masing – masing VM Anda running
1
2
3
4
5
6
7
8
9
10
11
## Node Apps01
---------------
[root@apps01 ~]# systemctl status mariadb |grep Active
Active: active (running) since Sat 2020-08-29 13:56:03 UTC; 7h ago
[root@apps01 ~]#
## Node Apps02
---------------
[root@apps02 ~]# systemctl status mariadb |grep Active
Active: active (running) since Sat 2020-08-29 13:51:52 UTC; 7h ago
[root@apps02 ~]#
Selanjutnya konfigurasi database mariadb di masing – masing VM
# Node Apps01 (Master)
Buka file mariadb-server.conf untuk melakukan konfigurasi database mariadb
1
2
[root@apps01 ~]#
[root@apps01 ~]# vim /etc/my.cnf.d/mariadb-server.cnf
Tambahkan beberapa baris dibawah [mysqld] detailnya seperti berikut
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
# this is read by the standalone daemon and embedded servers
[server]
# this is only for the mysqld standalone daemon
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mysqld/mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
[mysqld]
server-id = 1
report_host = master
log_bin = /var/lib/mysql/mariadb-bin
log_bin_index = /var/lib/mysql/mariadb-bin.index
relay_log = /var/lib/mysql/relay-bin
relay_log_index = /var/lib/mysql/relay-bin.index
#datadir=/var/lib/mysql
#socket=/var/lib/mysql/mysql.sock
#log-error=/var/log/mariadb/mariadb.log
#pid-file=/run/mariadb/mariadb.pid
Simpan dan lakukan restart terhadap mariadb server
1
2
3
[root@apps01 ~]#
[root@apps01 ~]# systemctl restart mariadb
[root@apps01 ~]#
# Node Apps02 (Master)
Tahapan ini sama dengan tahapan yang di VM Apps01 Anda hanya peru mengubah server-id dan repost_host nya saja
1
2
[root@apps02 ~]#
[root@apps02 ~]# vim /etc/my.cnf.d/mariadb-server.cnf
Berikut full konfigurasinya
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
# this is read by the standalone daemon and embedded servers
[server]
# this is only for the mysqld standalone daemon
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mysqld/mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
[mysqld]
server-id = 2
report_host = master2
log_bin = /var/lib/mysql/mariadb-bin
log_bin_index = /var/lib/mysql/mariadb-bin.index
relay_log = /var/lib/mysql/relay-bin
relay_log_index = /var/lib/mysql/relay-bin.index
#datadir=/var/lib/mysql
#socket=/var/lib/mysql/mysql.sock
#log-error=/var/log/mariadb/mariadb.log
#pid-file=/run/mariadb/mariadb.pid
Silakan simpan dan restart mariadb
1
2
3
[root@apps02 ~]#
[root@apps02 ~]# systemctl restart mariadb
[root@apps02 ~]#
Selanjutnya silakan login ke masing – masing VM database mariadb dan membuat user untuk replikasi nya
# Node Apps01 (Master)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
[root@apps01 ~]#
[root@apps01 ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.17-MariaDB-log 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)]> create user 'test_master'@'%' identified by 'test_master';
Query OK, 0 rows affected (0.002 sec)
MariaDB [(none)]> grant replication slave on *.* to 'test_master'@'%';
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]> show master status;
+--------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| mariadb-bin.000001 | 664 | | |
+--------------------+----------+--------------+------------------+
1 row in set (0.000 sec)
MariaDB [(none)]>
Keterangan: User yang digunakan diatas yaitu test_master dan password nya test_master
Noted: Silakan dicatat hasil dari status master
# Node Apps02 (Master)
Langkah ini sama dengan langkah sebelumnya, silakan login ke mysql dan membuat user untuk replikasi database nya
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
[root@apps02 ~]#
[root@apps02 ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.17-MariaDB-log 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)]> create user 'test_master2'@'%' identified by 'test_master2';
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]> grant replication slave on *.* to 'test_master2'@'%';
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]> show master status;
+--------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| mariadb-bin.000001 | 667 | | |
+--------------------+----------+--------------+------------------+
1 row in set (0.000 sec)
MariaDB [(none)]>
Keterangan: User yang digunakan diatas yaitu test_master2 dan password nya test_master2
Noted: Silakan dicatat hasil dari status master
Selanjutnya menghubungkan dan melakukan replikasi master – master database Apps01 ke Apps02
# Hubungkan Apps01 ke Apps02
Pertama yang harus di lakukan yaitu stop slave
1
2
3
4
5
MariaDB [(none)]>
MariaDB [(none)]> STOP SLAVE;
Query OK, 0 rows affected, 1 warning (0.000 sec)
MariaDB [(none)]>
Hubungkan Apps01 ke Apps02
1
2
3
4
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.10.18', MASTER_USER='test_master2', MASTER_PASSWORD='test_master2', MASTER_LOG_FILE='mariadb-bin.000001', MASTER_LOG_POS=667;
Query OK, 0 rows affected (2.182 sec)
MariaDB [(none)]>
Noted: Silakan sesuaikan username, password database apps02 yang sudah dibuat sebelumnya, untuk Master_Host silakan isi IP Apps02
Selanjutnya start slave
1
2
3
4
MariaDB [(none)]> START SLAVE;
Query OK, 0 rows affected (0.002 sec)
MariaDB [(none)]>
# Hubungkan Apps02 ke Apps01
Langkah kali ini hampir sama dengan langkah sebelumnya, bedanya hanya pada penentuan IP, username dan password mariadb yang telah dibuat sebelumnya.
Silakan stop slave
1
2
3
4
5
MariaDB [(none)]>
MariaDB [(none)]> STOP SLAVE;
Query OK, 0 rows affected, 1 warning (0.001 sec)
MariaDB [(none)]>
Hubungkan Apps02 ke Apps01, pastikan informasi yang ada di apps01 diinput dengan benar
1
2
3
4
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.10.9', MASTER_USER='test_master', MASTER_PASSWORD='test_master', MASTER_LOG_FILE='mariadb-bin.000001', MASTER_LOG_POS=664;
Query OK, 0 rows affected (0.038 sec)
MariaDB [(none)]>
Start slave
1
2
3
4
MariaDB [(none)]> START SLAVE;
Query OK, 0 rows affected (0.002 sec)
MariaDB [(none)]>
Berikutnya cek status dari replikasi di masing – masing VM database Apps01 dan Apps02
# Status Replikasi Master Apps01
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
MariaDB [(none)]> show slave status \G
***************************1. row***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.10.18
Master_User: test_master2
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mariadb-bin.000001
Read_Master_Log_Pos: 667
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 557
Relay_Master_Log_File: mariadb-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 667
Relay_Log_Space: 860
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 2
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: conservative
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Slave_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0
Slave_Transactional_Groups: 0
1 row in set (0.001 sec)
MariaDB [(none)]>
# Status Replikasi Master Apps02
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
MariaDB [(none)]> show slave status \G
***************************1. row***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.10.9
Master_User: test_master
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mariadb-bin.000001
Read_Master_Log_Pos: 664
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 557
Relay_Master_Log_File: mariadb-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 664
Relay_Log_Space: 860
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: conservative
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Slave_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0
Slave_Transactional_Groups: 0
1 row in set (0.001 sec)
MariaDB [(none)]>
Berikut detail capture dari status replikasi masing – masing VM Apps01 dan Apps02
Selanjutnya kita akan mencoba membuat database di Apps01 jika hasil yang dibuat di Apps01 tersimpan atau ada juga atau tereplikasi juga di Apps02 maka konfigurasi replikasi master – master sudah berhasil
Sebaliknya sekarang kita coba buat database di apps02 pastikan di apps01 ada atau tereplikasi
Selamat mencoba 😁