Arsitektur High Availability MySQL dengan Master-Slave Replication & ProxySQL

1. Konteks Industri & Analisis Akar Masalah

Dalam lanskap arsitektur web modern, skalabilitas dan High Availability (HA) pada lapisan database adalah benteng pertahanan terakhir yang menentukan apakah sebuah aplikasi enterprise dapat bertahan di bawah tekanan trafik tinggi atau mengalami downtime katastrofik. Pola penerapan tradisional di mana aplikasi backend (PHP, Go, Node.js) melakukan query baca (read) dan tulis (write) secara langsung ke satu instance database MySQL tunggal (Single Point of Failure / SPOF) adalah resep bencana untuk aplikasi dengan konkurensi masif.

Bottleneck Performa pada Arsitektur Konvensional

1. I/O Saturation: Seluruh operasi read (SELECT) dan write (INSERT, UPDATE, DELETE) bersaing memperebutkan resource CPU, memori, dan subsystem disk (I/OPS) yang sama pada satu mesin.
2. Keterbatasan Locking: Operasi tulis yang intensif dapat memicu table-level atau row-level locking yang menghambat eksekusi query baca yang seharusnya ringan dan cepat.
3. Risiko Bencana Total: Ketika server database utama mengalami kegagalan hardware, kernel panic, atau kehabisan memori (OOM Killer), seluruh ekosistem aplikasi lumpuh seketika karena tidak ada jalur failover otomatis.

Perbandingan Kondisi: Sebelum vs Sesudah Implementasi HA

• Sebelum: Aplikasi menggunakan satu server MySQL (Primary). Traffic tinggi menyebabkan latensi melonjak hingga ribuan milidetik. Saat server down, situs offline total selama proses pemulihan manual (cold backup restoration).
• Sesudah: Aplikasi terhubung ke database virtualization layer (ProxySQL). Query tulis dialihkan secara otomatis ke Master, sementara query baca didistribusikan secara cerdas ke kluster Slave (Read/Write Splitting). Jika Master gagal, ProxySQL bersama orchestrator mendeteksi kegagalan dan mempromosikan Slave secara seamless tanpa modifikasi kode di sisi aplikasi.

[!IMPORTANT] Mengandalkan koneksi langsung aplikasi ke beberapa IP database tanpa middleware routing adalah antipola karena akan membebani application code dengan logika connection pooling dan failover handling yang rumit.

2. Konsep Inti & Deep Dive Arsitektur

Arsitektur High Availability MySQL dengan Master-Slave Replication dan ProxySQL menggabungkan tiga komponen utama: MySQL Primary (Master), MySQL Replica (Slave), dan ProxySQL sebagai High-Performance MySQL Proxy.

Alur Data & Eksekusi (Under The Hood)

1. Aplikasi mengirimkan SQL query melalui port koneksi ProxySQL (biasanya port 6033).
2. ProxySQL Query Processor menganalisis apakah query tersebut berjenis SELECT atau INSERT/UPDATE/DELETE berdasarkan aturan Query Rules yang telah dikonfigurasi.
3. Jika query adalah Write, ProxySQL merutekannya ke Master Node.
4. Jika query adalah Read, ProxySQL menggunakan algoritma load balancing (seperti Round-Robin atau Weight-based) untuk mendistribusikannya ke salah satu Slave Node.
5. MySQL Master mengeksekusi transaksi dan mencatat perubahannya ke dalam Binary Log (Binlog).
6. MySQL Slave mengambil (fetch) Binlog dari Master melalui I/O Thread, menulisnya ke Relay Log, lalu SQL Thread mengeksekusi ulang perubahan tersebut pada database lokalnya.

+-------------+
| Application |
+-------------+
       |
       | SQL Query (Port 6033)
       v
+---------------------------------------------------+
|                     ProxySQL                      |
|  +--------------------+   +--------------------+  |
|  |    Read Queries    |-->|    Slave Nodes     |  |
|  +--------------------+   +--------------------+  | 
|                           | (Replication Lag)  |  |
|  +--------------------+   +--------------------+  |
|  |   Write Queries    |-->|    Master Node     |  |
|  +--------------------+   +--------------------+  |
+---------------------------------------------------+
                              |
                              | Binlog Streaming
                              v
                        +--------------+
                        | Slave Nodes  |
                        +--------------+

3. Perbandingan Komparatif / Analisis Pendekatan

Membangun HA MySQL memiliki beberapa jalur pendekatan arsitektural. Berikut perbandingan mendalam antara Master-Slave dengan ProxySQL melawan alternatif lain seperti MySQL Group Replication (MGR) dan MariaDB Galera Cluster.

[!TIP] Gunakan arsitektur Master-Slave + ProxySQL jika beban aplikasi Anda didominasi oleh operasi baca (read-heavy dengan rasio 80:20 atau 90:10) dan Anda menginginkan kontrol penuh terhadap routing query serta connection pooling.

4. Prasyarat Sistem & Persiapan Lingkungan

