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
- 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.
