Untuk dapat mencoba melakukan replikasi master to master mysql ini, silakan persiapkan (minimal) 2 server mysql siap pakai, contoh dengan IP 192.168.1 dan 192.168.2
1. Sesuaikan konfigurasi masing-masing server MySQL sebagai master. Ubah pada tiap file /etc/my.cnf.
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
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 192.168.1.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 = ’192.168.1.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='192.168.1.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: 192.168.1.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='192.168.1.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: 192.168.1.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. Replikasi Database http://bptik.unnes.ac.id/artikel/replikasi-database-mysql-master-to-master/
