Membuat Replikasi Database MySQL/MariaDB Master-Slave

Replikasi database adalah proses menyalin database dari suatu server ke server database lainnya setiap ada perubahan pada database yang dijadikan sebagai master, Sehingga jika kita memiliki database yang sama di beberapa server yang berbeda dan ingin menambah atau menghapus isi database tersebut kita tidak perlu melakukan perubahan di setiap database secara manual, kita cukup merubah isi database di server yang dijadikan master dan otomatis database yang ada di server slave akan ikut berubah.

Tujuan dari penggunaan replikasi database ini banyak, tapi secara umum biasanya digunakan untuk backup database atau untuk keperluan analisa database yang cukup besar, karena saat melakukan analisa database yang memiliki isi cukup banyak biasanya menguras resource server sehingga jika analisa database dilakukan di server produksi tentu akan membebani server tersebut dan akan mempengaruhi kinerja dari situs atau aplikasi yang berjalan di server tersebut.

Pada contoh tutorial ini saya menggunakan 2 server database dan keduanya menggunakan MariaDB, Satu sebagai master dan satunya lagi sebagai slave, Tetapi replikasi database bisa dilakukan antara MySQL dan MariaDB jadi tidak harus MariaDB ke MariaDB tapi juga bisa MySQL ke MariaDB atau sebaliknya, Namun jika replikasi Dari MySQL ke MariaDB mungkin beberapa fitur tidak tersedia misalnya fungsi GTID, karena GTID MySQL dan MariaDB berbeda.

Server yang digunakan pada contoh ini adalah:

  1. Ip 144.202.2.57 Menggunakan MariaDB 10.3 sebagai master dan sudah ada database yang akan direplikasi dengan nama database zonetrik_namadb
  2. IP 45.76.0.113 Menggunakan MariaDB 10.3 sebagai slave

Catatan: Untuk yang saya tandai dengan warna merah dibawah ini silahkan disesuaikan dengan server kamu.

Konfigurasi Replikasi Di Server Master

Pertama edit file my.cnf server database kamu melalui SSH, untuk letak file my.cnf biasanya ada di /etc/my.cnf atau /etc/mysql/my.cnf

nano /etc/my.cnf
atau
nano /etc/mysql/my.cnf

Dan tambahkan konfigurasi seperti dibawah ini:

[mariadb]
log-bin
server_id=1
binlog-do-db=zonetrik_namadb
bind-address=144.202.2.57
log-basename=master1

Simpan konfigurasi tersebut dan restart MariaDB dari SSH.

systemctl restart mariadb

Selanjutnya login ke MySQL menggunakan user root di SSH

mysql -u root -p

Dan masukkan password mysql untuk user root kamu. Selanjutnya buat user replikasi dengan perintah:

CREATE USER 'master_replika'@'%' IDENTIFIED BY 'PASSWOD_USER_master_replika';
GRANT REPLICATION SLAVE ON *.* TO 'master_replika'@'%';

master_replika adalah user database yang akan digunakan untuk menjalankan replikasi dan bukan database yang akan direplikasi dan PASSWOD_USER_master_replika adalah passwod untuk user master_replika tersebut.

Kunci tabel agar hanya bisa dibaca dan tidak ada perubahan.

FLUSH TABLES WITH READ LOCK;

Kemudian periksa nama dan posisi log biner pada server master dengan perintah ini:

SHOW MASTER STATUS;
+--------------------+----------+-----------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+-----------------+------------------+
| master1-bin.000002 | 686 | zonetrik_namadb | |
+--------------------+----------+-----------------+------------------+
1 row in set (0.001 sec)

Catat dan simpan nama file dan posisi log tersebut kemudian keluar dari mysql

exit;
Konfigurasi Replikasi Server Master
Klik foto untuk memperbesar

Membuat Backup database dari server master dengan menggunakan mysqldump

mysqldump zonetrik_namadb -u root -p > zonetrik_namadb.sql

Transfer file backup database tersebut ke server slave

scp zonetrik_namadb.sql [email protected]45.76.0.113:/root/

Dan masukkan password root untuk login ke ssh server slave.
Sekarang kembali login ke mysql server master dan buka kunci database yang dikunci tadi.

mysql -u root -p
UNLOCK TABLES;
exit;

Konfigurasi Replikasi Di Server Slave

Selanjutnya konfigurasi di server Slave, Pertama buat dulu database baru di server slave, kamu bisa membuat database dari kontrol panel hosting kamu atau dari command line. Jika menggunakan command line pertama kamu login ke SSH server slave dan masuk ke mysql server slave tersebut.

mysql -u root -p
CREATE USER 'zonetrik_userdb'@'localhost' IDENTIFIED BY 'PASSWOD_DB';
CREATE DATABASE zonetrik_namadb;
GRANT ALL PRIVILEGES ON zonetrik_namadb.* TO 'zonetrik_userdb'@'%' IDENTIFIED BY 'PASSWOD_DB';
FLUSH PRIVILEGES;
exit;

Jika sudah membuat database di server slave selanjutnya Import database yang sudah ditransfer dari server slave tadi dengan perintah:

mysql -u root -p zonetrik_namadb < zonetrik_namadb.sql

Kemudian edi file my.cnf di server slave

nano /etc/my.cnf
atau
nano /etc/mysql/my.cnf

