Membangun High Availability MySQL Cluster dengan Orchestrator

1. Konteks Industri & Analisis Akar Masalah

Dalam lanskap arsitektur web modern, ketersediaan tinggi (High Availability atau HA) pada lapisan basis data adalah fondasi mutlak yang membedakan antara sistem kelas enterprise dan aplikasi hobi. Kegagalan basis data (database downtime) bukan sekadar gangguan teknis; ia adalah bencana finansial, kerusakan reputasi, dan keputusasaan bagi pengguna akhir. Ketika traffic melonjak hingga puluhan ribu requests per second (RPS), infrastruktur backend web seperti Node.js, Go, atau PHP-FPM dengan Nginx load balancing akan runtuh seketika jika basis data MySQL di belakangnya mengalami single point of failure (SPOF).

Secara historis, administrator sistem mengandalkan replikasi MySQL Master-Slave tradisional dengan intervensi manual atau skrip bash buatan sendiri (homegrown scripts) untuk mendeteksi kegagalan. Pendekatan ini terbukti rapuh di dunia nyata. Skrip pemantauan sering kali mengalami false positive, menyebabkan fenomena split-brain di mana dua node bertindak sebagai master secara bersamaan, mengakibatkan korupsi data yang masif (silent data corruption). Keterlambatan replikasi (replication lag), masalah transisi GTID (Global Transaction Identifier), dan lambatnya waktu deteksi (Mean Time to Detect - MTTD) membuat waktu pemulihan (Mean Time to Recovery - MTTR) melonjak dari hitungan detik ke hitungan jam.

Sebelum penerapan Orchestrator, arsitektur umumnya bergantung pada skrip pemantauan mentah berbasis Cron yang memanggil mysqladmin ping setiap menit. Jika master down, skrip mempromosikan slave secara paksa tanpa memeriksa status GTID secara akurat, menyebabkan inkonsistensi data antar node, hilangnya transaksi (data loss), dan kegagalan aplikasi secara menyeluruh.

Setelah penerapan Orchestrator yang dikombinasikan dengan MySQL Group Replication atau Master-Slave berbasis GTID, sistem bertransformasi total. Orchestrator menyediakan deteksi kegagalan topologi secara real-time, orkestrasi promosi replica otomatis yang aman, pemulihan topologi pasca-kegagalan (failover healing), serta integrasi mulus dengan sistem penemuan layanan (service discovery) seperti Consul atau ProxySQL. MTTD ditekan hingga di bawah 5 detik, dan MTTR diringkas menjadi kurang dari 15 detik tanpa intervensi manusia.

[!IMPORTANT] Jangan pernah mengandalkan skrip failover buatan sendiri tanpa verifikasi GTID yang ketat. Risiko split-brain dapat menghancurkan integritas basis data relasional dalam hitungan detik.

2. Konsep Inti & Deep Dive Arsitektur

Orchestrator adalah alat manajemen topologi replikasi MySQL yang sangat tangguh, dikembangkan oleh GitHub. Ia bekerja dengan memetakan topologi replikasi sebagai sebuah directed graph (graf berarah), di mana node adalah instans MySQL (Master atau Slave) dan sisi graf merepresentasikan hubungan replikasi binlog (SHOW SLAVE STATUS).

Di bawah tenda (under the hood), Orchestrator secara periodik melakukan polling ke seluruh instans MySQL dalam kluster untuk mengumpulkan metrik status, posisi binlog, posisi relay log, status GTID, dan variabel konfigurasi penting. Ketika sebuah master terdeteksi mati (berdasarkan timeout health check yang dikonfigurasi), Orchestrator mengevaluasi status kesehatan kluster, memilih candidate replica terbaik berdasarkan posisi binlog paling mutakhir, dan mengeksekusi rangkaian perintah pemulihan otomatis secara sekuensial.

Berikut adalah visualisasi arsitektur kluster MySQL berbasis Orchestrator dengan ProxySQL sebagai query routing layer:

+-------------------------------------------------------------------------+
|                         Application Layer                               |
|                  (Node.js / Go / PHP-FPM / Nginx)                       |
+-------------------------------------------------------------------------+
                                     |
                                     v
