Scaling MySQL dengan Master-Slave Replication & ProxySQL

1. Konteks Industri & Analisis Akar Masalah

Dalam lanskap arsitektur web modern, skalabilitas basis data relasional (RDBMS) seperti MySQL tetap menjadi salah satu tantangan paling krusial yang dihadapi oleh Infrastructure Architect dan Database Engineer. Ketika sebuah aplikasi web bertumbuh dari ribuan menjadi jutaan active users, beban operasional pada sistem penyimpanan data melonjak secara eksponensial.

Sistem monolitik dengan satu instans MySQL tunggal (Single Point of Failure / SPOF) dengan cepat akan mencapai batas jenuhnya (saturation point). Bottleneck performa umumnya bermanifestasi pada:

1. I/O Disk Saturation: Eksekusi heavy read queries dan write operations secara simultan menyebabkan tingginya antrean disk read/write (IOPS limit tercapai), terutama pada penyimpanan berbasis SSD konvensional atau HDD.
2. CPU Throttling & Connection Exhaustion: Lonjakan jumlah koneksi bersamaan (concurrent connections) menghabiskan alokasi memori dan thread CPU untuk manajemen koneksi, alih-alih mengeksekusi kueri bisnis.
3. Lock Contention: Transaksi tulis (INSERT, UPDATE, DELETE) yang mengunci baris atau tabel tertentu menahan antrean operasi baca (SELECT), mengakibatkan latensi tinggi pada aplikasi backend (PHP-FPM, Node.js, Go).

Sebelum penerapan arsitektur Read/Write Splitting berbasis Master-Slave Replication dan ProxySQL, aplikasi biasanya melakukan koneksi langsung ke satu database tunggal. Konfigurasi ini rentan terhadap kegagalan total ketika terjadi lonjakan traffic mendadak (traffic spike), lonjakan penggunaan koneksi yang berujung pada error Too many connections, dan risiko kehilangan data jika server fisik mengalami kerusakan katastrofik.

Setelah penerapan arsitektur Enterprise ini, beban baca didistribusikan secara merata ke multiple Read Replicas (Slave nodes), sementara operasi tulis dikonsolidasikan secara eksklusif ke Master node. ProxySQL bertindak sebagai cerdas SQL-aware proxy di lapisan tengah (middleware layer), yang secara otomatis merutekan kueri berdasarkan pola sintaksis, memantau kesehatan node (health checking), menangani connection pooling, dan melakukan failover secara otomatis tanpa memerlukan perubahan kode pada lapisan aplikasi backend.

2. Konsep Inti & Deep Dive Arsitektur

Arsitektur High Availability MySQL dengan ProxySQL mengintegrasikan tiga komponen utama: MySQL Master Node (penulis utama / Write), MySQL Slave Nodes (pembaca / Read Replicas menggunakan Semi-Sync Replication), dan ProxySQL (lapisan perutean kueri cerdas).

Alur Data & Eksekusi (Data Flow)

[Client Application: PHP/Go/Node.js]
                  │
                  ▼ (Koneksi SQL ke Port 6033)
          [ProxySQL Layer]
                  │
      ┌───────────┴───────────┐
      │ (Query Analysis)      │
      ▼                       ▼
 [WRITE Query]           [READ Query]
      │                       │
      ▼                       ▼
[MySQL Master Node]    [MySQL Slave Nodes]
      │                       │
      └───────(Binlog)────────┘
         Semi-Sync Replication

1. Koneksi Klien: Aplikasi backend terhubung ke ProxySQL (biasanya pada port 6033), bukan langsung ke instans MySQL fisik.
2. Query Parsing & Rewriting: ProxySQL menganalisis struktur AST (Abstract Syntax Tree) dari setiap kueri SQL yang masuk untuk menentukan apakah kueri tersebut berjenis SELECT (bukan bagian dari transaksi eksplisit BEGIN/COMMIT) atau kueri modifikasi data (INSERT, UPDATE, DELETE, REPLACE, atau transaksi tertulis).
3. Query Routing:
   • Kueri Tulis diarahkan ke Hostgroup Master (misalnya Hostgroup 10).
   • Kueri Baca diarahkan ke Hostgroup Slave (misalnya Hostgroup 20), dengan algoritma load balancing seperti Round-Robin atau Weight-based.
4. Replikasi Data: MySQL Master mencatat perubahan data ke dalam Binary Log (Binlog). Slave nodes mengambil Binlog tersebut dan menerapkannya secara Semi-Synchronous, memastikan bahwa setidaknya satu slave telah menerima dan mencatat log transaksi ke relay log sebelum transaksi dianggap sukses oleh master, meminimalisir replication lag dan risiko kehilangan data (data loss).

3. Perbandingan Komparatif / Analisis Pendekatan

