Pada tutorial sebelumnya saya telah membahas replikasi database dengan metode Master-Slave. Dimana dimasing – masing server akan berperan sabagai Master dan Slave. Pada tutorial kali ini akan membahas metode replikasi database Master-Master, dimana kedua server akan berperan sebagai Master dan Slave. 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.
Disini saya menggunakan ubuntu 16.04 sebagai OSnya dan mariadb 10.0.38. Dengan ketentuan sebagai berikut:
Server Master A = 192.168.60.141
Server Master B = 192.168.60.142
Konfigurasi MariaDB
Master A
Buka Konfigurasi Mariadb.
nano /etc/mysql/mariadb.conf.d/50-server.cnf
Menuju baris 29. Ubah bind-address ke ip master.
bind-address = 192.168.60.141
Menuju baris 74. Hilangkan tanda pagar pada server-id dan log_bin.
server-id = 1 log_bin = /var/log/mysql/mysql-bin.log
Master B
Buka Konfigurasi Mariadb.
nano /etc/mysql/mariadb.conf.d/50-server.cnf
Menuju baris 29. Ubah bind-address ke ip master.
bind-address = 192.168.60.142
Menuju baris 74. Hilangkan tanda pagar pada server-id dan log_bin.
server-id = 2 log_bin = /var/log/mysql/mysql-bin.log
Restart service Mariadb.
systemctl restart mysql
Membuat user replikasi
Master A
mysql -u root -p GRANT REPLICATION SLAVE ON *.* TO 'replica'@'192.168.60.142' IDENTIFIED BY 'secret'; FLUSH PRIVILEGES;
Master B
mysql -u root -p GRANT REPLICATION SLAVE ON *.* TO 'replica'@'192.168.60.141' IDENTIFIED BY 'secret'; FLUSH PRIVILEGES;
Konfigurasi Slave
Status Master A
SHOW MASTER STATUS; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000001 | 616 | | | +------------------+----------+--------------+------------------+
Status Master B
SHOW MASTER STATUS; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000001 | 616 | | | +------------------+----------+--------------+------------------+
Konfigurasi Master A
Koneksi ke Master B
CHANGE MASTER TO MASTER_HOST='192.168.60.142', MASTER_USER='replica', MASTER_PASSWORD='secret', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=616;
Jalankan Slave
START SLAVE;
Tampilkan status Slave. Pastikan sudah bisa login ke Master B.
SHOW SLAVE STATUS \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.60.142
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:Konfigurasi Master B
Koneksi ke Master A
CHANGE MASTER TO MASTER_HOST='192.168.60.141', MASTER_USER='replica', MASTER_PASSWORD='secret', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=616;
Jalankan Slave
START SLAVE;
Tampilkan status Slave. Pastikan sudah bisa login ke Master A.
SHOW SLAVE STATUS \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.60.141
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:Ujicoba
Buat database di Master A. Lalu cek di Master B. Jika Berhasil database Master B akan sama dengan database Master A. Uji coba juga database Master B dengan membuat database lalu cek di database Master B.
Ok Segitu aja tutorial kali ini. Makasih telah membaca.