Salin dan Bagikan

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.

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

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

Mengapa Master-Slave Replication?

Persiapan Server

1. Server Requirements

2. Install MariaDB di Kedua Server

Konfigurasi Master Server

1. Edit my.cnf Master

2. Buat User Replication

3. Restart MariaDB

4. Ambil Initial Backup

Konfigurasi Slave Server

1. Edit my.cnf Slave

2. Transfer Backup dari Master

3. Import Backup

4. Konfigurasi Slave untuk Replication

5. Verifikasi Replication

Testing Replication

1. Test Data Replication

2. Test Lag Monitoring

3. Test Failover (Manual)

Monitoring Replication

1. Query Monitoring

2. Setup Monitoring Script

3. Setup Cron Monitoring

Troubleshooting Replication

1. Slave_IO_Running: No

2. Slave_SQL_Running: No

3. High Replication Lag

4. Data Inconsistency

Best Practices

Advanced: Multi-Source Replication

Kesimpulan

Artikel Terkait