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;
Login ke master server 2 sebagai root di mysql.
- 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)
mantap mas bro…
Trims mas
Wah pengkodeannya lengkap. terimakasih sudah berbagi Gan.
tutorial nya sangat membantu, excellent joss mantap !
mau nanya bang.. harus 3 user yah? fungsi 3 user untuk apa?
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