MySQL Replication untuk Developer - Setup Primary dan Replica Database dari Nol

Suatu hari aplikasi saya mulai lambat. Bukan lambat biasa - halaman yang tadinya 200ms jadi 4 detik. Saya cek CPU, normal. RAM, normal. Query lambat? Semua sudah pakai index. Setelah bongkar log, ternyata masalahnya bukan di kode aplikasi sama sekali. Masalahnya ada di replikasi database: server replica ketinggalan 40 menit dari primary, dan semua traffic baca diarahkan ke sana.

Kalau kamu pernah pegang aplikasi yang traffic-nya sudah naik, pasti cepat atau lambat ketemu situasi ini. Satu server MySQL akhirnya nggak cukup. Bukan cuma soal kapasitas CPU, tapi juga soal: bagaimana kalau server mati? Bagaimana kalau kamu mau migrasi schema tanpa bikin aplikasi down?

Di artikel ini saya akan cerita pengalaman setup MySQL replication dari nol di dua VPS Ubuntu. Bukan teori dari dokumentasi resmi, tapi versi yang benar-benar saya jalankan, termasuk semua error yang saya temui di tengah jalan.

Kenapa Replikasi Database Itu Penting

Sebelum masuk teknis, mari kita samakan dulu soal "kenapa". Ada tiga alasan besar:

• Read scaling - Aplikasi biasanya baca jauh lebih sering dari tulis (perbandingan kasarnya 80:20). Kalau kamu punya 2-3 replica, query SELECT bisa dibagi ke beberapa server.
• High availability - Kalau primary mati, kamu punya data terbaru di replica. Failover bisa dalam hitungan detik, bukan jam restore backup.
• Backup tanpa ganggu production - Backup dari replica artinya kamu nggak perlu bikin lock di server utama. Ini saja sudah alasan yang cukup.

Yang perlu dicatat: replikasi bukan pengganti backup. Kalau kamu tidak sengaja menjalankan DELETE FROM users tanpa WHERE, perintah itu akan ikut tereplikasi ke semua replica dalam hitungan milidetik. Replikasi menyebarkan kesalahan secepat menyebarkan data benar.

Dua Jenis Replikasi: Binlog Format

MySQL punya tiga format binary log. Pilihan ini menentukan apa yang dikirim dari primary ke replica.

Saya selalu pakai ROW. Alasannya simpel: satu-satunya bug replikasi serius yang pernah saya alami dulu terjadi karena pakai STATEMENT. Query dengan UPDATE ... SET counter = counter + 1 WHERE updated_at > NOW() - INTERVAL 1 HOUR menghasilkan jumlah baris berbeda di replica karena jam server beda 200ms.

Persiapan: Dua Server, Satu Konfigurasi

Setup yang saya pakai:

# Server Primary
IP: 10.0.0.10 hostname: db-primary (MySQL 8.0, Ubuntu 24.04)
# Server Replica
IP: 10.0.0.11 hostname: db-replica (MySQL 8.0, Ubuntu 24.04)

Langkah pertama, edit konfigurasi di kedua server. Buat file baru supaya rapi:

sudo nano /etc/mysql/mysql.conf.d/replication.cnf

Isi untuk server primary:

