Suatu hari nanti, mungkin sebagian dari pembaca artikel ini akan membutuhkan sesuatu yang disebut replikasi database MySQL. Salah satu fungsi dari replikasi ini adalah untuk high availability karena database akan direplikasi ke dua server database yang berbeda
Sebagai contoh misalkan ada dua server MySQL dan pastikan MySQL sudah terinstall di masing-masing server, jika belum baca artikel ini Cara Install Nginx, Php7, MySQL 5. 6, PhpMyAdmin Di Centos 7
DB-server1 : 192.168.84.100 DB-server2 : 192.168.81.100
jadi, jika dua database yang ada di server yang berbeda direplikasi, maka apa pun yang terjadi pada tabel di $ [root@DB server1 ~]# mysql> SHOW SLAVE STATUS\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.84.100 Master_User: dbuser_replica Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000008 Read_Master_Log_Pos: 771248 Relay_Log_File: mysql-relay-log.000005 Relay_Log_Pos: 771411 Relay_Master_Log_File: mysql-bin.000008 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: dbny4 $ [root@DB server2 ~]# mysql> SHOW SLAVE STATUS\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.81.100 Master_User: dbuser_replica Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 120 Relay_Log_File: mysql-relay-log.000005 Relay_Log_Pos: 283 Relay_Master_Log_File: mysql-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: dbny4 4, misalnya kita memasukkan data ke $ [root@DB server1 ~]# mysql> SHOW SLAVE STATUS\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.84.100 Master_User: dbuser_replica Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000008 Read_Master_Log_Pos: 771248 Relay_Log_File: mysql-relay-log.000005 Relay_Log_Pos: 771411 Relay_Master_Log_File: mysql-bin.000008 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: dbny4 $ [root@DB server2 ~]# mysql> SHOW SLAVE STATUS\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.81.100 Master_User: dbuser_replica Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 120 Relay_Log_File: mysql-relay-log.000005 Relay_Log_Pos: 283 Relay_Master_Log_File: mysql-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: dbny4 4, maka otomatis data tersebut akan berada di server $ [root@DB server1 ~]# mysql> SHOW SLAVE STATUS\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.84.100 Master_User: dbuser_replica Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000008 Read_Master_Log_Pos: 771248 Relay_Log_File: mysql-relay-log.000005 Relay_Log_Pos: 771411 Relay_Master_Log_File: mysql-bin.000008 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: dbny4 $ [root@DB server2 ~]# mysql> SHOW SLAVE STATUS\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.81.100 Master_User: dbuser_replica Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 120 Relay_Log_File: mysql-relay-log.000005 Relay_Log_Pos: 283 Relay_Master_Log_File: mysql-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: dbny4 6, disinkronkan dan disalin secara otomatis. Sebaliknya, jika ada perubahan data pada tabel database di server _______1_______6, maka akan diperbarui juga di server _______1_______4
Namun, tidak seperti replikasi MySQL Master/Slave, setiap perubahan di server master akan disalin ke server slave, tetapi apa pun yang terjadi pada tabel di server slave tidak akan disalin ke server master. Jadi server slave hanya menerima data
Perbedaan antara replikasi master/master dan master/slave sebenarnya jika replikasi master/master maka kedua layanan slave di setiap server berjalan, jadi keduanya bertindak sebagai master dan slave. Jika fungsi dari slave adalah untuk menerima data dari master, maka jika keduanya berperan sebagai master dan slave maka keduanya secara otomatis akan saling mengirim/menerima data, logis bukan? . Untuk lebih jelasnya, Anda dapat melihat contoh status budak di bawah ini
$ [root@DB server1 ~]# mysql> SHOW SLAVE STATUS\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.84.100 Master_User: dbuser_replica Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000008 Read_Master_Log_Pos: 771248 Relay_Log_File: mysql-relay-log.000005 Relay_Log_Pos: 771411 Relay_Master_Log_File: mysql-bin.000008 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: dbny4 $ [root@DB server2 ~]# mysql> SHOW SLAVE STATUS\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.81.100 Master_User: dbuser_replica Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 120 Relay_Log_File: mysql-relay-log.000005 Relay_Log_Pos: 283 Relay_Master_Log_File: mysql-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: dbny4 _
Ok, sepertinya cukup untuk penjelasan Master/Master dan Master/Slave. Pada artikel kali ini yang akan dipraktekkan adalah konfigurasi master to master atau master/master replikasi mysql
Konfigurasi pada DB-server1
Masukkan server DB-server1 dan edit file $ [root@DB server1 ~]# mysql> SHOW SLAVE STATUS\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.84.100 Master_User: dbuser_replica Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000008 Read_Master_Log_Pos: 771248 Relay_Log_File: mysql-relay-log.000005 Relay_Log_Pos: 771411 Relay_Master_Log_File: mysql-bin.000008 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: dbny4 $ [root@DB server2 ~]# mysql> SHOW SLAVE STATUS\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.81.100 Master_User: dbuser_replica Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 120 Relay_Log_File: mysql-relay-log.000005 Relay_Log_Pos: 283 Relay_Master_Log_File: mysql-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: dbny4 9
$ vi /etc/my.cnf _
lalu tambahkan seperti di bawah ini
server-id=1 log-bin="mysql-bin" binlog-do-db=dbny4 replicate-do-db=dbny4 relay-log="mysql-relay-log" _
jika demikian, simpan dan mulai ulang layanan mysql
$ systemctl restart mysql
Penjelasan
$ vi /etc/my.cnf
_0. kita asumsikan di bawah server DB-server1 id servernya 1 atau bisa dirubah sesuai keinginan, asalkan tidak sama dengan server yang lain
$ vi /etc/my.cnf
_1. ini untuk log bin mysql, biarkan seperti ini
$ vi /etc/my.cnf
_2. diubah sesuai dengan nama database
$ vi /etc/my.cnf
_3. ganti dengan nama database yang akan direplikasi
$ vi /etc/my.cnf
_4. biarkan seperti ini di log relai
kemudian masuk ke console MySQL (masih di server DB-server1) dengan cara ketik $ vi /etc/my.cnf 5 lalu enter seperti dibawah ini
$ mysql mysql>
kemudian cek status master informasi mana dari status master ini yang akan digunakan untuk proses replikasi
mysql> show master status; +------------------+----------+----------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+----------------+------------------+-------------------+ | mysql-bin.000008 | 886372 | m4n4g3r3_dbny4 | | | +------------------+----------+----------------+------------------+-------------------+ 1 row in set (0,00 sec)
Ok konfigurasi pada server DB-server 1 kita tahan sampai disini. Lanjutkan ke konfigurasi server DB-server2
Konfigurasi server DB-server2
Untuk konfigurasi server DB-server2 sama dengan server DB-server1, yang perlu diubah adalah _______8_______6 di file _______1_______9 jadi tampilannya seperti ini
server-id=2 log-bin="mysql-bin" binlog-do-db=dbny4 replicate-do-db=dbny4 relay-log="mysql-relay-log"
bisa kita lihat, _______8________6 disini kita ubah menjadi angka 2. Jika Anda telah menyimpan perubahan pada file _______1_______9 dan restart layanan mysql
Lalu pergi ke konsol mysql DB-server2, lalu jalankan server-id=1 log-bin="mysql-bin" binlog-do-db=dbny4 replicate-do-db=dbny4 relay-log="mysql-relay-log" 0
mysql> show master status; +------------------+----------+----------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+----------------+------------------+-------------------+ | mysql-bin.000003 | 120 | dbny4 | | | +------------------+----------+----------------+------------------+-------------------+ 1 row in set (0,00 sec)
Sekarang, kembali ke server _______1________4 dan masuk ke konsol mysql. Kemudian jalankan perintah mysql seperti di bawah ini
mysql> create user 'dbuser_replica'@'%' identified by 'passwordnya'; mysql> GRANT REPLICATION SLAVE ON *.* TO 'dbuser_replica'@'%';
ubah _______9_______2 seperti yang diinginkan. Pengguna ini nantinya akan digunakan sebagai pengguna untuk replikasi. Sesuaikan juga _______9________3 sesuai dengan password yang diinginkan
Jika demikian, lakukan hal yang sama pada server $ [root@DB server1 ~]# mysql> SHOW SLAVE STATUS\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.84.100 Master_User: dbuser_replica Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000008 Read_Master_Log_Pos: 771248 Relay_Log_File: mysql-relay-log.000005 Relay_Log_Pos: 771411 Relay_Master_Log_File: mysql-bin.000008 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: dbny4 $ [root@DB server2 ~]# mysql> SHOW SLAVE STATUS\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.81.100 Master_User: dbuser_replica Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 120 Relay_Log_File: mysql-relay-log.000005 Relay_Log_Pos: 283 Relay_Master_Log_File: mysql-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: dbny4 6. Pastikan user dan password sama
Selanjutnya, kembali ke server _______1________4, lalu jalankan perintah di bawah ini
$ [root@DB server1 ~]# mysql> SHOW SLAVE STATUS\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.84.100 Master_User: dbuser_replica Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000008 Read_Master_Log_Pos: 771248 Relay_Log_File: mysql-relay-log.000005 Relay_Log_Pos: 771411 Relay_Master_Log_File: mysql-bin.000008 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: dbny4 $ [root@DB server2 ~]# mysql> SHOW SLAVE STATUS\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.81.100 Master_User: dbuser_replica Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 120 Relay_Log_File: mysql-relay-log.000005 Relay_Log_Pos: 283 Relay_Master_Log_File: mysql-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: dbny4 _0
Isi _______9________6 dengan alamat IP _______1_______6. Ubah server-id=1 log-bin="mysql-bin" binlog-do-db=dbny4 replicate-do-db=dbny4 relay-log="mysql-relay-log" _8 dan server-id=1 log-bin="mysql-bin" binlog-do-db=dbny4 replicate-do-db=dbny4 relay-log="mysql-relay-log" 9 sesuai dengan informasi yang muncul di server-id=1 log-bin="mysql-bin" binlog-do-db=dbny4 replicate-do-db=dbny4 relay-log="mysql-relay-log" 0 di server $ [root@DB server1 ~]# mysql> SHOW SLAVE STATUS\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.84.100 Master_User: dbuser_replica Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000008 Read_Master_Log_Pos: 771248 Relay_Log_File: mysql-relay-log.000005 Relay_Log_Pos: 771411 Relay_Master_Log_File: mysql-bin.000008 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: dbny4 $ [root@DB server2 ~]# mysql> SHOW SLAVE STATUS\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.81.100 Master_User: dbuser_replica Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 120 Relay_Log_File: mysql-relay-log.000005 Relay_Log_Pos: 283 Relay_Master_Log_File: mysql-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: dbny4 6
Lakukan hal yang sama di server $ [root@DB server1 ~]# mysql> SHOW SLAVE STATUS\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.84.100 Master_User: dbuser_replica Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000008 Read_Master_Log_Pos: 771248 Relay_Log_File: mysql-relay-log.000005 Relay_Log_Pos: 771411 Relay_Master_Log_File: mysql-bin.000008 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: dbny4 $ [root@DB server2 ~]# mysql> SHOW SLAVE STATUS\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.81.100 Master_User: dbuser_replica Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 120 Relay_Log_File: mysql-relay-log.000005 Relay_Log_Pos: 283 Relay_Master_Log_File: mysql-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: dbny4 _6 tetapi ubah di server-id=1 log-bin="mysql-bin" binlog-do-db=dbny4 replicate-do-db=dbny4 relay-log="mysql-relay-log" 6 dengan IP $ [root@DB server1 ~]# mysql> SHOW SLAVE STATUS\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.84.100 Master_User: dbuser_replica Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000008 Read_Master_Log_Pos: 771248 Relay_Log_File: mysql-relay-log.000005 Relay_Log_Pos: 771411 Relay_Master_Log_File: mysql-bin.000008 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: dbny4 $ [root@DB server2 ~]# mysql> SHOW SLAVE STATUS\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.81.100 Master_User: dbuser_replica Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 120 Relay_Log_File: mysql-relay-log.000005 Relay_Log_Pos: 283 Relay_Master_Log_File: mysql-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: dbny4 4, dan ubah $ systemctl restart mysql 5, $ systemctl restart mysql 6 menjadi server-id=1 log-bin="mysql-bin" binlog-do-db=dbny4 replicate-do-db=dbny4 relay-log="mysql-relay-log" 0 di server $ [root@DB server1 ~]# mysql> SHOW SLAVE STATUS\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.84.100 Master_User: dbuser_replica Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000008 Read_Master_Log_Pos: 771248 Relay_Log_File: mysql-relay-log.000005 Relay_Log_Pos: 771411 Relay_Master_Log_File: mysql-bin.000008 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: dbny4 $ [root@DB server2 ~]# mysql> SHOW SLAVE STATUS\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.81.100 Master_User: dbuser_replica Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 120 Relay_Log_File: mysql-relay-log.000005 Relay_Log_Pos: 283 Relay_Master_Log_File: mysql-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: dbny4 4
Jika semua hal di atas sudah dilakukan. Jalankan perintah ini di kedua server
$ [root@DB server1 ~]# mysql> SHOW SLAVE STATUS\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.84.100 Master_User: dbuser_replica Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000008 Read_Master_Log_Pos: 771248 Relay_Log_File: mysql-relay-log.000005 Relay_Log_Pos: 771411 Relay_Master_Log_File: mysql-bin.000008 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: dbny4 $ [root@DB server2 ~]# mysql> SHOW SLAVE STATUS\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.81.100 Master_User: dbuser_replica Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 120 Relay_Log_File: mysql-relay-log.000005 Relay_Log_Pos: 283 Relay_Master_Log_File: mysql-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: dbny4 _1
Selesai
Jika konfigurasi berjalan lancar dan berhasil, maka jika dicentang dengan perintah _______10_______9 pada setiap server akan muncul seperti ini
DB-server1
$ [root@DB server1 ~]# mysql> SHOW SLAVE STATUS\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.84.100 Master_User: dbuser_replica Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000008 Read_Master_Log_Pos: 771248 Relay_Log_File: mysql-relay-log.000005 Relay_Log_Pos: 771411 Relay_Master_Log_File: mysql-bin.000008 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: dbny4 $ [root@DB server2 ~]# mysql> SHOW SLAVE STATUS\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.81.100 Master_User: dbuser_replica Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 120 Relay_Log_File: mysql-relay-log.000005 Relay_Log_Pos: 283 Relay_Master_Log_File: mysql-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: dbny4 _2
DB-server2
$ [root@DB server1 ~]# mysql> SHOW SLAVE STATUS\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.84.100 Master_User: dbuser_replica Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000008 Read_Master_Log_Pos: 771248 Relay_Log_File: mysql-relay-log.000005 Relay_Log_Pos: 771411 Relay_Master_Log_File: mysql-bin.000008 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: dbny4 $ [root@DB server2 ~]# mysql> SHOW SLAVE STATUS\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.81.100 Master_User: dbuser_replica Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 120 Relay_Log_File: mysql-relay-log.000005 Relay_Log_Pos: 283 Relay_Master_Log_File: mysql-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: dbny4 _3
Bagaimana cara menguji bahwa replikasi benar-benar berhasil?
Mughi
Mughi adalah Content Writer di Pasarhosting. Dia memulai karirnya sebagai seorang musafir untuk mendapatkan kitab suci dengan dukungan gurunya, menemukan jati dirinya, tidak pernah berhenti bertindak semaunya, monyet sakti, menjadi penjaga mencari kitab suci, monyet sakti, liar, nakal, brutal, membuat semua orang menjadi gila, monyet ajaib, hanya hukuman yang bisa menghentikannya