Open in app

Sign in

Write

Sign in

Contoh Mysql Delayed Replication menggunakan MySql 5.7

Ady Sanjaya
5 min readJun 7, 2023

--

Disaster recovery dengan memanfaatkan replikasi dengan jarak waktu yang sudah ditentukan

Apa itu Delayed Replication?
Delayed replication pada MySql adalah replikasi biasa (master-slave) yang melakukan replikasi tidak secara realtime dengan tujuan untuk memulihkan data yang corrupted, rusak, terhapus ataupun hilang dengan cara mengembalikan data ke waktu sebelum terjadinya corrupted / error.

Setup Requirement
- 2 Vps Ubuntu dengan masing-masing Mysql sudah terinstall.

Steps:

  • Buat contoh database dan tabel (skip jika sudah ada).
create database delayed_replication;
use delayed_replication;
CREATE TABLE employees (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100),
  age INT,
  department VARCHAR(100)
);

CREATE TABLE sales (
  id INT PRIMARY KEY AUTO_INCREMENT,
  employee_id INT,
  amount DECIMAL(10,2),
  sale_date DATE,
  FOREIGN KEY (employee_id) REFERENCES employees(id)
);
INSERT INTO employees (name, age, department)
VALUES ('John Doe', 30, 'Sales'),
       ('Jane Smith', 35, 'Marketing'),
       ('Mike Johnson', 28, 'Finance');

INSERT INTO sales (employee_id, amount, sale_date)
VALUES (1, 100.50, '2023-06-01'),
       (2, 250.20, '2023-06-02'),
       (3, 150.75, '2023-06-03');

pada server master lakukan hal berikut :

  • Buka pengaturan mysql /etc/mysql/mysql.conf.d/mysqld.cnf, tambahkan konfigurasi berikut jika belum ada.
nano /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
log-bin=/var/lib/mysql/mysql-bin
server-id=1
binlog_do_db = delayed_replication #pengaturan jika hanya 1 dari beberapa database yang ingin direplikasikan
  • Restart mysql agar mysql-bin bekerja, gunakan salah satu command berikut.
sudo systemctl restart mysql
#atau
sudo service mysql restart
  • File bin akan tampil sesuai dengan perubahan data yang terjadi di database.
  • Buat user baru yang akan digunakan oleh server slave
CREATE USER 'repl'@'%' IDENTIFIED BY '123qweASD';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;
  • Kunci perubahan data di database server master agar server slave bisa membaca dari posisi perubahan terakhir
FLUSH TABLES WITH READ LOCK;
  • Baca posisi terakhir perubahan di database server master yang nantinya akan dimasukkan di pengaturan server slave
SHOW MASTER STATUS\G;

untuk kasus diatas angka 83537 adalah posisi terakhir perubahan

  • jangan menutup mysql cli untuk mempertahankan lock database

Pada server slave lakukan hal berikut :

  • export database saat ini dari master ke slave
mysqldump -h gantidenganipmaster -u root -p --skip-triggers delayed_replication > delayed_replication.sql
  • cari pengaturan mysql di “server slave” /etc/mysql/mysql.conf.d/mysqld.cnf, tambahkan konfigurasi berikut jika belum ada
nano /etc/mysql/mysql.conf.d/mysqld.cnf
server-id = 2
relay-log = /var/log/mysql/mysql-relay-bin.log
log_slave_updates = 1
replicate-do-db = delayed_replication #pengaturan jika hanya 1 dari beberapa database yang ingin direplikasikan
  • Restart mysql agar setting slave bekerja, gunakan salah satu command berikut.
sudo systemctl restart mysql
#atau
sudo service mysql restart
  • masuk ke database slave untuk membuat database yang akan disinkronkan
mysql -u root -p
--buat database untuk import data
create database delayed_replication;
--keluar dari database cli
exit;
  • import database dari master ke slave
mysql -u root -p delayed_replication < delayed_replication.sql
  • masuk kembali ke mysql kemudian atur replikasi
mysql -u root -p
CHANGE MASTER TO
    MASTER_HOST='gantidenganipmaster',
    MASTER_USER='repl',
    MASTER_PASSWORD='123qweASD',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=83537,
    MASTER_DELAY=30;

--pengaturan jika hanya 1 database yang ingin di baca
CHANGE REPLICATION FILTER REPLICATE_DO_DB = (delayed_replication);

START SLAVE;

“83537” adalah posisi terakhir yang dikunci dari server master.
“mysql-bin.000001” juga disamakan dengan file settingan server master
“master delay” mengatur berapa lama jeda replikasi dalam detik

