Lewati ke konten
Kembali ke Blog
High Availability MariaDB Replication

Konfigurasi Replication Master-Slave di MariaDB: Panduan Lengkap High Availability

Hendra Wijaya
· · 8 menit baca

Replication adalah fondasi dari high availability database. Setelah mengimplementasikan ratusan setup replication untuk client, saya akan berbagi konfigurasi yang paling reliable untuk MariaDB master-slave replication.

Mengapa Master-Slave Replication?

Benefits:
– High Availability: Failover otomatis jika master down
– Read Scaling: Distribusi read query ke slave
– Backup: Backup tanpa impact ke production
– Disaster Recovery: Slave di lokasi berbeda

Persiapan Server

1. Server Requirements

Master Server:
– CPU: 4+ cores
– RAM: 8GB+
– Disk: SSD dengan IOPS tinggi
– Network: Latency rendah ke slave

Slave Server:
– CPU: 4+ cores
– RAM: 8GB+
– Disk: SSD (bisa lebih kecil dari master)
– Network: Koneksi stable ke master

2. Install MariaDB di Kedua Server

Master (192.168.1.10):

# Install MariaDB
sudo apt update
sudo apt install mariadb-server mariadb-client -y

Start service


sudo systemctl start mariadb
sudo systemctl enable mariadb

Slave (192.168.1.11):

# Install MariaDB (sama dengan master)
sudo apt update
sudo apt install mariadb-server mariadb-client -y
sudo systemctl start mariadb
sudo systemctl enable mariadb

Konfigurasi Master Server

1. Edit my.cnf Master

sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

[mysqld]
# Basic Settings
server-id = 1
bind-address = 0.0.0.0

Binary Log


log_bin = /var/log/mysql/mariadb-bin
binlog_format = ROW
binlog_row_image = FULL
expire_logs_days = 7
max_binlog_size = 100M


GTID (Global Transaction ID)


gtid_domain_id = 1
gtid_strict_mode = 1
log_slave_updates = 1


Replication Settings


slave_parallel_mode = optimistic
slave_parallel_threads = 4
slave_parallel_max_queued = 16M


InnoDB untuk Replication


innodb_flush_log_at_trx_commit = 1
sync_binlog = 1


Monitoring


binlog_rows_query_log_events = 1
log_bin_trust_function_creators = 1


Binary log do_db (opsional - semua database)


binlog_do_db = app_database


atau skip specific database


binlog_ignore_db = test

2. Buat User Replication

mysql -u root -p

-- Buat user khusus untuk replication
CREATE USER 'replica'@'192.168.1.11' IDENTIFIED BY 'ReplicaPass123!';
GRANT REPLICATION SLAVE ON *.* TO 'replica'@'192.168.1.11';
GRANT BINLOG MONITOR ON *.* TO 'replica'@'192.168.1.11';
FLUSH PRIVILEGES;

-- Verifikasi user
SELECT User, Host FROM mysql.user WHERE User='replica';

3. Restart MariaDB

sudo systemctl restart mariadb

4. Ambil Initial Backup

# Lock tables dan export
cd /backup

mysql -u root -p -e "FLUSH TABLES WITH READ LOCK; SET GLOBAL read_only = ON;"


Backup dengan posisi binlog


mysqldump -u root -p --all-databases \
--single-transaction \
--master-data=2 \
--routines \
--triggers \
> master_backup.sql


Cek posisi binlog


mysql -u root -p -e "SHOW MASTER STATUS;"


Unlock tables


mysql -u root -p -e "UNLOCK TABLES; SET GLOBAL read_only = OFF;"

Catat output SHOW MASTER STATUS:

File: mariadb-bin.000001
Position: 1234
Binlog_Do_DB: app_database

Konfigurasi Slave Server

1. Edit my.cnf Slave

sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

[mysqld]
# Basic Settings
server-id = 2
bind-address = 0.0.0.0

Binary Log (untuk chain replication atau backup)


log_bin = /var/log/mysql/mariadb-bin
binlog_format = ROW
expire_logs_days = 7


GTID


gtid_domain_id = 2
gtid_strict_mode = 1
log_slave_updates = 1


Replication Settings (read-only)


read_only = 1
super_read_only = 1
skip_slave_start = 1


Slave Performance


