Skip to content

ノートDwinar

Hidup tuh bercanda, yang serius cuma mati

Menu
  • Home
  • Admin Server
    • Debian
    • RedHat
    • Ubuntu
    • Zimbra
  • Blog
  • Buku & Publishing
  • About
  • Contact
  • Privacy Policy
Menu

Cara Replikasi Database Master – Master

Posted on February 22, 2019February 22, 2019 by dwinar

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.

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Recent Posts

  • Instalasi T-Pot Honeypot Framework
  • Ulang Tahun Excellent 9
  • Cara Setting DNS over HTTPS
  • Cara Upgrade OS Mikrotik
  • Membersihkan Sampah Pada Docker

Recent Comments

  • raihan irfan on Cara Install Docker Toolbox di Windows 10 Home
  • dwinar on Instalasi T-Pot Honeypot Framework
  • dwinar on Instalasi T-Pot Honeypot Framework
  • dwinar on Cara Replikasi Database Master – Slave
  • manda on Cara Replikasi Database Master – Slave

Categories

  • Admin Server
  • Blog
  • Debian
  • RedHat
  • Ubuntu
  • Zimbra

Archives

  • January 2021
  • September 2020
  • June 2020
  • May 2020
  • April 2020
  • March 2020
  • February 2020
  • January 2020
  • December 2019
  • November 2019
  • October 2019
  • September 2019
  • August 2019
  • July 2019
  • June 2019
  • May 2019
  • April 2019
  • March 2019
  • February 2019
  • January 2019
  • December 2018
  • November 2018
  • October 2018
  • September 2018
  • August 2018
  • July 2018

Tags

BBB Blog CentOS CentOS 8 Cockpit cPanel DataBases docer Docker Docker CE Docker Private Registry Docker Toolbox fail2ban Gnome google cloud Honeypot Jitsi Kubernetes Let’s Encrypt Linux lsync MariaDB Mikrotik Nakivo NextCloud Open Source OpenSUSE Redhat RedHat 7 S/MIME SSH SSL T-Pot Ubuntu Video Conference Web Server WFH WHM Windows Zextras Zimbra Zimbra Docs Zimbra Drive Zimbra Drive v2 Zimbra NE
February 2019
MTWTFSS
 123
45678910
11121314151617
18192021222324
25262728 
« Jan   Mar »
© 2023 ノートDwinar | Powered by Minimalist Blog WordPress Theme