MySQL master to master replication adalah teknik untuk mereplikasi data antar 2 master mysql atau lebih dimana setiap masternya tetap dijadikan database utama (master) untuk transaksi. Misal User A melakukan transaksi pada aplikasi dan datanya masuk ke DB master pertama, maka data tersebut akan direplikasi/disalin ke DB master kedua. Begitu juga User B melakukan transaksi dan datanya masuk ke DB master kedua, maka akan disalin juga ke DB master pertama.
Cara kerja mysql master to master replication
Contoh dibawah adalah flow dari master to master replication:
Penjelasan:
- Misal ada 2 master mysql, yaitu mysql 1 dan mysql 2
- User 1 menginsert data ABCD dan IJKL ke mysql 1, maka datanya akan tereplikasi ke mysql 2
- Kemudian User 2 menginsert data EFGH ke mysql 2, maka data tereplikasi ke mysql 1
- mysql 1 dan mysql 2 saling bertukar binlog untuk mengcopy data. Detail mengenai binlog akan dijelaskan nanti.
- 5, 15, 21 adalah ID (primary key) dengan autoincrement. ID dibuat renggang karena menggunakan autoincrement offset untuk mencegah terjadinya ID bentrok (duplicate primary key) antar master
- Pada mysql 1 autoincrementnya dibuat dengan (increment 10 dan offset 5), artinya setiap ada data baru akan tergenerate ID 5, 15, 25, 35 dst. Contoh diatas User 1 menginsert 2 data yaitu, ABCD dan IJKL maka ID yang tergenerate adalah 5 dan 15.
- Pada mysql 2 autoincrementnya dibuat dengan (increment 10 dan offset 1), artinya akan tergenerate ID 1, 11, 21, 31, dst. Contoh diatas EFGH akan tergenerate ID 21 (bukan 1 atau 11, karena ID diambil dari ID yang terakhir yaitu 15)
- Setiap master memerlukan nilai autoincrement offset yang berbeda-beda, agar ID setiap masternya tidak sama
- Memang akan ada beberapa ID yang terbuang sia-sia. Contoh pada gambar diatas ID 1,2,3,4,6,7,8,9,10,11,12,13,14,16,19,20 yang terbuang sia-sia. Sengaja saya buatkan gap antar ID agar bila kedepan ada penambahan master (jadi 3 master), masih bisa menggunakan ID yang tersedia
- Mysql master to master replication tidak hanya mereplikasi penambahan data, tapi update dan delete data juga. Dan juga DDL (Alter, Drop, Create)
- Mysql master to master replication menggunakan metode pulling (ambil data). Artinya jika mysql 1 ada data baru, mysql 2 akan ambil data binlog mysql 1
Kenapa pakai MySQL di docker?
Saya pakai mysql di docker karena salah satu server (onpremise) saya sudah terinstall mysql dengan port 3306 (yang mana itu sudah digunakan untuk keperluan lain), jadi saya harus install mysql satu lagi dengan port lain, jadi saya putuskan menggunakan docker.
Disclaimer dulu
Metode Mysql master to master replication dengan docker seperti ini sudah saya implementasikan di pekerjaan, tapi hanya pada environment development (bukan di production). Saya tidak menyarankan menggunakan mysql docker pada environment production. Karena sejauh ini (yang saya tahu) jika mysql berjalan didocker akan lambat performa I/O nya. Harus ada yang ditunning disisi servernya https://forums.docker.com/t/mysql-slow-performance-in-docker/37179/12. Tapi jika digunakan di development, saya rasa tidak masalah (karena data di development tidak banyak)
Seperti apa yang akan kita buat?
Langsung saja lihat gambarnya:
Kita memerlukan 2 server disini. Contoh disini saya menggunakan 1 server di AWS, 1 server lagi di on premise. Masing-masing server akan kita install mysqlnya dengan menggunakan docker dan mysql_data nya akan kita simpan dilevel host lalu kita mount kedalam containernya. Jadi mysql_data nya akan aman bila containernya dihapus. (*mysql_data berisi semua data-data yang ada di database dalam bentuk file).
Antara mysql 1 dan mysql 2 akan saling connect menggunakan IP Public. Port yang digunakan adalah 3407. Nanti juga kita akan buatkan user mysql khusus untuk replikasi dan kita akan set hak aksesnya.
Let’s get started
Saya asumsikan kita sudah menginstall:
- docker
OS yang digunakan pada tutorial ini:
- Ubuntu 20.04
Kita akan menggunakan mysql versi:
- mysql 5.7
Mari kita mulai. Saya akan jabarkan secara mendetail. š
Install Mysql 1 dengan docker
Kita mulai dari mysql yang pertama. Buat folder dengan struktur seperti ini di server pertama:
mysql1/
config/
my.cnf
DockerfileAda 2 folder yaitu mysql1 dan config. Dan 2 file yaitu my.cnf dan Dockerfile. Kita akan fokus pada kedua file tersebut.
my.cnf
my.cnf berisi konfigurasi untuk replikasi mysql. Jadi buat file my.cnf dengan konfigurasi berikut:
[mysqld]
bind-address = 0.0.0.0
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
binlog_do_db = my_db
replicate-do-db = my_db
auto_increment_increment = 10
auto_increment_offset = 5Penjelasan:
- bind-address 0.0.0.0, artinya mysql bisa diakses dari IP manapun. Ini diperlukan karena nanti mysql 2 akan connect ke mysql 1 untuk melakukan replikasi data. Note: Disini saya biarkan terset 0.0.0.0 karena di environment saya, didepan mysql ada firewall lagi (security group) untuk memfilter IP mana saja yang boleh mengakses mysql 1).
- server-id 1, ID server untuk mysql. Setiap mysql yang melakukan replikasi harus didefine server-id nya dan harus unik. Misal ada 2 mysql yang saling replicate, maka mysql tersebut bisa diset server-idnya masing-masing
server-id=1danserver-id=2 - log_bin (binlog), lokasi binary log mysql. Gunanya untuk menyimpan log terkait penambahan ataupun perubahan data (DML) ataupun DDL (Alter, Create, Drop). Misal ada insert data baru (INSERT INTO xx) ke mysql 1, query tersebut akan dicatat kedalam file
binlogdalam bentuk binary.binlogini nantinya akan diconsume oleh mysql 2 untuk diambil datanya. Contoh file binlogmysql-bin-changelog.xxxxxx - expire_logs_days 10, artinya file binlog akan dihapus jika sudah lebih dari 10 hari. Kita perlu set expire_logs_days supaya storage server tidak full.
- max_binlog_size 100M, Artinya setiap file binlog akan dipecah-pecah maksimal 100M perfile. Contohnya
mysql-bin-changelog.000001,mysql-bin-changelog.000002,mysql-bin-changelog.000003, dst - binlog_do_db = my_db, Artinya hanya database my_db yang akan dibuat binlognya. *Disini kita akan menggunakan my_db sebagai contohnya.
- replicate-do-db = my_db, Artinya hanya database my_db yang akan direplikasi oleh mysql 1.
- auto_increment_increment = 10 dan auto_increment_offset = 5.
Kedua parameter ini sangat penting untuk mencegah duplicate primary key. Jika diset 10 dan 5, artinya ID akan diberi kelipatan 10 setiap rownya (10, 20, 30, dst). Karena ada offset 5, maka ID akan berubah menjadi (5,15, 25, 35, dst). Dimana angka 5 juga harus dimasukkan dalam list karena merupakan awalan offset.
Dockerfile
Buat file Dockerfile dan isikan dengan konfigurasi berikut:
FROM mysql:5.7
COPY config/my.cnf /etc/mysql/conf.d/my.cnf
RUN mkdir /var/log/mysql && chown -R mysql:mysql /var/log/mysql/
Penjelasan:
- FROM mysql:5.7, Artinya kita download image mysql:5.7 dari repository docker.
- COPY config/my.cnf, Artinya salin file my.cnf (yang sudah kita buat sebelumnya) ke dalam container dengan path /etc/mysql/conf.d/my.cnf. *Containernya nanti kita akan buat pada tahap selanjutnya.
- RUN mkdir /var/log/mysql, Artinya buat folder log di /var/log/mysql. Jika nanti ada error mysql, kita bisa lihat errornya pada folder ini.
- chown -R mysql:mysql /var/log/mysql, Artinya change owner ke user mysql supaya mysql bisa me-write log ke folder mysql.
Buat Image
Sekarang buat imagenya, masuk kedalam folder mysql1 lalu jalankan command berikut:
docker build -t mysql-my-db:latest .Akan terbuat image baru dengan nama mysql-my-db:latest. Image ini bisa kalian push ke container repository sebagai base image dari mysql kalian (opsional). Tapi disini saya tidak akan jelaskan caranya, karena bakal terlalu panjang š
Untuk melihat imagenya bisa dengan command
docker images mysql-my-db:latestBuat Container
Selanjutnya buat container dari image yang sudah kita buat sebelumnya (mysql-my-db).
docker run --name mysql-my-db -d -p 3407:3306 -e MYSQL_ROOT_PASSWORD=pass987 --restart unless-stopped -v mysql-my-db:/var/lib/mysql mysql-my-db:latestPenjelasan:
- docker run, Artinya membuat container baru
- –name mysql-my-db = nama container barunya
- -d = jalan di detach mode
- -p 3407:3306 = mengeksport 3407 agar bisa diakses dari luar container, sedangkan 3306 merupakan port internal container (mysql)
- -e MYSQL_ROOT_PASSWORD=pass987, Artinya set root password mysql menggunakan environment variable, disini kita set passwordnya pass987
- –restart unless-stopped = mysql akan direstart jika terjadi crash, kecuali stopped maka tidak direstart.
- mysql-my-db:/var/lib/mysql = diawal sudah saya jelaskan.. jadi mysql_data akan ditaruh dihost. mysql_data akan disimpan didalam folder mysql-my-db yang ada dihost. Kemudian mysql-my-db akan dimount ke dalam container pada path /var/lib/mysql
- mysql-my-db:latest = Image yang sudah dibuat sebelumnya. Kita akan buat container dari image ini
Setelah container terbuat, kita bisa cek apakah containernya sudah berjalan
$ docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
d196ba17de4a mysql-my-db:latest "docker-entrypoint.sā¦" 8 minutes ago Up 8 minutes 33060/tcp, 0.0.0.0:3407->3306/tcp, :::3407->3306/tcp mysql-my-dbKita bisa cek juga mysql_data (_data) disimpan pada folder mysql-my-db di host
$ ls -al /var/lib/docker/volumes/mysql-my-db/
total 12
drwx-----x 3 root root 4096 Jul 18 09:21 .
drwx-----x 7 root root 4096 Jul 18 09:21 ..
drwxrwxrwt 5 systemd-coredump systemd-coredump 4096 Jul 18 09:22 _dataTest connect mysql 1 lewat terminal (local testing)
Pertama kita perlu cari tahu berapa IP dockernya dengan command ifconfig
$ ifconfig
docker0: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
inet 172.17.0.1 netmask 255.255.0.0 broadcast 172.17.255.255IP dockernya adalah 172.17.0.1, kemudian connect ke mysql 1 menggunakan command berikut, lalu masukkan passwordnya (pass987)
mysql -u root -p -h 172.17.0.1 -P 3407
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
....
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>Bagus sudah bisa login ke mysql 1. Selanjutnya kita akan install mysql 2.
Install Mysql 2 dengan docker
Sebagian besar sama caranya seperti install mysql 1, cm ada beberapa value yang perlu disesuaikan.
Pertama kita buat juga sturktur foldernya di server kedua
mysql2/
config/
my.cnf
DockerfileSelanjutnya konfigurasi my.cnf dan Dockerfile
my.cnf
[mysqld]
bind-address = 0.0.0.0
server-id = 2
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
binlog_do_db = my_db
replicate-do-db = my_db
auto_increment_increment = 10
auto_increment_offset = 1Hanya server-id dan auto_increment_offset yang kita bedakan valuenya.
Dockerfile
FROM mysql:5.7
COPY config/my.cnf /etc/mysql/conf.d/my.cnf
RUN mkdir /var/log/mysql && chown -R mysql:mysql /var/log/mysql/Buat Image
Jalankan command berikut di folder mysql2
docker build -t mysql-my-db:latest .Buat Container
Jalankan command berikut
docker run --name mysql-my-db -d -p 3407:3306 -e MYSQL_ROOT_PASSWORD=pass987 --restart unless-stopped -v mysql-my-db:/var/lib/mysql mysql-my-db:latestTest connect mysql 2 lewat terminal (local testing)
Commandnya sama seperti sebelumnya:
mysql -u root -p -h 172.17.0.1 -P 3407Test koneksi mysql lewat IP Public
Nantinya mysql 1 dan mysql 2 akan berkomunikasi lewat IP Public agar bisa saling terhubung. Jadi kita harus pastikan mysql 1 bisa mengakses mysql 2, mysql 2 bisa mengakses mysql 1.
Untuk mengetestnya kita bisa test koneksi mysql melalui IP Public. Jalankan command berikut pada mysql 1, kita akan coba connect ke mysql 2 melalui IP Public.
mysql -u root -p -h 180.118.89.11 -P 3407Pada mysql 2, kita akan coba connect ke mysql 1
mysql -u root -p -h 55.44.33.11 -P 3407Jika hasilnya bisa saling connect, maka aman. Jika tidak pastikan port 3407 dan IP public kedua server sudah diallow di bagian firewall (security group, ip tables, atau semacamnya)
Buat database my_db pada kedua mysql
Tadi kita sudah bisa connect pada kedua mysqlnya. Sekarang buat database pada kedua mysql tersebut.
mysql> create database my_db;
Query OK, 1 row affected (0.01 sec)Catat binlog position pada kedua mysql
Kita perlu catat binlog position pada kedua mysql. Karena nanti antara mysql 1 dan mysql 2 akan bertukar binlog untuk mencopy data, jadi harus tahu posisi binlognya sudah sampai mana.
Jalankan command berikut di mysql 1:
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 321 | my_db | | |
+------------------+----------+--------------+------------------+-------------------+Penjelasan:
- Posisi binlog ada di file mysql-bin.0000003
- Posisi binlog dalam byte ada di posisi 321
- Yang kita buat binlognya hanya database my_db (sesuai my.cnf yang kita buat)
- Note: file binlog nantinya akan terus bertambah dan berubah posisinya. Contohnya kedepan mungkin akan menjadi
mysql-bin.000005dan posisinya88811
Jalankan juga commandnya pada mysql 2
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 321 | my_db | | |
+------------------+----------+--------------+------------------+-------------------+Oke ternyata kebetulan sama file dan posisi binlognya dengan yang pertama.
Kedua posisi binlog ini nantinya akan kita gunakan untuk menjalankan command CHANGE MASTER TO .. sebuah statement yang digunakan untuk mereplikasi data.
Buat user replikasi dan set hak aksesnya
Pada saat replikasi data nanti, kita akan menggunakan sebuah user. Jadi kita buat user dengan nama ‘replication’ di kedua mysql dan berikan hak akses untuk bisa mereplikasi data.
Buat User Replikasi
Buat user replication di mysql 1
CREATE USER 'replication'@'180.118.89.11' IDENTIFIED BY 'rep987';Penjelasan:
- Kita buat user dengan nama: replication
- 180.118.89.11 artinya user ‘replication’ dapat diakses dari IP 180.118.89.11
- Dimana IP 180.118.89.11 merupakan IP public dari server ke 2 (mysql 2)
- Set password usernya dengan password rep987
Buat user replication di mysql 2
CREATE USER 'replication'@'55.44.33.11' IDENTIFIED BY 'rep987';Penjelasan:
- Yang perlu dibedakan hanyalah IP 55.44.33.11, karena user ini nantinya akan bisa diakses dari mysql 1 – server 1 (IP 55.44.33.11)
Set Hak Akses User
Beri hak akses ke user ‘replication’ pada kedua mysql tersebut. Hak akses yang diperlukan untuk replikasi data adalah REPLICATION SLAVE
Pada mysql 1 jalankan command berikut
GRANT REPLICATION SLAVE ON *.* TO 'replication'@'180.118.89.11';Pada mysql 2 jalankan command berikut
GRANT REPLICATION SLAVE ON *.* TO 'replication'@'55.44.33.11';Setup “CHANGE MASTER TO..”
CHANGE MASTER TO adalah sebuah statement yang digunakan untuk mereplikasi data. Metodenya adalah pulling (ambil data binlog). Gampangnya, bayangkan saja seperti git pull.
Statement ini kita harus jalankan di kedua mysql.
Jalankan pada mysql 1
mysql> CHANGE MASTER TO MASTER_HOST = '180.118.89.11',
MASTER_USER = 'replication',
MASTER_PASSWORD = 'rep987',
MASTER_PORT = 3407,
MASTER_LOG_FILE = 'mysql-bin.000003',
MASTER_LOG_POS = 321;Penjelasan:
- Dari mysql 1 kita akan connect ke mysql 2, jadi kita set MASTER_HOST ke IP server 2 (180.118.89.11)
- MASTER_USER = ‘replication’, connect ke mysql 2 dengan user replication
- MASTER_PASSWORD = ‘rep987’, password user replication pada mysql 2
- MASTER_PORT = 3407, port pada mysql 2
- MASTER_LOG_FILE = ‘mysql-bin.000003’, posisi file binlog pada mysql 2 (yang sudah kita catat sebelumnya)
- MASTER_LOG_POS = 321, posisi byte binlog pada mysql 2 (yang sudah kita catat sebelumnya)
Jalankan pada mysql 2
CHANGE MASTER TO MASTER_HOST = '55.44.33.11',
MASTER_USER = 'replication',
MASTER_PASSWORD = 'rep987',
MASTER_PORT = 3407,
MASTER_LOG_FILE = 'mysql-bin.000003',
MASTER_LOG_POS = 321;Check ‘SHOW SLAVE STATUS’
Setelah dua-duanya sudah kita set CHANGE MASTER TO, selanjutnya kita cek hasil konfigurasinya pada kedua mysql tersebut apakah sudah benar?
Pada mysql 1
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 180.118.89.11
Master_User: replication
Master_Port: 3407
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 321
Relay_Log_File: d196ba17de4a-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB: my_db
...............Bisa kita lihat, Relay_Master_Log_File = mysql-bin.0000003 dan Read_master_Log_Pos = 321. Artinya mysql 1 saat ini berada pada file mysql-bin.0000003 dan byte 321 pada mysql 2 (180.118.89.11).
Pada mysql 2
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 55.44.33.11
Master_User: replication
Master_Port: 3407
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 321
Relay_Log_File: 7f870fc9ccfd-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB: my_db
Oke, kedua konfigurasi sudah benar. Kita lanjut ke step berikutnya.
Jalankan Replikasi (START SLAVE)
Jalankan replikasi di mysql 1 dan mysql 2 dengan command berikut
mysql> start slave;Selanjutnya kita cek lagi menggunakan SHOW SLAVE STATUS untuk mengetahui apakah replikasinya sudah berjalan?
Pada mysql 1 jalankan lagi command:
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 180.118.89.11
................
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updatesPenjelasan:
- Slave_IO_State: Waiting for master to send event, Artinya mysql 1 sudah connect ke mysql 2 dan sedang menunggu event apakah ada binlog baru yang tersedia? Jika ada maka akan dipull
- Slave_SQL_Running_State: Slave has read all relay log; ….., Artinya mysql 1 sudah membaca semua binlog dari mysql 2, artinya data pada mysql 2 dan mysql 1 sudah sinkron
*Jika hasil output sudah seperti diatas, berarti replikasi sudah berjalan dengan baik
Pada mysql 2 jalankan juga commandnya
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 55.44.33.11
......
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Nice, dua-duanya sudah berjalan dengan baik replikasinya.
Testing Replikasi: Buat Table Baru
Sekarang kita test replikasinya dengan membuat table baru di mysql 1. Apakah nanti tablenya akan tereplikasi ke mysql 2? Let’s see.
Pada mysql 1 kita buat table dengan nama mst_user
mysql> use my_db;
mysql> CREATE TABLE mst_user(id INT NOT NULL AUTO_INCREMENT, name VARCHAR(255), PRIMARY KEY(id));Lalu pada mysql 2 kita cek tablenya. Jika tablenya ada berarti tereplikasi dengan baik.
mysql> use my_db;
mysql> show tables;
+-----------------+
| Tables_in_my_db |
+-----------------+
| mst_user |
+-----------------+
Testing Replikasi: Insert Data
Sekarang kita akan test insert data.
Pada mysql 1 jalankan query insert berikut
mysql> INSERT INTO mst_user(id, name) VALUES(NULL,'moko');
mysql> INSERT INTO mst_user(id, name) VALUES(NULL,'ucup');Lalu cek di mysql 2 datanya, harusnya ada data yang tampil.
mysql> select * from mst_user;
+----+------+
| id | name |
+----+------+
| 5 | moko |
| 15 | ucup |
+----+------+Penjelasan:
- Kita insert data lewat mysql 1, maka ID yang tercreate adalah 5, 15 (increment 10, offset 5) seperti yang diawal sudah dijelaskan
Sekarang kita akan coba insert dari mysql 2
mysql> INSERT INTO mst_user(id, name) VALUES(NULL,'udin');Pada mysql 1, cek datanya. Harusnya sekarang ada 3 data
mysql> select * from mst_user;
+----+------+
| id | name |
+----+------+
| 5 | moko |
| 15 | ucup |
| 21 | udin |
+----+------+Penjelasan:
- Karena kita insert lewat mysql 2, maka ID yang tercreate adalah 21 (increment 10, offset 1) sesuai yang diawal sudah dijelaskan
Penutup
Oke, sudah panjang bener tutorialnya. Saatnya ditutup.
Sebenarnya masih ada beberapa lagi yang mau dishare soal SHOW SLAVE STATUS, karena disana kita bisa monitoring replikasinya, apakah replikasinya sedang delay, atau ada error, gimana cara benerin replikasi yang error? dan yang lainnya. Mungkin nanti saya akan buatkan tutorialnya terpisah (kalau sempat).
Baiklah, akhir kata semoga tutorial MySQL master to master replication ini bermanfaat buat yang membutuhkan. Sampai jumpa!