+-------------------------------------------------------------------------+
|                        ProxySQL (Load Balancer)                         |----+
|                  - Read/Write Splitting & Query Routing                 |    |
+-------------------------------------------------------------------------+    |
                     |                                  |                      |
      [Write Queries]|                                  |[Read Queries]        |
                     v                                  v                      |
+--------------------------+               +--------------------------+        |
|       MySQL Master       |--[Binlog]---->|       MySQL Slave 1      |        |
|    (Read/Write Node)     |               |      (Read-Only Node)    |        |
+--------------------------+               +--------------------------+        |
                     |                                  |                      |
                     |     +----------------------+     |                      |
                     +---->|     Orchestrator     |<----+
                           |  (Topology Discovery |      [Health Checks]
                           |   & Auto-Failover)   |
                           +----------------------+               
                                     |
                                     v
                           +----------------------+
                           |    Consul / Raft     |
                           |  (KV Store & Leader) |
                           +----------------------+

Alur eksekusi saat terjadi kegagalan master:

1. Deteksi: Orchestrator gagal melakukan ping atau query SELECT 1 ke MySQL Master selama durasi ServerPollInterval dan FailureDetectionPeriod.
2. Konsensus: Berkomunikasi dengan instance Orchestrator lain melalui backend Raft/Consul untuk memastikan bahwa master benar-benar mati dan bukan karena partisi jaringan lokal (split-brain prevention).
3. Pemilihan Kandidat: Memindai semua replica yang terhubung, membandingkan posisi GTID (Executed_Gtid_Set), dan memilih replica yang paling sedikit mengalami lag.
4. Promosi: Mengeksekusi STOP SLAVE; RESET SLAVE ALL; SET GLOBAL read_only = OFF; pada replica terpilih.
5. Perekatan Ulang (Re-routing): Mengarahkan slave lainnya untuk melakukan point-to master yang baru menggunakan CHANGE MASTER TO berbasis GTID.
6. Hook Eksekusi: Memanggil skrip eksternal (misalnya untuk memperbarui DNS atau mengonfigurasi ulang ProxySQL backend).

3. Perbandingan Komparatif / Analisis Pendekatan

Untuk memilih arsitektur HA yang tepat, kita harus membandingkan Orchestrator dengan solusi alternatif seperti MySQL Group Replication (MGR) dan Percona XtraDB Cluster (PXC/Galera).

[!TIP] Jika beban kerja aplikasi Anda didominasi oleh operasi pembacaan (read-heavy 80%+) seperti CMS, e-commerce, atau API publik, kombinasi Orchestrator dan MySQL Semi-Synchronous Replication adalah pilihan paling stabil, efisien, dan mudah di-debug.

4. Prasyarat Sistem & Persiapan Lingkungan

Sebelum melakukan instalasi perangkat lunak, pastikan seluruh infrastruktur memenuhi spesifikasi teknis berikut:

Spesifikasi Minimum Hardware

• OS: Ubuntu 22.04 LTS / Debian 12 Bookworm (Kernel Linux 5.15+)
• CPU: Minimal 4 vCore (Direkomendasikan 8 vCore untuk node Orchestrator dan Database)
• RAM: Minimal 8 GB untuk database server, 2 GB untuk Orchestrator
• Storage: SSD NVMe dengan performa IOPS tinggi (minimal 3000 IOPS write)

Prasyarat Jaringan & DNS

• Port TCP 3306 (MySQL), 3000 (Orchestrator Web/API), 10008 (Orchestrator Raft internal jika mode raft aktif).
• Sinkronisasi waktu menggunakan Chrony / NTP yang akurat di seluruh node untuk menghindari inkonsistensi log.

Persiapan Akun MySQL untuk Orchestrator

Masuk ke MySQL shell pada setiap node dan buat pengguna khusus dengan hak istimewa administratif yang diperlukan:

CREATE USER 'orchestrator'@'%' IDENTIFIED BY 'StrongOrchestratorPassword2026!';
GRANT SUPER, REPLICATION CLIENT, REPLICATION SLAVE, PROCESS, SELECT, RELOAD, DROP ON *.* TO 'orchestrator'@'%';
GRANT CREATE, INSERT, UPDATE, DELETE ON orchestrator.* TO 'orchestrator'@'%';
FLUSH PRIVILEGES;

5. Panduan Implementasi Langkah demi Langkah

Langkah 1: Konfigurasi MySQL Server (Master & Slaves)

Edit file /etc/mysql/mysql.conf.d/mysqld.cnf pada setiap instans MySQL untuk mengaktifkan GTID, binlog, dan identifikasi server yang unik.

[mysqld]
server-id                       = 101
port                            = 3306
bind-address                    = 0.0.0.0

# Logging & Replikasi
log_bin                         = /var/log/mysql/mysql-bin.log
binlog_format                   = ROW
log_slave_updates               = ON
enforce_gtid_consistency        = ON
gtid_mode                       = ON

# Optimasi Performa & Ketahanan
innodb_buffer_pool_size         = 4G
innodb_flush_log_at_trx_commit  = 1
sync_binlog                     = 1
innodb_file_per_table           = 1
max_connections                 = 1000

# Pengaturan Semi-Sync Replication
plugin_load_add                 = "semisync_master.so"
plugin_load_add                 = "semisync_slave.so"
rpl_semi_sync_master_enabled    = 1
rpl_semi_sync_slave_enabled     = 1
rpl_semi_sync_master_timeout    = 1000

Catatan: Ubah server-id menjadi 102, 103, dan seterusnya untuk node slave yang berbeda. Restart layanan MySQL:

sudo systemctl restart mysql

Langkah 2: Instalasi dan Konfigurasi Orchestrator

Unduh dan instal paket Orchestrator pada node manajemen khusus atau gabungkan dengan node ProxySQL.

# Unduh paket biner orchestrator (sesuaikan versi)
wget https://github.com/openark/orchestrator/releases/download/v3.2.6/orchestrator_3.2.6_amd64.deb
sudo dpkg -i orchestrator_3.2.6_amd64.deb

Buat file konfigurasi Orchestrator di /etc/orchestrator.conf.json dengan konfigurasi produksi yang lengkap:

{
  "ListenAddress": ":3000",
  "MySQLTopologyWHM_Username": "orchestrator",
  "MySQLTopologyWHM_Password": "StrongOrchestratorPassword2026!",
  "Backend": "sqlite3",
  "SQLite3DataFile": "/var/lib/orchestrator/orchestrator.db",
  "DefaultMasterSlaveWeight": 1,
  "RecoveryPeriodBlockSeconds": 3600,
  "DelayMasterPromotionIfSQLThreadNotUpToDate": true,
  "MasterAutoHealing": true,
  "FailoverProcesses": [
    "echo 'Failover started for {failureType} on {failureCluster}' >> /var/log/orchestrator/failover.log",
    "/usr/local/bin/orchestrator-failover-handler.sh {failureCluster} {failedHost} {successorHost}"
  ],
  "DetectMasterFailureBySQLThreads": false,
  "PeerDiscoveryInterval": 5,
  "DiscoveryPollSeconds": 5,
  "SkipPingReplicaSLAVE": false,
  "ReasonableReplicationLagSeconds": 10,
  "CandidateMirrorsLabel": "candidate",
  "AuditLog": "/var/log/orchestrator/orchestrator-audit.log",
  "KVClusterMasterPrefix": "/orchestrator/cluster/master",
  "ConsulAddress": "",
  "UseMutualTLS": false
}

Langkah 3: Skrip Penanganan Failover Otomatis

Buat skrip /usr/local/bin/orchestrator-failover-handler.sh untuk menangani pembaruan rute ProxySQL secara otomatis ketika failover terjadi.

#!/bin/bash
CLUSTER=$1
FAILED_HOST=$2
SUCCESSOR_HOST=$3

LOGFILE="/var/log/orchestrator/failover-actions.log"