Untuk memahami nilai strategis dari arsitektur ini, berikut adalah perbandingan mendalam antara tiga pendekatan umum pengelolaan database skala menengah hingga besar.

4. Prasyarat Sistem & Persiapan Lingkungan

Sebelum memulai implementasi, pastikan Anda telah menyiapkan setidaknya 3 unit server (atau Virtual Private Server / VPS) yang terhubung dalam satu jaringan lokal (VPC) dengan latensi minimal (< 2ms):

1. MySQL Master Node: IP 192.168.1.10 (OS: Ubuntu 22.04 LTS, MySQL 8.0)
2. MySQL Slave Node: IP 192.168.1.11 (OS: Ubuntu 22.04 LTS, MySQL 8.0)
3. ProxySQL Node: IP 192.168.1.12 (OS: Ubuntu 22.04 LTS, ProxySQL v2.5+)

Persiapan Sistem Operasi & Firewall

Pastikan port utama terbuka pada masing-masing node:

• MySQL: Port 3306
• ProxySQL: Port 6033 (Client traffic), Port 6032 (Admin interface)

# Jalankan perintah ini pada semua node untuk memastikan sinkronisasi waktu (NTP)
sudo apt update && sudo apt install -y chrony
sudo systemctl enable --now chrony

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:

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

# 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

Restart layanan MySQL:

sudo systemctl restart mysql

Buat akun replikasi di MySQL Master:

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

-- Buat juga user khusus pemantauan untuk ProxySQL
CREATE USER 'proxysql_monitor'@'%' IDENTIFIED WITH mysql_native_password BY 'MonitorPass123!';
GRANT REPLICATION CLIENT ON *.* TO 'proxysql_monitor'@'%';
FLUSH PRIVILEGES;

Dapatkan status Master untuk inisialisasi slave:

SHOW MASTER STATUS;

Catat nilai File dan Position yang dihasilkan.

Langkah 2: Konfigurasi MySQL Slave (192.168.1.11)

Edit file konfigurasi MySQL /etc/mysql/mysql.conf.d/mysqld.cnf:

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

# Pengaturan Semi-Sync Replication
plugin-load-add         = semisync_master.so
plugin-load-add         = semisync_slave.so
rpl_semi_sync_master_enabled = 0
rpl_semi_sync_slave_enabled  = 1

Restart layanan MySQL:

sudo systemctl restart mysql

Hubungkan slave ke master menggunakan perintah SQL:

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 & Konfigurasi ProxySQL (192.168.1.12)

Instal ProxySQL dari repository resmi:

sudo wget -O /etc/apt/trusted.gpg.d/proxysql_repo_key.gpg https://repo.proxysql.com/proxysql/repo_pub_key
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 enable --now proxysql

Akses antarmuka admin ProxySQL:

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

Konfigurasikan ProxySQL melalui SQL interaktif:

-- 1. Tambahkan MySQL Servers ke Hostgroups
INSERT INTO mysql_servers(hostgroup, hostname, port) VALUES (10, '192.168.1.10', 3306);
INSERT INTO mysql_servers(hostgroup, hostname, port) VALUES (20, '192.168.1.11', 3306);

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

-- 3. Tambahkan Database Users
INSERT INTO mysql_users(username, password, default_hostgroup) VALUES ('app_user', 'AppUserPass123!', 10);

-- 4. Konfigurasi Query Rules untuk Read/Write Splitting
-- Semua kueri tulis atau transaksi eksplisit diarahkan ke Hostgroup 10 (Master)
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply) 
VALUES (1, 1, '^SELECT.*FOR UPDATE', 10, 1);

-- Semua kueri SELECT standar diarahkan ke Hostgroup 20 (Slave)
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply) 
VALUES (2, 1, '^SELECT', 20, 1);

-- 5. Simpan konfigurasi ke memori disk dan 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

[!IMPORTANT] Memahami parameter konfigurasi secara mendalam mencegah kegagalan runtime yang berpotensi melumpuhkan sistem produksi.

• rpl_semi_sync_master_timeout = 1000: Parameter pada MySQL master ini menentukan batas waktu tunggu (dalam milidetik) bagi master untuk menerima konfirmasi ack dari setidaknya satu slave. Jika slave tidak merespons dalam waktu 1 detik, master akan melakukan fallback otomatis ke replikasi asinkron standar untuk mencegah terhentinya operasi tulis aplikasi.
• match_pattern = '^SELECT': Pola Regular Expression (Regex) pada tabel mysql_query_rules ProxySQL. Baris ini memindai setiap kueri SQL yang dimulai dengan kata kunci SELECT dan mengarahkannya ke hostgroup slave (20). Perlu diperhatikan bahwa kueri kompleks yang melibatkan subquery atau fungsi tertentu harus dievaluasi dengan cermat agar tidak salah jalur.
• destination_hostgroup = 10 (untuk FOR UPDATE): Kueri baca yang memerlukan penguncian baris eksplisit (SELECT ... FOR UPDATE) wajib dikirimkan ke Master (10) untuk menghindari ketidakkonsistenan data akibat pembacaan dari slave yang mungkin mengalami replication lag.

