Cara Replikasi Database Master-Master MariaDB 10 di Ubuntu 16.04

Pada tutorial sebelumnya telah dibahas bagaimana cara melakukan replikasi database MariaDB dengan model Master-Slave. Dengan model replikasi Master-Master, kedua server bertindak sebagai Master dan Slave pada saat yang sama. Jika terjadi perubahan database di salah satu server, perubahan yang sama juga terjadi pada server yang lain. Tidak seperti pada model Master-Slave, jika terjadi perubahan pada Slave tidak mempengaruhi database pada Master.

-Perangkat Percobaan-

Perangkat yang digunakan pada percobaan replikasi:

· Distro: Ubuntu 16.04 LTS · MariaDB: 10.0.29 · IP Server A: 10.0.8.38 · IP Server B: 10.0.8.55

-Konfigurasi MariaDB-

Server A

[INPUT]

1 2 3 4 5 6 7 8 9 10 sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf # baris 29, ubah IP bind-address bind-address = 10.0.8.38 # baris 74, lepas comment server-id = 1 # baris 75, lepas comment log_bin = /var/log/mysql/mysql-bin.log

Server B

[INPUT]

1 2 3 4 5 6 7 8 9 10 sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf # baris 29, ubah IP bind-address bind-address = 10.0.8.55 # baris 74, lepas comment server-id = 2 # baris 75, lepas comment log_bin = /var/log/mysql/mysql-bin.log

Restart service di kedua Server

[INPUT]

1 sudo systemctl restart mysql

-Membuat user replikasi-

Server A

[INPUT]

1 2 3 4 mysql -u root -p   GRANT REPLICATION SLAVE ON *.* TO ‘replica’@’10.0.8.55’ IDENTIFIED BY ‘secret’; FLUSH PRIVILEGES;

Server B

[INPUT]

1 2 3 4 mysql -u root -p   GRANT REPLICATION SLAVE ON *.* TO ‘replica’@’10.0.8.38’ IDENTIFIED BY ‘secret’; FLUSH PRIVILEGES;

-Konfigurasi Slave-

Status Master Server A

[INPUT]

1 2 3 4 5 6 7 SHOW MASTER STATUS;   +——————+———-+————–+——————+ | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | +——————+———-+————–+——————+ | mysql-bin.000001 |      616 |              |                  | +——————+———-+————–+——————+

Status Master Server B

[INPUT]

1 2 3 4 5 6 7 SHOW MASTER STATUS;   +——————+———-+————–+——————+ | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | +——————+———-+————–+——————+ | mysql-bin.000001 |      616 |              |                  | +——————+———-+————–+——————+

-Konfigurasi Slave-

Server A

Koneksi ke Master Server B

[INPUT]

1 2 3 4 5 6 CHANGE MASTER TO MASTER_HOST=’10.0.8.55′, MASTER_USER=’replica’, MASTER_PASSWORD=’secret’, MASTER_LOG_FILE=’mysql-bin.000001′, MASTER_LOG_POS=616;

Jalankan slave

[INPUT]

1 START SLAVE;

Tampilkan status slave, apakah berhasil login ke Master B dan siap menerima replikasi

[INPUT]

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 SHOW SLAVE STATUS \G   *************************** 1. row ***************************                Slave_IO_State: Waiting for master to send event                   Master_Host: 10.0.8.55                   Master_User: replica                   Master_Port: 3306                 Connect_Retry: 60               Master_Log_File: mysql-bin.000001           Read_Master_Log_Pos: 616                Relay_Log_File: mysqld-relay-bin.000002                 Relay_Log_Pos: 535         Relay_Master_Log_File: mysql-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:                    Last_Error:                  Skip_Counter:           Exec_Master_Log_Pos: 616               Relay_Log_Space: 833               Until_Condition: None                Until_Log_File:                 Until_Log_Pos:            Master_SSL_Allowed: No            Master_SSL_CA_File:            Master_SSL_CA_Path:               Master_SSL_Cert:             Master_SSL_Cipher:                Master_SSL_Key:         Seconds_Behind_Master: Master_SSL_Verify_Server_Cert: No                 Last_IO_Errno:                 Last_IO_Error:                Last_SQL_Errno:                Last_SQL_Error:   Replicate_Ignore_Server_Ids:              Master_Server_Id: 2                Master_SSL_Crl:            Master_SSL_Crlpath:                    Using_Gtid: No                   Gtid_IO_Pos:

Server B

Koneksi ke Master Server A

[INPUT]

1 2 3 4 5 6 CHANGE MASTER TO MASTER_HOST=’10.0.8.38′, MASTER_USER=’replica’, MASTER_PASSWORD=’secret’, MASTER_LOG_FILE=’mysql-bin.000001′, MASTER_LOG_POS=616;

Jalankan slave

[INPUT]

1 START SLAVE;

Tampilkan status slave, apakah berhasil login ke Master B dan siap menerima replikasi

[INPUT]

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 SHOW SLAVE STATUS \G   *************************** 1. row ***************************                Slave_IO_State: Waiting for master to send event                   Master_Host: 10.0.8.38                   Master_User: replica                   Master_Port: 3306                 Connect_Retry: 60               Master_Log_File: mysql-bin.000001           Read_Master_Log_Pos: 616                Relay_Log_File: mysqld-relay-bin.000002                 Relay_Log_Pos: 535         Relay_Master_Log_File: mysql-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:                    Last_Error:                  Skip_Counter:           Exec_Master_Log_Pos: 616               Relay_Log_Space: 833               Until_Condition: None                Until_Log_File:                 Until_Log_Pos:            Master_SSL_Allowed: No            Master_SSL_CA_File:            Master_SSL_CA_Path:               Master_SSL_Cert:             Master_SSL_Cipher:                Master_SSL_Key:         Seconds_Behind_Master: Master_SSL_Verify_Server_Cert: No                 Last_IO_Errno:                 Last_IO_Error:                Last_SQL_Errno:                Last_SQL_Error:   Replicate_Ignore_Server_Ids:              Master_Server_Id: 1                Master_SSL_Crl:            Master_SSL_Crlpath:                    Using_Gtid: No                   Gtid_IO_Pos:

-Pengujian-

· Pada Server A buat database baru. · Pada Server B periksa apakah database yang dibuat pada Server A apakah sudah tereplikasi. · Dan lakukan juga sebaliknya pada Server B.

selamat mencoba 🙂

Leave a Reply

Your email address will not be published.