echo "[$(date)] ALERT: Master $FAILED_HOST di kluster $CLUSTER down. Mempromosikan $SUCCESSOR_HOST" >> $LOGFILE

# Perbarui ProxySQL untuk mengalihkan Write Traffic ke Master Baru
mysql -u admin -padmin -h 127.0.0.1 -P 6032 <<EOF
UPDATE mysql_servers SET status='OFFLINE_SOFT' WHERE hostname='$FAILED_HOST';
UPDATE mysql_servers SET status='ONLINE' WHERE hostname='$SUCCESSOR_HOST';
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
EOF

echo "[$(date)] SUCCESS: ProxySQL berhasil dialihkan ke $SUCCESSOR_HOST" >> $LOGFILE

Berikan izin eksekusi pada skrip tersebut:

sudo chmod +x /usr/local/bin/orchestrator-failover-handler.sh
sudo systemctl enable orchestrator
sudo systemctl start orchestrator

6. Bedah Kode & Penjelasan Detail Baris Kunci

Mari kita bedah parameter paling krusial dalam file konfigurasi /etc/orchestrator.conf.json:

• "RecoveryPeriodBlockSeconds": 3600: Parameter ini menentukan waktu jeda (cooldown) setelah sebuah pemulihan sukses dilakukan pada kluster yang sama. Nilai 3600 detik (1 jam) mencegah Orchestrator melakukan pemulihan berulang kali secara membabi buta (flapping failover) jika terjadi ketidakstabilan jaringan yang sporadis.

• "DelayMasterPromotionIfSQLThreadNotUpToDate": true: Sangat penting untuk integritas data. Jika diatur ke true, Orchestrator akan menolak mempromosikan replica yang belum menyelesaikan eksekusi seluruh relay log yang diterimanya. Ini memastikan tidak ada transaksi yang tertinggal pada replica baru sebelum ia naik takhta menjadi master.

• "FailoverProcesses": Array perintah eksternal yang dieksekusi secara otomatis segera setelah kegagalan dikonfirmasi. Variabel substitusi seperti {failedHost} dan {successorHost} diisi secara dinamis oleh Orchestrator, memungkinkan integrasi instan dengan alat jaringan, DNS, atau load balancer.

7. Skenario Produksi & Studi Kasus Dunia Nyata

Pada sistem e-commerce berskala besar dengan event flash sale (contoh: 50.000 pesanan per menit), beban basis data terbagi menjadi 10% Write (transaksi checkout) dan 90% Read (katalog produk, keranjang belanja).

Implementasi High Concurrency

1. ProxySQL Layer: Menerima seluruh koneksi dari aplikasi Node.js. Query INSERT, UPDATE, DELETE dilempar ke Server ID 101 (Master). Query SELECT didistribusikan secara Round-Robin ke Server ID 102 dan 103 (Slaves).
2. Kondisi Beban Puncak: Node Master mendadak mengalami kernel panic akibat kehabisan memori (OOM Killer).
3. Respon Otomatis Orchestrator:
   • Dalam 5 detik (DiscoveryPollSeconds), Orchestrator mendeteksi Master tidak merespons.
   • Setelah 3 kali polling berturut-turut, Orchestrator mendeklarasikan kegagalan master.
   • Slave 1 (102) yang memiliki GTID paling sinkron dipilih sebagai kandidat.
   • Skrip failover mengeksekusi perpindahan ProxySQL secara instan. Aplikasi hanya mengalami brief error spike selama kurang dari 4 detik sebelum transaksi kembali normal.

8. Common Pitfalls, Anti-Patterns & Solusi

[!WARNING] Hindari kesalahan-kesalahan fatal berikut yang sering dilakukan oleh administrator pemula dalam mengelola kluster MySQL HA:

1. Mengabaikan sync_binlog = 1 dan innodb_flush_log_at_trx_commit = 1:

   • Anti-Pattern: Mengaturnya ke 0 untuk mengejar performa tulis mentah (raw speed).
   • Dampak: Jika terjadi pemadaman listrik mendadak, transaksi yang baru saja dikonfirmasi akan hilang dari binlog, menyebabkan inkonsistensi fatal saat failover.
   • Solusi: Selalu gunakan nilai 1 di lingkungan produksi demi durabilitas data ACID.

