Replikasi Database MySQL Master to Master

Persiapan

Untuk dapat mencoba melakukan replikasi master to master mysql ini, silakan persiapkan (minimal) 2 server mysql siap pakai, contoh dengan IP 10.2.9.1 dan 10.2.9.2.

Langkah Replikasi Master to Master MySQL

1. Sesuaikan konfigurasi masing-masing server MySQL sebagai master. Ubah pada tiap file /etc/my.cnf.
Untuk master server 1 misalnya:

[mysqld]
port=3306
datadir=/home/mysql/
socket=/usr/local/mysql/tmp/mysql.sock
user=mysql
old_passwords=0
#symbolic-links=0
#query_cache_size = 268435456
query_cache_size = 256M
query_cache_type=1
query_cache_limit = 1048576
log = 1
long_query_time = 1
slow_query_log	= 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
general_log	= 1
general_log_file =	/var/log/mysqld.log
server_id           = 1
log_bin             = /var/log/mysql/mysql-bin.log
log_bin_index       = /var/log/mysql/mysql-bin.log.index
relay_log           = /var/log/mysql/mysql-relay-bin
relay_log_index     = /var/log/mysql/mysql-relay-bin.index
expire_logs_days    = 10
max_binlog_size     = 100M
log_slave_updates   = 1
bind-address = 0.0.0.0
local-infile = 0

sedangkan untuk server master 2:

[mysqld]
port=3306
datadir=/home/mysql
socket=/usr/local/mysql/tmp/mysql.sock
user=mysql
old_passwords=0
#symbolic-links=0
query_cache_size = 268435456
query_cache_type=1
query_cache_limit=1048576
log = 1
long_query_time = 1
slow_query_log	= 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
general_log	= 1
general_log_file =	/var/log/mysqld.log
server_id           = 2
log_bin             = /var/log/mysql/mysql-bin.log
log_bin_index       = /var/log/mysql/mysql-bin.log.index
relay_log           = /var/log/mysql/mysql-relay-bin
relay_log_index     = /var/log/mysql/mysql-relay-bin.index
expire_logs_days    = 10
max_binlog_size     = 100M
log_slave_updates   = 1
bind-address = 0.0.0.0
local-infile = 0

2. Instal replikasi ke masing-masing server, login ke server1 sebagai root.
a. buatlah 3 user untuk keperluan replikasi ini: mysqlchkuser, mmm_monitor, mmm_agent, dan replication.
b. Grant mmm_monitor sebagai client replication.
c. Grant mmm_agent sebagai super dan client replication.
d. Grant replication sebagai slave
e. flush privileges mysql
f. flush juga read lock mysql
g. cek status master mysql
gambaran langkah-langkah diatas dalam perintah dan respon di console seperti berikut:

mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 36
Server version: 5.5.12-log Source distribution
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement
mysql> create user 'mysqlchkuser'@'localhost' identified by 'mysql321';
Query OK, 0 rows affected (0.06 sec)
mysql> create user 'mmm_monitor'@'%' identified by 'monitor_password';
Query OK, 0 rows affected (0.06 sec)
mysql> create user 'mmm_agent'@'%' identified by 'agent_password';
Query OK, 0 rows affected (0.00 sec)
mysql> create user 'replication'@'%' identified by 'replication_password';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION CLIENT ON *.* TO 'mmm_monitor'@'%' IDENTIFIED BY 'monitor_password';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT SUPER, REPLICATION CLIENT, PROCESS ON *.* TO 'mmm_agent'@'%' IDENTIFIED BY 'agent_password';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%' IDENTIFIED BY 'replication_password';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |     1044 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

h. Dump database master server 1 ke master server 2 dengan mysqldump

mysqldump -u root -p --all-databases > /tmp/database-backup.sql

i. Pindah file backup ke master server 2 dengan scp,

scp /tmp/database-backup.sql 10.2.9.2:~/

j. Unlock table di master 1.
3. Login ke master server 2 sebagai root di mysql.
a. flush privileges yang ada.
b. ubah master mysql dengan master_host = ‘10.2.9.1’ (atau sesuai IP master server 1 anda) berikut dengan port (biasanya 3306), master user (dalam hal ini ‘replication’), password user dan log file mastering nya.

mysql> flush privileges;
Query OK, 0 rows affected (0.46 sec)
mysql> CHANGE MASTER TO master_host='10.2.9.1', master_port=3306, master_user='replication', -> master_password='replication_password', master_log_file='mysql-bin.000001', master_log_pos=1044;

c. jalankan slave pada master server 2 dan cek status slave-nya.

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status \G run command
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.2.9.1
                  Master_User: replication
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 1044
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 253
        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: 1044
              Relay_Log_Space: 409
              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
1 row in set (0.00 sec)

d. cek plu status master-nya

mysql> show master status -> ;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 | 27957968 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 | 27957968 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

4. Kembali ke master server 1 dan setting master
a. Ubah master mysql dengan master host sesuai IP master server 2, sesuaikan port, user, password dan file log-nya.

mysql> CHANGE MASTER TO master_host='10.2.9.2', master_port=3306, master_user='replication', -> master_password='replication_password', master_log_file='mysql-bin.000002', master_log_pos=27957968;
Query OK, 0 rows affected (0.34 sec)

b. jalankan slave dan cek statusnya

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G run command
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.2.9.1
                  Master_User: replication
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 27957968
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 253
        Relay_Master_Log_File: mysql-bin.000002
             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: 27957968
              Relay_Log_Space: 409
              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
1 row in set (0.00 sec)

5. Uji coba replikasi.
a. Pada console master server 1, silakan lihat daftar database
b. Cobalah buat sebuah database baru

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| c4CkV893_B4r7Hue   |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)
mysql> create database contohreplikasi;
Query OK, 1 row affected (0.03 sec)

c. Coba cek di master server 2, harusnya database contohreplikasi otomatis juga dibuatkan.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| c4CkV893_B4r7Hue   |
| contohreplikasi    |
| mysql              |
| performance_schema |
+--------------------+
5 rows in set (0.00 sec)

Sekian. Semoga berhasil dan selamat menikmati replikasi data master to master MySQL.
Referensi:

1. emka.web.id (http://emka.web.id/programming/database/mysql/2012/replikasi-master-to-master-mysql/)
2. MySQL-mmm.org (http://mysql-mmm.org/mmm2:guide)
3. ALinux.web.id (http://alinux.web.id/2011/08/16/replicate-master-to-master-mysql.html)
4. https://ict.unnes.ac.id/artikel/replikasi-database-mysql-master-to-master