Sebelum memulai implementasi teknis, pastikan environment infrastruktur Anda memenuhi spesifikasi berikut:

Spesifikasi Minimum Node

• OS: Ubuntu Server 22.04 LTS / Debian 12 / Rocky Linux 9
• Node 1 (Master): IP 192.168.1.10, MySQL 8.0, RAM 4GB, CPU 2 Cores
• Node 2 (Slave): IP 192.168.1.11, MySQL 8.0, RAM 4GB, CPU 2 Cores
• Node 3 (ProxySQL): IP 192.168.1.12, ProxySQL 2.5+, RAM 2GB, CPU 1 Core

Verifikasi Jaringan & Firewall

Pastikan port-port berikut terbuka dan tidak diblokir oleh UFW atau Firewalld:

• Port 3306: Komunikasi MySQL internal dan replikasi.
• Port 6033: ProxySQL port klien (aplikasi terhubung ke sini).
• Port 6032: ProxySQL Admin interface.

5. Panduan Implementasi Langkah demi Langkah

Langkah 1: Konfigurasi MySQL Master (192.168.1.10)

Edit file konfigurasi MySQL /etc/mysql/mysql.conf.d/mysqld.cnf (atau /etc/my.cnf):

[mysqld]
server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log
binlog_format           = ROW
expire_logs_days        = 7
relay-log               = mysql-relay-bin
bind-address            = 0.0.0.0
read_only               = 0

Restart layanan MySQL:

sudo systemctl restart mysql

Buat user replikasi dan user monitoring di MySQL Master:

CREATE USER 'replicator'@'%' IDENTIFIED WITH mysql_native_password BY 'StrongReplPassword123!';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%';

CREATE USER 'proxysql_monitor'@'%' IDENTIFIED WITH mysql_native_password BY 'MonitorPassword123!';
GRANT SELECT ON performance_schema.* TO 'proxysql_monitor'@'%';

CREATE USER 'app_user'@'%' IDENTIFIED WITH mysql_native_password BY 'AppUserPassword123!';
GRANT ALL PRIVILEGES ON production_db.* TO 'app_user'@'%';
FLUSH PRIVILEGES;

Dapatkan status master untuk sinkronisasi awal:

SHOW MASTER STATUS;

Catat nilai File dan Position yang dihasilkan.

Langkah 2: Konfigurasi MySQL Slave (192.168.1.11)

Edit file konfigurasi MySQL di Slave:

[mysqld]
server-id               = 2
relay-log               = mysql-relay-bin
log_bin                 = /var/log/mysql/mysql-bin.log
read_only               = 1
bind-address            = 0.0.0.0

Restart layanan MySQL Slave:

sudo systemctl restart mysql

Hubungkan Slave ke Master menggunakan SQL command:

CHANGE MASTER TO
    MASTER_HOST='192.168.1.10',
    MASTER_USER='replicator',
    MASTER_PASSWORD='StrongReplPassword123!',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=157;

START SLAVE;

Verifikasi status slave:

SHOW SLAVE STATUSG

Pastikan Slave_IO_Running: Yes dan Slave_SQL_Running: Yes.

Langkah 3: Instalasi dan Konfigurasi ProxySQL (192.168.1.12)

Tambahkan repository ProxySQL dan instal:

sudo apt update
sudo apt install -y lsb-release gnupg2 wget
wget -O - 'https://repo.proxysql.com/ProxySQL/proxysql-2.5.x/repo_pub_key' | sudo apt-key add -
echo "deb https://repo.proxysql.com/ProxySQL/proxysql-2.5.x/$(lsb_release -sc)/ ./" | sudo tee /etc/apt/sources.list.d/proxysql.list
sudo apt update
sudo apt install -y proxysql
sudo systemctl start proxysql

Masuk ke ProxySQL Admin Interface (Default password: admin / admin):

mysql -u admin -padmin -h 127.0.0.1 -P 6032

Konfigurasikan Server Pools, Monitor, Users, dan Query Rules:

-- 1. Tambahkan Server ke Server Groups
INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (1, '192.168.1.10', 3306);
INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (2, '192.168.1.11', 3306);

-- 2. Konfigurasi User Monitoring
UPDATE global_variables SET variable_value='proxysql_monitor' WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='MonitorPassword123!' WHERE variable_name='mysql-monitor_password';

-- 3. Konfigurasi User Aplikasi
INSERT INTO mysql_users(username, password, default_hostgroup) VALUES ('app_user', '*436D2576974B39B6F38592FA0CCDBBD8D46850EA', 1);

-- Catatan: Password di atas adalah bentuk hash SHA1 dari 'AppUserPassword123!' dengan prefiks bintang (*)

-- 4. Konfigurasi Read/Write Splitting Rules
INSERT INTO mysql_query_rules(rule_id, active, match_pattern, destination_hostgroup, apply) VALUES (1, 1, '^SELECT.*FOR UPDATE$', 1, 1);
INSERT INTO mysql_query_rules(rule_id, active, match_pattern, destination_hostgroup, apply) VALUES (2, 1, '^SELECT', 2, 1);