--keluar dari database cli
exit
  • Jika sudah berhasil, kembali ke server master untuk membuka kunci database
UNLOCK TABLES;

testing:

  • Masuk ke cli mysql di server master, lakukan update 1 data sales.
mysql -u root -p
use delayed_replication;
select * from sales where id = 1;
update sales set amount = 101 where id = 1;
select * from sales where id = 1;
  • Masuk ke cli mysql di server slave, cek perubahan. Data sales akan berubah dalam 30 detik sesuai waktu delayed yang ditentukan sebelumnya.
mysql -u root -p
use delayed_replication;
select * from sales where id = 1;

Selamat anda sudah bisa melakukan replikasi menggunakan delay untuk disaster recovery 😄

Catatan Penulis:

  • Pada pengaturan mysql pastikan server slave dapat mengakses server master. Jika belum bisa pastikan bind address “server master” sudah mengizinkan akses dari server luar.
[mysqld]
bind-address    = 0.0.0.0 #untuk menerima akses dari semua server
bind-address    = 127.0.0.1 #hanya menerima akses dari server internal
bind-address    = 133.208.101.50, 133.208.101.51 #hanya menerima akses dari beberapa server tertentu
  • Jika aktivitas perubahan data di database dirasa cukup besar, sediakan storage tambahan seperti Volumes Block Storage (Digital Ocean) atau Elastick Block Storage (AWS). Pindahkan direktori data mysql juga file bin ke dalam additional volume tersebut.
  • Jika terjadi error saat konfigurasi karena ada langkah yang salah atau terlewatkan, contohnya error berikut

ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository

#masuk ke mysql cli
mysql -u root -p

--stop slave
stop slave;

--keluar dari mysql cli
exit

#hapus dahulu semua file relay
rm /var/log/mysql/mysql-relay-bin*

#masuk kembali ke mysql cli
mysql -u root -p

--reset pengaturan slave
RESET SLAVE ALL;

--atur kembali konfigurasi replikasi seperti "CHANGE MASTER TO" atau "CHANGE REPLICATION FILTER"

--nyalakan kembali slave
start slave;

--keluar dari mysql cli
exit
  • Jika data mysql-bin atau relay-bin diletakkan selain di folder /var/lib/mysql atau /var/log/mysql, jangan lupa mengubah akses user direktori agar dapat diakses oleh mysql. Jangan lupa juga untuk menambahkan direktori di konfigurasi apparmor (/etc/apparmor.d/usr.sbin.mysqld)
chown -R mysql:mysql /direktori/baru
nano /etc/apparmor.d/usr.sbin.mysqld

#tambahkan izin direktori
/direktori/baru/nama.log rw
/direktori/baru/nama.err rw
/direktori/baru/ r
/direktori/baru/** rw
  • Untuk lebih amannya, user mysql yang dibuat dispesifikkan menggunakan ip server slave.
-- ganti wilcard '%'
CREATE USER 'repl'@'%' IDENTIFIED BY '123qweASD';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

-- menjadi ip server slave
CREATE USER 'repl'@'133.208.101.50' IDENTIFIED BY '123qweASD'; 
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'133.208.101.50';
  • --skip-triggers ditambahkan jika database memiliki banyak trigger yang biasanya menyebabkan error ketika import.
  • Bagi pemula untuk keluar dari nano menggunakan ctrl+X, kemudian Y jika ada perubahan ingin disimpan 😅
  • jika start slave berhasil namun tidak ada pesan error apapun tetapi replikasi tetap tidak jalan coba cek status slave
SHOW SLAVE STATUS;

silahkan selesaikan masalah sesuai pesan error yang ditampilkan,

Contoh 1, salah nama file atau bisa jadi space hardisk master tidak mencukupi
Contoh 2: konfigurasi max_allowed_packet di /etc/mysql/mysql.conf.d/mysqld.cnf tidak ada atau terlalu kecil, bisa tambahkan atau dinaikkan, contoh : max_allowed_packet = 256M

credits:
- https://dev.mysql.com/doc/refman/5.7/en/replication-configuration.html
- https://dev.mysql.com/doc/refman/5.7/en/replication-options-replica.html
- https://dev.mysql.com/doc/refman/5.7/en/change-replication-filter.html
- https://dev.mysql.com/doc/refman/5.7/en/change-master-to.html
- freepik

Database
MySQL

--

--

Ady Sanjaya
Ady Sanjaya

Written by Ady Sanjaya

3 Followers
2 Following

IT Operations in Yuscorp

No responses yet

Help

Status

About

Careers

Press

Blog

Privacy

Terms

Text to speech

Teams