slave_parallel_mode = optimistic
slave_parallel_threads = 4
slave_parallel_max_queued = 16M
slave_net_timeout = 60
slave_compressed_protocol = 1


Relay Log Settings


relay_log = /var/log/mysql/relay-bin
relay_log_index = /var/log/mysql/relay-bin.index
relay_log_space_limit = 2G
max_relay_log_size = 100M


Crash-safe replication


relay_log_recovery = 1
relay_log_info_repository = TABLE
master_info_repository = TABLE


Reporting


report_host = slave1
report_port = 3306

2. Transfer Backup dari Master

# Di slave, copy backup dari master
scp [email protected]:/backup/master_backup.sql /tmp/

3. Import Backup

# Import ke slave
mysql -u root -p < /tmp/master_backup.sql

4. Konfigurasi Slave untuk Replication

mysql -u root -p

-- Setup replication dengan GTID (Recommended)
STOP SLAVE;
RESET SLAVE ALL;

CHANGE MASTER TO
MASTER_HOST = '192.168.1.10',
MASTER_PORT = 3306,
MASTER_USER = 'replica',
MASTER_PASSWORD = 'ReplicaPass123!',
MASTER_USE_GTID = slave_pos,
MASTER_SSL = 0;


-- Atau dengan posisi binlog (legacy)
/

CHANGE MASTER TO
MASTER_HOST = '192.168.1.10',
MASTER_PORT = 3306,
MASTER_USER = 'replica',
MASTER_PASSWORD = 'ReplicaPass123!',
MASTER_LOG_FILE = 'mariadb-bin.000001',
MASTER_LOG_POS = 1234;
/


-- Start replication
START SLAVE;


-- Cek status
SHOW SLAVE STATUS\G

5. Verifikasi Replication

-- Pastikan tidak ada error
SHOW SLAVE STATUS\G

-- Output yang diharapkan:
-- Slave_IO_Running: Yes
-- Slave_SQL_Running: Yes
-- Seconds_Behind_Master: 0 (atau angka kecil)
-- Last_IO_Error: (kosong)
-- Last_SQL_Error: (kosong)


-- Cek GTID posisi
SELECT @@gtid_slave_pos;


-- Bandingkan dengan master
-- (jalankan di master: SELECT @@gtid_binlog_pos;)

Testing Replication

1. Test Data Replication

Di Master:

USE app_database;

CREATE TABLE test_replication (
id INT AUTO_INCREMENT PRIMARY KEY,
data VARCHAR(100),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);


INSERT INTO test_replication (data) VALUES ('Test from Master');

Di Slave:

USE app_database;

-- Cek apakah data sudah sampai
SELECT * FROM test_replication;


-- Output harus menunjukkan row yang sama

2. Test Lag Monitoring

-- Di slave, cek lag
SHOW SLAVE STATUS\G
-- Perhatikan: Seconds_Behind_Master

-- Jika lag besar, cek proseslist
SHOW FULL PROCESSLIST;

3. Test Failover (Manual)

Simulasikan master down:

# Di master, stop MariaDB
sudo systemctl stop mariadb

Promote slave menjadi master:

-- Di slave
STOP SLAVE;
RESET SLAVE ALL;

-- Jadikan master
SET GLOBAL read_only = 0;
SET GLOBAL super_read_only = 0;


-- Update aplikasi untuk pakai slave sebagai master

Monitoring Replication

1. Query Monitoring

-- Cek replication lag
SHOW SLAVE STATUS\G

-- Detail thread
SELECT * FROM information_schema.PROCESSLIST 
WHERE COMMAND = 'Binlog Dump' OR COMMAND LIKE '%Slave%';


-- Replication statistics
SELECT 
VARIABLE_NAME,
VARIABLE_VALUE
FROM information_schema.GLOBAL_STATUS
WHERE VARIABLE_NAME LIKE '%slave%';

2. Setup Monitoring Script

sudo nano /usr/local/bin/check_replication.sh

#!/bin/bash

ALERT_EMAIL="[email protected]"
MAX_LAG=60  # detik


Cek replication status


STATUS=$(mysql -u root -e "SHOW SLAVE STATUS\G" 2>/dev/null)


