Instalasi dan Konfigurasi Replikasi MySQL Master to Master

INFORMASI
Panduan ini menggunakan sistem operasi Ubuntu 12.04 Precise Pangolin. Aplikasi MySQL yang digunakan sebagai MySQL Replication adalah versi MySQL 5.5.38-0ubuntu0.12.04.1-log

APA ITU REPLIKASI MYSQL?
Suatu teknik untuk melakukan copy dan pendistribusian data dan objek-objek database dari satu database ke databse lain yang lokasinya terpisah secara fisik. Dengan menggunakan teknik ini, data dapat didistribusikan ke lokasi yang berbeda melalui koneksi jaringan local maupun internet.

DAFTAR IP SERVER

192.168.1.121 : IP Replikasi SQL 1
192.168.1.122 : IP Replikasi SQL 2

INSTALASI MYSQL SERVER
Install MySQL Server pada kedua server Replikasi SQL 1 & 2 dengan perintah berikut :

apt-get install mysql-server mysql-client

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

[mysqld]
port=3306
datadir=/home/mysql/
socket=/usr/local/mysql/tmp/mysql.sock
user=mysql
old_passwords=0
query_cache_size = 256M
query_cache_type=1
query_cache_limit = 8M

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

Untuk master server 2:

[mysqld]
port=3306
datadir=/home/mysql/
socket=/usr/local/mysql/tmp/mysql.sock
user=mysql
old_passwords=0
query_cache_size = 256M
query_cache_type=1
query_cache_limit = 8M

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

INSTALL REPLIKASI PADA MASING MASING SERVER
Login ke server1 sebagai root.

Buatlah 3 user untuk keperluan replikasi ini: mysqlchkuser, mmm_monitor, mmm_agent, dan replication.
Grant mmm_monitor sebagai client replication.
Grant mmm_agent sebagai super dan client replication.
Grant replication sebagai slave
Flush privileges mysql
Flush juga read lock mysql
Cek status master mysql

Gambaran langkah-langkah diatas dalam perintah dan respon di console seperti berikut:

root@sql1:~# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1028
Server version: 5.5.38-0ubuntu0.12.04.1-log (Ubuntu)

Copyright (c) 2000, 2014, 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 'rahasia';
Query OK, 0 rows affected (0.06 sec)
mysql> create user 'mmm_monitor'@'%' identified by 'rahasia';
Query OK, 0 rows affected (0.06 sec)
mysql> create user 'mmm_agent'@'%' identified by 'rahasia';
Query OK, 0 rows affected (0.00 sec)
mysql> create user 'replication'@'%' identified by 'rahasia';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION CLIENT ON *.* TO 'mmm_monitor'@'%' IDENTIFIED BY 'rahasia';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT SUPER, REPLICATION CLIENT, PROCESS ON *.* TO 'mmm_agent'@'%' IDENTIFIED BY 'rahasia';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%' IDENTIFIED BY 'rahasia';
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)

Dump database master server 1 ke master server 2 dengan mysqldump

mysqldump -u root -p --all-databases --events > /srv/database-master.sql

Pindah file backup ke master server 2 dengan scp,

scp /srv/database-master.sql root@192.168.1.122:/srv/

Unlock table di master 1.

mysql> UNLOCK TABLES;

Salin backup ke mysql master server 2

mysql -u root -p < /srv/database-master.sql

Flush privileges yang ada.
Setup replication master 2

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

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
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.121
                  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)

Cek status server master 2

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

Kembali ke master server 1 dan setting master

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='192.168.1.122', master_port=3306, master_user='replication', -> master_password='rahasia', master_log_file='mysql-bin.000002', master_log_pos=27957968;

Jalankan slave dan cek statusnya

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.122
                  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)

Uji coba replikasi.

Pada console master server 1, silakan lihat daftar database

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

Cobalah buat sebuah database baru

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

Jika tampil database contohreplikasi maka konfigurasi replikasi yang kita lakukan berhasil

Sumber : MySQL-mmm.org (http://mysql-mmm.org/mmm2:guide)

Hafizhul says:

January 31, 2015 at 4:55 pm

mantap mas bro…

Maryadi Arismunandar says:

February 2, 2015 at 3:49 pm

Trims mas

Yusuf Karim says:

October 13, 2015 at 7:30 pm

Wah pengkodeannya lengkap. terimakasih sudah berbagi Gan.

junaidi abdillah says:

August 16, 2016 at 12:30 pm

tutorial nya sangat membantu, excellent joss mantap !

andy says:

January 3, 2017 at 3:54 pm

mau nanya bang.. harus 3 user yah? fungsi 3 user untuk apa?

surya says:

August 26, 2017 at 9:33 am

mksh buat tutorianya..kenapa di saya mysql-bin.000002 selalu berubah kalau servernya saya matikan dan di hidupakn kembali jadi mysql-bin.000003 al hasil jadi nag sincron

Instalasi dan Konfigurasi Replikasi MySQL Master to Master

Instalasi dan Konfigurasi Replikasi MySQL Master to Master

Nugi Abdiansyah

6 Comments

Leave a Reply Cancel Reply

Recent Posts

Categories

Arsip Artikel

Jadwal Training

TENTANG EXCELLENT

KONTAK KAMI

Post Terbaru

Informasi Operasional Excellent Selama Libur Hari Raya Idul Fitri 1445

Nextcloud: Persyaratan dan Instalasi Sistem

JADWAL TRAINING