Topologi Replikasi Database MySQL, Master to Master dan Master to Slave
Pengenalan Replikasi Database MySQL
Replikasi Database MySQL adalah melakukan sinkronisasi database agar saling entry atau isi database. Selain itu replikasi database bertujuan untuk membuat backup secara realtime apabila Server database MySQL utama (Master) offline / down maka kita tetap bisa mendapatkan data saat entry terakhir dilakukan oleh user di database Backup (Slave). Replikasi Database MySQL ada dua macam:
Master to Master MySQL Replication
Web 1 dan Web 2 bertindak kedua – dua nya sebagai master. Apabila ada Update database di Web 1 maka secara realtime akan di salin ke Web2. Apabila ada update database di Web2 maka secara realtime akan di salin ke Web 1.
Master to Slave MySQL Replication
Web 1 bertindak sebagai database utama (Master), apabila ada update di database Web 1 maka secara realtime di salin ke Web 3. Web 3 hanya bersifat sebagai backup (Slave). Web 3 selalu standby aktif menunggu salinan data dari Web 1.
Konfigurasi Database MySQL Web 1
Konfigurasi my.cnf Web1
root@web1:~# vim /etc/mysql/my.cnf
[mysqld]
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
#Nama Database yg akan kita replikasi
binlog_do_db = kurusetra
binlog_ignore_db = mysql
binlog_ignore_db = test
#bind-address = 127.0.0.1
Restart MySQL Web1
root@web1:~# service mysql restart
Replicator Database Web1
root@web1:~# mysql
mysql> create database kurusetra;
mysql> create user ‘replicator’@'%’ identified by ‘password’;
mysql> grant replication slave on *.* to ‘replicator’@'%’;
mysql> \q
Konfigurasi Database MySQL Web2
Konfigurasi my.cnf Web2
root@web2:~# vim /etc/mysql/my.cnf
[mysqld]
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
server-id = 2
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = kurusetra
binlog_ignore_db = mysql
binlog_ignore_db = test
#bind-address = 127.0.0.1
Restart MySQL Web2
root@web2:~# service mysql restart
Replicator Database Web2
root@web2:~# mysql
mysql> create database kurusetra;
mysql> create user ‘replicator’@'%’ identified by ‘password’;
mysql> grant replication slave on *.* to ‘replicator’@'%’;
mysql> \q
Pengujian Replikasi Database MySQL
Konfigurasi Replikasi Web1 ke Web2
## POSISI DI WEB1 ##
root@web1:~# mysql
mysql> show master status;
+——————+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+————–+——————+
| mysql-bin.000004 | 107 | kurusetra | mysql,test |
+——————+———-+————–+——————+
1 row in set (0.00 sec)
# 107 adalah posisi awal sebelum database kurusetra di isi dengan tabel
## POSISI DI WEB2 ##
# KITA JADIKAN WEB1 SEBAGAI MASTER WEB2
root@web2:~# mysql
mysql> slave stop;
mysql> CHANGE MASTER TO MASTER_HOST=’192.168.57.40′, MASTER_USER=’replicator’, MASTER_PASSWORD=’password’,MASTER_LOG_FILE=’mysql-bin.000004′, MASTER_LOG_POS=107;
mysql> start slave;
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.57.45
Master_User: replicator
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 107
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000004
#Slave_IO_Running: Yes dan Slave_SQL_Running: Yes
#Berarti Web2 siap melakukan replikasi tabel database kurusetra dari Web1
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
mysql> show master status;
+——————+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+————–+——————+
| mysql-bin.000004 | 1065 | kurusetra | mysql,test |
+——————+———-+————–+——————+
1 row in set (0.00 sec)
Konfigurasi Replikasi Web2 ke Web1
#POSISI DI WEB1
#KITA JADIKAN WEB2 SEBAGAI MASTER WEB1
root@web1:~# mysql
mysql> slave stop;
mysql> CHANGE MASTER TO MASTER_HOST=’192.168.57.41′, MASTER_USER=’replicator’, MASTER_PASSWORD=’password’, MASTER_LOG_FILE=’mysql-bin.000004′, MASTER_LOG_POS=1065;
mysql> slave start;
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.57.46
Master_User: replicator
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 1065
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Pengujian Replikasi Web1 ke Web2
## POSISI KEMBALI KE WEB1 ##
mysql> use kurusetra;
mysql> create table dariweb1 (mid int(11) auto_increment, PRIMARY KEY
-> (mid)) Engine=MyISAM;
mysql> insert into dariweb1 () values ();
##CEK TABEL DI WEB2 (POSISI DI WEB2) ##
mysql> use kurusetra;
mysql> show tables;
#TABEL YANG DIBUAT DI WEB1
#MUNCUL DI WEB2
mysql> show tables;
+———————+
| Tables_in_kurusetra |
+———————+
| dariweb1 |
+———————+
mysql> select * from dariweb1
-> ;
+—–+
| mid |
+—–+
| 1 |
+—–+
Pengujian Replikasi Web2 ke Web1
#POSISI DI WEB2
root@web2:~# mysql
mysql> use kurusetra;
mysql> create table dariweb2 (mid int(11) auto_increment, PRIMARY KEY
-> (mid)) Engine=MyISAM;
mysql> insert into dariweb2 () values ();
mysql> insert into dariweb1 () values ();
#POSISI DI WEB1
root@web1:~# mysql
mysql> use kurusetra;
mysql> show tables;
+———————+
| Tables_in_kurusetra |
+———————+
| dariweb1 |
| dariweb2 |
+———————+
mysql> select * from dariweb2;
+—–+
| mid |
+—–+
| 1 |
+—–+
mysql> select * from dariweb1;
+—–+
| mid |
+—–+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
+—–+
