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
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
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
1 | sudo systemctl restart mysql |
Membuat user replikasi
Server A
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
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
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
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
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
1 | START SLAVE; |
Tampilkan status slave, apakah berhasil login ke Master B dan siap menerima replikasi
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: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 616 Relay_Log_Space: 833 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: |
Server B
Koneksi ke Master Server A
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
1 | START SLAVE; |
Tampilkan status slave, apakah berhasil login ke Master B dan siap menerima replikasi
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: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 616 Relay_Log_Space: 833 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: |
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 🙂