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

Update: 3 February 2026

Baca: 6 menit

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

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 root@192.168.1.10:/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="admin@example.com"
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

Gunakan GTID: Memudahkan failover dan troubleshooting
Semua tabel pakai Primary Key: Required untuk ROW-based replication
Monitoring: Setup alerting untuk replication lag
Testing: Regular failover drills
Backup: Backup slave, bukan master
Read-only: Slave harus selalu read_only=1
Log-bin di slave: Untuk chain replication atau point-in-time recovery
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.