2. Topologi Replikasi Bercabang (Complex Diamond Topology):

   • Anti-Pattern: Membuat satu master memiliki banyak slave turunan yang tidak terstruktur dengan baik.
   • Dampak: Orchestrator kesulitan menghitung jalur promosi optimal saat master utama runtuh.
   • Solusi: Gunakan topologi linier atau hierarchical replication yang rapi dan terdaftar jelas di antarmuka web Orchestrator.

3. Ketiadaan Pemantauan Disk Space pada Binlog:

   • Anti-Pattern: Membiarkan binlog menumpuk tanpa batasan rotasi.
   • Dampak: Partisi disk /var/log penuh, menyebabkan MySQL berhenti total secara mendadak.
   • Solusi: Tetapkan expire_logs_days = 7 atau binlog_expire_logs_seconds = 604800 di konfigurasi mysqld.cnf.

9. Benchmark, Metrik Kinerja & Tuning Performa

Untuk menguji ketahanan kluster MySQL Orchestrator, kita menggunakan utilitas sysbench untuk melakukan simulasi beban OLTP (Online Transaction Processing).

Perintah Benchmark Sysbench

1. Persiapan Data Uji (10 Tabel, masing-masing 1 juta baris):

   sysbench /usr/share/sysbench/oltp_read_write.lua \
     --mysql-host=127.0.0.1 \
     --mysql-port=3306 \
     --mysql-user=orchestrator \
     --mysql-password='StrongOrchestratorPassword2026!' \
     --mysql-db=test \
     --table-size=1000000 \
     --tables=10 \
     prepare
   

2. Eksekusi Pengujian Beban Tinggi selama 10 Menit:

   sysbench /usr/share/sysbench/oltp_read_write.lua \
     --mysql-host=127.0.0.1 \
     --mysql-port=3306 \
     --mysql-user=orchestrator \
     --mysql-password='StrongOrchestratorPassword2026!' \
     --mysql-db=test \
     --table-size=1000000 \
     --tables=10 \
     --threads=64 \
     --time=600 \
     --report-interval=10 \
     run
   

Interpretasi Hasil & Tuning Lanjutan

• Perhatikan metrik Transactions per second (TPS) dan Latency 95th percentile.
• Jika latensi melonjak saat beban tinggi, lakukan tuning pada parameter InnoDB Buffer Pool (innodb_buffer_pool_size disetel 70-80% dari total RAM fisik server) dan tingkatkan innodb_io_capacity sesuai spesifikasi IOPS SSD Anda (misalnya innodb_io_capacity = 2000).

10. Checklist Penerapan Produksi & Kesimpulan

Sebelum meluncurkan kluster MySQL berbasis Orchestrator ke lingkungan Live Production, pastikan Anda telah memverifikasi seluruh daftar periksa keamanan dan keandalan berikut:

• Semua instans MySQL telah mengaktifkan GTID (enforce_gtid_consistency=ON, gtid_mode=ON).
• Pengguna MySQL khusus untuk Orchestrator memiliki hak istimewa yang tepat dan dibatasi oleh sumber IP yang aman.
• Layanan Orchestrator dikonfigurasi sebagai systemd service yang berjalan otomatis saat server melakukan reboot.
• Skrip pemulihan failover otomatis telah diuji coba melalui simulasi chaos engineering (mematikan mesin master secara paksa menggunakan sudo poweroff).
• Pemantauan metrik Prometheus dan Grafana aktif untuk memantau replication lag, status node, dan penggunaan disk.
• Prosedur backup harian menggunakan mysqldump atau Percona XtraBackup berjalan secara terjadwal melalui Cron.

Dengan menerapkan arsitektur ini secara disiplin, infrastruktur web enterprise Anda kini memiliki sistem pertahanan basis data yang tangguh, mandiri, dan siap menghadapi lonjakan traffic ekstrem tanpa rasa khawatir.