IO_RUNNING=$(echo "$STATUS" | grep "Slave_IO_Running" | awk '{print $2}')
SQL_RUNNING=$(echo "$STATUS" | grep "Slave_SQL_Running" | awk '{print $2}')
LAG=$(echo "$STATUS" | grep "Seconds_Behind_Master" | awk '{print $2}')


if [ "$IO_RUNNING" != "Yes" ] || [ "$SQL_RUNNING" != "Yes" ]; then
echo "Replication STOPPED! IO: $IO_RUNNING, SQL: $SQL_RUNNING" | \
mail -s "[CRITICAL] MariaDB Replication Down" $ALERT_EMAIL
exit 1
fi


if [ "$LAG" -gt "$MAX_LAG" ]; then
echo "Replication lag: $LAG seconds" | \
mail -s "[WARNING] MariaDB Replication Lag High" $ALERT_EMAIL
fi


echo "Replication OK - Lag: ${LAG}s"

3. Setup Cron Monitoring

sudo crontab -e

*/5 * * * * /usr/local/bin/check_replication.sh >> /var/log/replication_check.log 2>&1

Troubleshooting Replication

1. Slave_IO_Running: No

Cek network:

telnet 192.168.1.10 3306

Cek user:

-- Di master
SELECT User, Host FROM mysql.user WHERE User='replica';

Reset dan restart:

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

2. Slave_SQL_Running: No

Cek error:

SHOW SLAVE STATUS\G
-- Perhatikan: Last_SQL_Error

Skip error (hati-hati!):

STOP SLAVE;
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE;

Atau dengan GTID:

STOP SLAVE;
SET gtid_slave_pos = '<master_gtid_posisi>';
START SLAVE;

3. High Replication Lag

Analisis:

-- Cek query yang lambat
SELECT * FROM mysql.slow_log 
ORDER BY start_time DESC LIMIT 10;

-- Cek resource
SHOW ENGINE INNODB STATUS\G

Optimasi:

# Tingkatkan parallel threads
slave_parallel_threads = 8
slave_parallel_max_queued = 32M

4. Data Inconsistency

Periksa dengan pt-table-checksum (Percona Toolkit):

pt-table-checksum --user=root --password h=192.168.1.10,P=3306 \
    --databases=app_database --replicate=percona.checksums

Best Practices

  1. Gunakan GTID: Memudahkan failover dan troubleshooting
  2. Semua tabel pakai Primary Key: Required untuk ROW-based replication
  3. Monitoring: Setup alerting untuk replication lag
  4. Testing: Regular failover drills
  5. Backup: Backup slave, bukan master
  6. Read-only: Slave harus selalu read_only=1
  7. Log-bin di slave: Untuk chain replication atau point-in-time recovery
  8. Relay log: Monitor space usage

Advanced: Multi-Source Replication

Setup slave dengan multiple masters:

-- Connection 1 (Master 1)
CHANGE MASTER 'master1' TO
    MASTER_HOST = '192.168.1.10',
    MASTER_USER = 'replica1',
    MASTER_PASSWORD = 'Pass1',
    MASTER_USE_GTID = slave_pos;

-- Connection 2 (Master 2)
CHANGE MASTER 'master2' TO
MASTER_HOST = '192.168.1.20',
MASTER_USER = 'replica2',
MASTER_PASSWORD = 'Pass2',
MASTER_USE_GTID = slave_pos;


-- Start semua
START ALL SLAVES;


-- Cek status
SHOW ALL SLAVES STATUS\G

Kesimpulan

Master-Slave replication di MariaDB dengan GTID adalah fondasi yang solid untuk high availability. Dengan monitoring dan maintenance yang tepat, Anda akan memiliki sistem database yang reliable dan scalable.

Ditulis oleh

Hendra Wijaya
Artikel Sebelumnya Info GTK Data Diklat dan Pelatihan: Kursus Guru Artikel Selanjutnya Kumpulan Ucapan Selamat Ramadhan Bahasa Indonesia yang Menyentuh Hati

Artikel Terkait

MariaDB Spider Storage Engine: Sharding untuk Scalable Database

Februari 3, 2026

MariaDB ColumnStore: Analytics Storage Engine untuk Big Data

Februari 3, 2026

MariaDB Aria Storage Engine: Panduan Lengkap untuk MyISAM Replacement

Februari 3, 2026

Tinggalkan Komentar

Email tidak akan ditampilkan.