-- 5. Simpan Konfigurasi ke Disk dan Muat ke Runtime
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

6. Bedah Kode & Penjelasan Detail Baris Kunci

• binlog_format = ROW: Baris ini krusial pada konfigurasi MySQL Master. Format ROW memastikan setiap perubahan baris data dicatat secara presisi ke dalam binary log, menghindari inkonsistensi replikasi yang sering terjadi pada format STATEMENT saat mengeksekusi query non-deterministik.
• read_only = 1: Diterapkan pada MySQL Slave untuk mencegah aplikasi atau user secara tidak sengaja melakukan operasi tulis langsung ke server slave, yang dapat merusak integritas rantai replikasi.
• destination_hostgroup = 2 pada ProxySQL Query Rules: Mengarahkan seluruh query yang cocok dengan pola regex ^SELECT (pencarian data biasa) menuju hostgroup 2 (Slave Nodes), sementara query tulis otomatis jatuh ke default hostgroup (Hostgroup 1 - Master Node).

7. Skenario Produksi & Studi Kasus Dunia Nyata

Pada sistem e-commerce berskala besar dengan ribuan transaksi per detik, lonjakan trafik mendadak saat flash sale dapat melumpuhkan database tradisional.

Implementasi Nyata

• Koneksi Aplikasi: Framework backend (seperti Laravel atau Go Fiber) dikonfigurasi untuk terhubung ke ProxySQL di 192.168.1.12:6033 alih-alih langsung ke database fisik.
• Pemisahan Beban: Dashboard analitik dan laporan penjualan mengeksekusi query SELECT masif yang diarahkan secara otomatis ke 3 node Slave terpisah.
• Proteksi Master: Master Node hanya menerima query INSERT pesanan (checkout) dan UPDATE stok, menjadikannya bersih dari beban query analitik yang berat.

8. Common Pitfalls, Anti-Patterns & Solusi

1. Replication Lag yang Tinggi:
   • Masalah: Slave tertinggal jauh dari Master akibat query baca yang kompleks atau spesifikasi hardware slave yang lebih rendah.
   • Solusi: Aktifkan Multi-threaded Slave Replication di MySQL (slave_parallel_workers = 4) dan optimalkan indeks pada tabel.
2. Koneksi Putus Akibat Timeout ProxySQL:
   • Masalah: Aplikasi mengalami error MySQL server has gone away.
   • Solusi: Sesuaikan parameter mysql-connection_max_idle_time dan mysql-connect_timeout_server di ProxySQL.
3. Lupa Menyimpan Konfigurasi ProxySQL ke Disk:
   • Masalah: Konfigurasi hilang saat service ProxySQL direstart.
   • Solusi: Selalu jalankan perintah SAVE MYSQL ... TO DISK setelah melakukan modifikasi runtime.

9. Benchmark, Metrik Kinerja & Tuning Performa

Untuk menguji ketahanan kluster, gunakan tool benchmark standar industri seperti sysbench:

sysbench /usr/share/sysbench/oltp_read_write.lua \
  --mysql-host=192.168.1.12 \
  --mysql-port=6033 \
  --mysql-user=app_user \
  --mysql-password=AppUserPassword123! \
  --mysql-db=production_db \
  --tables=10 \
  --table-size=100000 \
  prepare

# Jalankan eksekusi benchmark
sysbench /usr/share/sysbench/oltp_read_write.lua \
  --mysql-host=192.168.1.12 \
  --mysql-port=6033 \
  --mysql-user=app_user \
  --mysql-password=AppUserPassword123! \
  --mysql-db=production_db \
  --tables=10 \
  --table-size=100000 \
  --threads=64 \
  --time=60 \
  run

Interpretasi & Tuning Lanjutan

• Pantau metrik QPS (Queries Per Second) melalui ProxySQL Stats Interface (SELECT * FROM stats_mysql_connection_pool;).
• Jika latensi tinggi, sesuaikan ukuran innodb_buffer_pool_size di MySQL (atur hingga 70-80% dari total RAM server database).

10. Checklist Penerapan Produksi & Kesimpulan

• Konfigurasi server-id unik pada setiap node MySQL.
• Aktifkan binlog_format = ROW dan log_bin pada Master.
• Pastikan read_only = 1 aktif pada semua Slave.
• ProxySQL terkonfigurasi dengan aturan Read/Write Splitting yang benar.
• Lakukan uji coba failover manual (Kill Master Node) untuk memastikan ProxySQL mengalihkan trafik dengan mulus.
• Monitor Replication Lag secara berkala menggunakan sistem monitoring seperti Prometheus dan Grafana.

Dengan menerapkan arsitektur Master-Slave Replication yang dikombinasikan dengan ProxySQL, infrastruktur database enterprise Anda kini memiliki ketahanan tinggi, skalabilitas baca yang optimal, serta performa puncak yang siap menghadapi lonjakan trafik ekstrem di lingkungan produksi.