7. Skenario Produksi & Studi Kasus Dunia Nyata

Dalam lingkungan E-commerce High-Concurrency dengan 50.000 requests per second (RPS), pembagian beban kerja berbasis ProxySQL memberikan dampak yang signifikan:

• Traffic Katalog Produk (Read-Heavy): 90% dari total kueri aplikasi adalah pencarian produk, navigasi kategori, dan pemuatan halaman detail. Seluruh kueri ini didistribusikan secara merata ke klaster 4 Read Replicas, menjaga utilisasi CPU pada Master tetap berada di bawah 25%.
• Sistem Pembayaran & Checkout (Write-Heavy): Proses transaksi, pengurangan stok inventaris, dan pembuatan pesanan diarahkan secara eksklusif ke Master Node.
• Connection Multiplexing: Aplikasi backend Node.js membuka hingga 5.000 koneksi bersamaan ke ProxySQL. ProxySQL secara cerdas memetakan ribuan koneksi klien ini ke hanya 50 koneksi persisten yang efisien menuju backend MySQL fisik, mencegah error kehabisan memori pada server database.

8. Common Pitfalls, Anti-Patterns & Solusi

1. Replication Lag yang Mengabaikan Konsistensi Sesi (Read-Your-Own-Writes):
   • Masalah: Pengguna memperbarui profil pengguna, lalu dialihkan ke halaman profil di mana data lama masih muncul karena Slave belum menyinkronkan perubahan dari Master.
   • Solusi: Gunakan fitur Query Cache ProxySQL untuk durasi singkat, atau arahkan kueri baca dari sesi pengguna yang baru melakukan modifikasi secara langsung ke Master menggunakan trik pin session atau GTID consistency checking.
2. Kegagalan Pemantauan (Monitor User Privileges):
   • Masalah: proxysql_monitor tidak memiliki hak akses REPLICATION CLIENT, menyebabkan ProxySQL menganggap semua slave dalam kondisi Offline_SOFT.
   • Solusi: Pastikan hak akses diberikan secara presisi: GRANT REPLICATION CLIENT, SELECT ON *.* TO 'proxysql_monitor'@'%';.
3. Salah Konfigurasi Time-to-Live (TTL) pada Connection Pool:
   • Masalah: Koneksi idle ditutup oleh firewall jaringan, menyebabkan error Lost connection to MySQL server during query pada aplikasi.
   • Solusi: Atur parameter mysql-connection_max_idle_time dan aktifkan KeepAlive pada konfigurasi global ProxySQL.

9. Benchmark, Metrik Kinerja & Tuning Performa

Untuk mengukur efektivitas arsitektur ini, gunakan alat pengujian beban seperti sysbench atau k6.

# Contoh pengujian benchmark menggunakan sysbench pada ProxySQL (Port 6033)
sysbench /usr/share/sysbench/oltp_read_only.lua \
  --mysql-host=192.168.1.12 \
  --mysql-port=6033 \
  --mysql-user=app_user \
  --mysql-password=AppUserPass123! \
  --threads=64 \
  --time=60 \
  run

Metrik Kunci yang Harus Dipantau:

• ProxySQL Query Latency: Pastikan waktu proses internal ProxySQL (Query_Processor_time) berada di bawah 0.5ms.
• Backend Connection Pool Utilization: Pantau metrik mysql_connection_pool di antarmuka admin ProxySQL untuk menghindari antrean koneksi penuh.
• Threads Running: Pastikan jumlah thread aktif di MySQL Master dan Slave stabil di bawah batas kapasitas CPU inti (threads_running).

10. Checklist Penerapan Produksi & Kesimpulan

Sebelum meluncurkan arsitektur ini ke lingkungan produksi, pastikan seluruh item dalam checklist berikut telah terpenuhi:

• Konfigurasi NTP aktif dan sinkron di seluruh node master, slave, dan proxy.
• Uji coba skenario Manual Failover dan Automatic Failover berhasil tanpa error pada aplikasi.
• Pengaturan firewall (UFW / iptables) hanya mengizinkan lalu lintas internal antar node terpercaya.
• Pemantauan metrik (Prometheus + Grafana) dikonfigurasi untuk memantau Replication Lag secara real-time.
• Pengujian pencadangan (Backup & Restore) rutin menggunakan mysqldump atau Percona XtraBackup pada Slave Node telah diverifikasi.

Dengan mengintegrasikan MySQL Master-Slave Replication dan ProxySQL, infrastruktur web Anda kini memiliki fondasi yang tangguh, sangat ter skalakan, dan siap menangani pertumbuhan bisnis jangka panjang tanpa hambatan performa database.