Tambahkan seperti ini di konfigurasi my.cnf mysql kamu:

[mariadb]
server_id=2
replicate-do-db=zonetrik_namadb

server_id merupakan ID untuk identifikasi dan kamu harus membuat nomor yang berbeda untuk setiap server baik dengan server master maupun dengan server slave lainnya. Dan replicate-do-db merupakan nama database yang akan direplikasi.

konfigurasi replication mysql my.cnf
Klik foto untuk memperbesar

Sekarang restart server database di server slave

service mysql restart

Kemudian login kembali ke mysql server slave dan mulai jalankan perintah untuk konfigurasi replikasi server slave.

mysql -u root -p
CHANGE MASTER TO MASTER_HOST='144.202.2.57',
MASTER_USER='master_replika',
MASTER_PASSWORD='PASSWOD_USER_master_replika',
MASTER_PORT=3306,
MASTER_LOG_FILE='master1-bin.000002',
MASTER_LOG_POS=686;

Ganti bagian MASTER_HOST dengan IP server master, MASTER_USER dan MASTER_PASSWORD isi dengan user dan password yang dibuat di server master untuk replikasi tadi, MASTER_LOG_FILE isi dengan nama file log dari server master yang disalin tadi, MASTER_LOG_POS di isi dengan posisi log pada output di server master tadi.
Jika sudah kita mulai server slave dengan perintah:

START SLAVE;
Mulai Replikasi Mysql Server Slave
Klik foto untuk memperbesar

Periksa status replikasi dengan perintah:

SHOW SLAVE STATUS\G;

Contoh Output:

MariaDB [(none)]> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 144.202.2.57
                  Master_User: master_replika
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master1-bin.000002
          Read_Master_Log_Pos: 686
               Relay_Log_File: zonetrik-slave-relay-bin.000002
                Relay_Log_Pos: 546
        Relay_Master_Log_File: master1-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: zonetrik_namadb
          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: 686
              Relay_Log_Space: 1145
              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
               Master_SSL_Crl:
           Master_SSL_Crlpath:
                   Using_Gtid: No
                  Gtid_IO_Pos:
      Replicate_Do_Domain_Ids:
  Replicate_Ignore_Domain_Ids:
                Parallel_Mode: conservative
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
1 row in set (0.00 sec)

ERROR: No query specified

Catatan: Jika output dari SHOW SLAVE STATUS\G; terdapat error message: Can’t connect to MySQL server on ‘144.202.2.57’ (113 “No route to host”) kamu perlu membuka port 3306 untuk koneksi TCP di server Master jika tidak ada pesan error seperti itu maka replikasi database sudah berhasil.
Untuk membuka port 3306 jalankan perintah ini di SSH server master:

Centos dan AlmaLinux
firewall-cmd --zone=public --permanent --add-port=3306/tcp
firewall-cmd --reload

Debian dan Ubuntu
ufw allow 3306/tcp

Setelah itu periksa kembali di server slave dengan perintah:

STOP SLAVE;
START SLAVE;
SHOW SLAVE STATUS\G;

Sampai disini kamu sudah berhasil membuat replikasi Master-Slave MySQL database. Sekarang kamu bisa menambah, mengedit atau menghapus isi database di server master dan otomatis perubahan di server master akan diterapkan di server slave.
Untuk penggunaan database di server slave sama saja seperti penggunaan database pada umumnya menggunakan host, user dan password di server slave tersebut hanya saja jika kamu melakukan perubahan di database server slave itu tidak akan merubah isi database di server master.

Menggunakan GTID (Global Transaction ID) Di MariaDB

GTID merupakan kepanjangan dari Global Transaction ID untuk replikasi database fitur ini mulai tersedia pada MariaDB 10.0 dan yang lebih baru untuk manfaat dan penjelasan lengkap tentang GTID silahkan lihat di halaman Global Transaction ID untuk lebih jelasnya.
Yang perlu diperhatikan adalah implementasi GTID pada MariaDB dan MySQL berbeda sehingga untuk menggunakan GTID dalam replikasi database baik server master dan slave harus menggunakan MariaDB semua atau MySQL semua, dan untuk MariaDB GTID tersedia di versi 10.0 dan yang lebih baru.

Jika server database kamu baik master maupun slave menggunakan MariaDB 10.0 atau yang lebih baru kamu bisa menggunakan GTID di replikasi database kamu, Jika replikasi database kamu sudah berjalan seperti cara di atas kamu cukup rubah konfigurasinya pada server slave dengan perintah seperti ini:

Masuk ke database di server slave

mysql -u root -p
STOP SLAVE;

CHANGE MASTER TO master_host="144.202.2.57",
 master_port=3306,
 master_user="master_replika",
 MASTER_PASSWORD='PASSWOD_USER_master_replika',
 master_use_gtid=current_pos;

START SLAVE;

SHOW SLAVE STATUS\G;

exit;

Menghapus Replikasi Di Server Slave

Dan untuk menghapus replikasi di server slave kamu bisa gunakan perintah ini:

STOP SLAVE;
RESET SLAVE;
RESET SLAVE ALL;

Untuk lebih jelas simak video tutorial cara replikasi database MySQL/MariaDB dibawah ini:

 

Sekian dulu tutorial Membuat Replication MySQL/MariaDB Database Master-Slave semoga membantu.

Leave a Comment

Your email address will not be published.