[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin
binlog_format = ROW
binlog_row_image = FULL
binlog_expire_logs_seconds = 604800
max_binlog_size = 256M
gtid_mode = ON
enforce_gtid_consistency = ON

Isi untuk server replica:

[mysqld]
server-id = 2
relay_log = /var/log/mysql/mysql-relay-bin
log_bin = /var/log/mysql/mysql-bin
read_only = ON
super_read_only = ON
gtid_mode = ON
enforce_gtid_consistency = ON
log_replica_updates = ON

Perhatikan server-id - ini harus unik di seluruh cluster. Kalau dua server punya ID sama, replica bakal menolak koneksi dengan error "The replica server is running with the same server id as the source". Ini error paling sering saya lihat waktu pertama kali setup.

Soal GTID (gtid_mode = ON): ini fitur MySQL 5.6+ yang bikin hidup jauh lebih gampang. Tanpa GTID, kamu harus hitung manual posisi file binlog dan koordinatnya. Dengan GTID, replica otomatis tahu "saya sudah sampai transaksi ke berapa" - nggak ada lagi drama koordinat salah.

Saya juga menyalakan super_read_only di replica, bukan cuma read_only. Bedanya: read_only masih bisa ditembus user dengan privilege SUPER. super_read_only menutup celah itu juga. Ini penting supaya nggak ada yang iseng menulis ke replica dan bikin data divergen.

sudo systemctl restart mysql
sudo mysql -e "SELECT @@server_id, @@gtid_mode, @@binlog_format"

Pastikan output menampilkan 1, ON, dan ROW untuk primary, lalu 2 untuk replica.

Buat User Replikasi

Di server primary, buat user khusus untuk replikasi. Jangan pakai root:

CREATE USER 'repl'@'10.0.0.11' IDENTIFIED BY 'PasswordReplikasiKuat123!';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.0.0.11';
FLUSH PRIVILEGES;

Batasi host ke IP replica saja. Kalau kamu pakai 'repl'@'%', artinya siapa pun yang tahu password itu bisa tarik semua data kamu. Saya pernah nemu server yang dibuka begini di internet terbuka - bukan pengalaman yang menyenangkan.

Ambil Snapshot Data dari Primary

Sekarang bagian yang paling sering bikin panik: menyalin data awal. Replica harus mulai dari kondisi yang sama persis dengan primary di satu titik waktu tertentu. Ada dua cara aman.

Cara 1 - mysqldump (paling umum):

# Di primary, dump dengan opsi konsisten
mysqldump -u root -p \
 --all-databases \
 --single-transaction \
 --source-data=2 \
 --set-gtid-purged=ON \
 --triggers --routines --events \
 > /tmp/full_backup.sql

Penjelasan opsi penting:

• --single-transaction - membuat snapshot konsisten tanpa mengunci tabel. Ini yang bikin dump InnoDB aman di server yang sedang ramai.
• --source-data=2 - menulis posisi binlog sebagai komentar di dalam dump (format baru MySQL 8; di versi lama namanya --master-data=2).
• --set-gtid-purged=ON - menulis GTID position ke dalam dump, jadi replica tahu titik mulai.

Cara 2 - mydumper (jauh lebih cepat untuk database besar):

sudo apt install mydumper -y
mydumper -u root -p -o /tmp/dumpdir -t 8 -F 64 -B kinara_project
# Salin ke replica
rsync -avz /tmp/dumpdir/ [email protected]:/tmp/dumpdir/

Untuk database di atas 50 GB, mydumper bisa 5-10x lebih cepat karena paralel. Saya sendiri pakai mysqldump untuk database sampai ~20 GB, dan mydumper di atas itu.

Restore ke Replica dan Sambungkan

Salin file dump, lalu restore di replica:

# Di replica
mysql -u root -p < /tmp/full_backup.sql

# Kalau pakai mydumper
myloader -u root -p -d /tmp/dumpdir -o -t 8

Setelah restore, sambungkan replica ke primary. Dengan GTID, cukup satu perintah:

CHANGE REPLICATION SOURCE TO
 SOURCE_HOST = '10.0.0.10',
 SOURCE_PORT = 3306,
 SOURCE_USER = 'repl',
 SOURCE_PASSWORD = 'PasswordReplikasiKuat123!',
 SOURCE_AUTO_POSITION = 1;

START REPLICA;

Verifikasi statusnya:

SHOW REPLICA STATUS\G

Yang wajib kamu cek di output:

• Replica_IO_Running: Yes - replica bisa terhubung ke primary
• Replica_SQL_Running: Yes - thread SQL tidak error
• Seconds_Behind_Source: 0 - replica tertinggal 0 detik
• Last_Error: kosong

Kalau salah satu thread tidak "Yes", cek Last_IO_Error atau Last_SQL_Error. Dari pengalaman saya, 90% masalah awal ada di tiga hal: firewall, salah password, atau server_id kembar.

Masalah Lag: Replica Ketinggalan, Ini yang Bikin Pusing

Ini masalah yang saya ceritakan di awal. Replikasi jalan lancar, semua status "Yes", tapi Seconds_Behind_Source terus naik sampai ribuan detik. Penyebabnya biasanya:

1. Replica single-threaded sementara primary multithreaded.

Primary bisa commit 500 transaksi per detik pakai banyak core. Replica lama (sebelum MySQL 5.7) apply dengan satu thread. Solusinya:

STOP REPLICA SQL_THREAD;
SET GLOBAL replica_parallel_workers = 8;
SET GLOBAL replica_parallel_type = 'LOGICAL_CLOCK';
START REPLICA SQL_THREAD;

Pada MySQL 8.0, replica_parallel_type hanya punya nilai LOGICAL_CLOCK, jadi aman. Kalau kamu masih pakai MySQL 5.7, DATABASE juga ada - tapi LOGICAL_CLOCK jauh lebih baik karena bisa paralel walau satu database.

Supaya setting ini permanen, tulis di replication.cnf:

replica_parallel_workers = 8
replica_parallel_type = LOGICAL_CLOCK
replica_preserve_commit_order = ON

replica_preserve_commit_order penting untuk menjaga urutan commit tetap sama dengan primary - kalau tidak, ada risiko data tidak konsisten pada saat failover.

2. Query berat tanpa index di replica.

Thread SQL replica meng-apply perubahan dalam satu transaksi. Kalau di dalam transaksi ada UPDATE yang nyerempet 200 ribu baris tanpa index, replika berhenti lama di situ. Ini pengalaman saya: ada satu UPDATE untuk backfill kolom yang butuh 12 menit. Di primary jalan sekali, tapi di replica dia jadi bottleneck.

Cara mendeteksi:

-- Lihat transaksi yang sedang di-apply
SHOW PROCESSLIST;

-- Cek query apa yang sedang jalan di replica
SELECT * FROM performance_schema.events_statements_current
WHERE SQL_TEXT IS NOT NULL\G

3. Replica dipakai untuk baca berat (reporting).

Ini kesalahan arsitektur. Kalau kamu mengarahkan query analitik berat ke replica yang sama dengan replica untuk failover, lag-nya pasti naik. Solusi: pisahkan - satu replica untuk reporting (boleh lag), satu replica kecil yang selalu bersih untuk failover.

Monitoring Replikasi: Jangan Andalkan Mata

Jangan pernah bergantung pada "sepertinya jalan". Saya pakai query monitoring ini dan jadikan alert:

SELECT
 CHANNEL_NAME,
 SERVICE_STATE,
 COUNT_TRANSACTIONS_RETRIES,
 COUNT_TRANSACTIONS_BEHIND
FROM performance_schema.replication_applier_status_by_worker;

Yang lebih penting lagi, cek waktu lag dari sumber data, bukan dari metrik internal:

-- Di primary: catat timestamp
SELECT NOW();

-- Di replica: cek apakah data terbaru sudah masuk
SELECT MAX(created_at) FROM kinara_project.blog_posting;

Kalau bedanya lebih dari 60 detik saat traffic normal, ada yang salah.

Kalau kamu sudah pakai Prometheus (saya pernah tulis soal Grafana dan Prometheus di posting lain), pasang mysqld_exporter dan alert di metrik mysql_slave_status_seconds_behind_master:

- alert: ReplicationLagHigh
 expr: mysql_slave_status_seconds_behind_master > 300
 for: 5m
 annotations:
 summary: "Replica {{ $labels.instance }} lag {{ $value }}s"

Batas 300 detik itu pilihan saya sendiri. Untuk aplikasi e-commerce bisa lebih ketat (30 detik), untuk blog seperti ini 300 detik masih wajar.

Routing Baca-Tulis di Aplikasi

Replikasi sudah jalan, tapi aplikasi belum tahu harus ke mana. Pola paling sederhana:

<?php
class Database
{
 private static $primary;
 private static $replica;

 public static function write(): PDO
 {
 if (!self::$primary) {
 self::$primary = new PDO(
 'mysql:host=10.0.0.10;dbname=kinara_project',
 'appuser', getenv('DB_PASS'),
 [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
 );
 }
 return self::$primary;
 }

 public static function read(): PDO
 {
 if (!self::$replica) {
 self::$replica = new PDO(
 'mysql:host=10.0.0.11;dbname=kinara_project',
 'appuser', getenv('DB_PASS'),
 [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
 );
 }
 return self::$replica;
 }
}

// INSERT / UPDATE / DELETE
Database::write()->exec("INSERT INTO log ...");

// SELECT
$rows = Database::read()->query("SELECT * FROM blog_posting")->fetchAll();

Jebakan yang sering terjadi: user baru saja menulis data, lalu refresh halaman dan datanya belum muncul. Itu bukan bug aplikasi - itu read-after-write latency. Solusinya: setelah operasi tulis, paksa baca ke primary selama beberapa detik.

class Database
{
 private static $forcePrimaryUntil = 0;

 public static function markWritten(): void
 {
 self::$forcePrimaryUntil = time() + 3;
 }

 public static function read(): PDO
 {
 if (time() < self::$forcePrimaryUntil) {
 return self::write();
 }
 // ... koneksi replica
 }
}

// Pemakaian:
Database::write()->exec("UPDATE users SET ...");
Database::markWritten();

Ini pendekatan yang saya pakai di aplikasi CI4 blog ini. Sederhana, nggak perlu proxy seperti ProxySQL. Kalau skalanya sudah lebih besar, ProxySQL atau MaxScale worth it.

Failover Manual vs Otomatis

Saya sengaja tidak memakai auto-failover. Alasannya: split-brain adalah mimpi buruk yang jauh lebih buruk daripada downtime 5 menit.

Prosedur failover manual yang saya pakai:

# 1. Pastikan replica sudah mengejar semua transaksi
mysql -e "STOP REPLICA IO_THREAD; SHOW REPLICA STATUS\G" | grep -E "Auto_Position|Retrieved_Gtid"
mysql -e "SELECT WAIT_FOR_EXECUTED_GTID_SET('<gtid_set>', 30)"

# 2. Promosikan replica jadi primary
mysql -e "STOP REPLICA; RESET REPLICA ALL; SET GLOBAL read_only=OFF; SET GLOBAL super_read_only=OFF"

# 3. Arahkan aplikasi ke server baru (update config / DNS / load balancer)

# 4. Cek lagi
mysql -e "SELECT @@read_only, @@super_read_only"

Kalau kamu mau otomatis, orchestrator atau mysqlfailover bisa dipakai. Tapi tetap ada risiko: kalau heartbeat putus karena network blip, orchestrator bisa salah promosikan replica dan kamu berakhir dengan dua primary. Untuk tim kecil, prosedur manual yang terdokumentasi jauh lebih aman.

Masalah yang Sering Muncul (dan Solusinya)

Berikut daftar error yang benar-benar saya temui saat setup dan operasional:

• Error 1236: Could not find first log file name in binary log index file - replica minta binlog yang sudah dipurge primary. Fix: reset replica (RESET REPLICA ALL) lalu ambil snapshot baru. Penyebabnya biasanya binlog_expire_logs_seconds terlalu pendek.
• Error 1062: Duplicate entry saat apply - biasanya karena ada data masuk ke replica secara manual. Fix: stop di titik itu, perbaiki data, skip transaksi dengan SET GLOBAL sql_replica_skip_counter = 1 (tanpa GTID) atau SET GTID_NEXT (dengan GTID). Hati-hati, skipping berarti data divergen.
• Disk penuh karena binlog - saya pernah kena, binlog tumbuh sampai 14 GB. Fix: PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 3 DAY); dan set binlog_expire_logs_seconds.
• Replica tidak mau start setelah reboot - cek datadir permission dan apakah /var/log/mysql ada. Sering kali folder log hilang setelah upgrade paket.

Kapan Replikasi Bukan Jawaban yang Tepat

Jujur, MySQL replication punya batas. Kalau kamu menghadapi salah satu dari ini, pertimbangkan arsitektur lain:

• Write throughput sangat tinggi - replikasi tidak membantu karena semua tulis tetap ke satu primary. Pertimbangkan sharding (vitess) atau database terdistribusi.
• Butuh konsistensi transaksional lintas region - replikasi asynchronous MySQL tidak menjamin itu. CockroachDB atau Spanner-style DB lebih cocok.
• Analitik berat - database kolumnar seperti ClickHouse jauh lebih efisien daripada replica MySQL untuk OLAP.

Untuk blog ini, aplikasi CI4, dan sebagian besar proyek freelance saya, satu primary + dua replica sudah lebih dari cukup. Overengineering dengan sistem terdistribusi kompleks justru bikin biaya operasional naik tanpa manfaat nyata.

Kesimpulan

Setup replikasi MySQL itu sebenarnya tidak sulit - mungkin 30 menit kalau semua berjalan lancar. Yang sulit adalah operasionalnya: memantau lag, menangani error, dan memastikan aplikasi tidak salah routing.

Yang saya sarankan untuk kamu yang baru mulai:

1. Selalu pakai binlog_format = ROW dan gtid_mode = ON. Jangan coba-coba STATEMENT.
2. Salin data awal dengan mysqldump --single-transaction atau mydumper, jangan lewat rsync datadir mentah.
3. Set super_read_only = ON di replica supaya tidak ada penulisan liar.
4. Pasang monitoring lag dari hari pertama - jangan tunggu ada masalah.
5. Dokumentasikan prosedur failover manual di README, dan latih sekali sebelum benar-benar dibutuhkan.

Kamu sendiri sudah pakai replikasi MySQL di production? Apa masalah terbesar yang pernah kamu hadapi - lag, error 1236, atau justru read-after-write yang bikin user komplain? Tulis di kolom komentar, saya penasaran apa yang paling sering terjadi di lapangan.