Implementasi Database Cluster Master Slave dengan PHP MySqli

By | 9 October 2025
0 Comment

Dalam artikel kali ini kita akan coba membuat contoh atau implementasi Database Cluster arsitektur Master Slave dengan PHP MySqli: termasuk metode round robin, failover dan health check otomatis berkala.

Konsep Master-Slave MySQL

Master → server utama untuk INSERT, UPDATE, DELETE (write operation).

Slave → server yang mereplikasi data dari master secara otomatis, biasanya untuk SELECT (read operation).

Tujuannya:

  • Membagi beban kerja (write → master, read → slave).
  • Failover (kalau master down, slave bisa di-promote).
  • Performa lebih tinggi pada aplikasi skala besar.

Skema Sederhana

  • Master DB: 192.168.1.10
  • Slave DB: 192.168.1.11
  • Database: shopdb

Contoh Implementasi PHP MySQLi

Kita bisa buat class Database Connection yang otomatis memilih master untuk INSERT/UPDATE/DELETE dan slave untuk SELECT.

<?php
class DatabaseCluster {
    private $master;
    private $slave;

    public function __construct() {
        // Koneksi ke MASTER (write)
        $this->master = new mysqli("192.168.1.10", "user_master", "password_master", "shopdb");
        if ($this->master->connect_error) {
            die("Koneksi ke Master gagal: " . $this->master->connect_error);
        }

        // Koneksi ke SLAVE (read)
        $this->slave = new mysqli("192.168.1.11", "user_slave", "password_slave", "shopdb");
        if ($this->slave->connect_error) {
            die("Koneksi ke Slave gagal: " . $this->slave->connect_error);
        }
    }

    // Query SELECT → ke SLAVE
    public function queryRead($sql) {
        $result = $this->slave->query($sql);
        if (!$result) {
            die("Error SELECT di Slave: " . $this->slave->error);
        }
        return $result;
    }

    // Query INSERT/UPDATE/DELETE → ke MASTER
    public function queryWrite($sql) {
        $result = $this->master->query($sql);
        if (!$result) {
            die("Error Write di Master: " . $this->master->error);
        }
        return $result;
    }

    public function __destruct() {
        $this->master->close();
        $this->slave->close();
    }
}
?>

Catatan Penting

  • Sinkronisasi Master-Slave harus sudah di-setup di server MySQL (CHANGE MASTER TO …, START SLAVE;).
  • Biasanya untuk production digunakan load balancer (misalnya ProxySQL, HAProxy, atau MariaDB MaxScale) agar aplikasi tidak perlu tahu detail master/slave.
  • Bisa juga pakai random slave selection kalau lebih dari 1 slave untuk load balancing query baca.

Implementasi Lebih dari 1 Slave

Jika terdapat lebih dari 1 slave (misalnya 3 server untuk baca), kita bisa pakai metode round robin supaya query SELECT tidak hanya ke 1 slave saja tapi dibagi rata. Berikut ini contoh implementasi dengan lebih dari 1 slave (misalnya ada 3 slave, query SELECT diarahkan secara bergantian / round robin).

Skema

Master (Write): 192.168.1.10

Slaves (Read):

  • 192.168.1.11
  • 192.168.1.12
  • 192.168.1.13

Contoh Implementasi Round Robin dengan PHP MySQLi

<?php
class DatabaseCluster {
    private $master;
    private $slaves = [];
    private $slaveCount;
    private $slaveIndex = 0;

    public function __construct() {
        // MASTER (untuk INSERT/UPDATE/DELETE)
        $this->master = new mysqli("192.168.1.10", "user_master", "password_master", "shopdb");
        if ($this->master->connect_error) {
            die("Koneksi ke Master gagal: " . $this->master->connect_error);
        }

        // SLAVES (untuk SELECT)
        $slaveConfigs = [
            ["192.168.1.11", "user_slave", "password_slave", "shopdb"],
            ["192.168.1.12", "user_slave", "password_slave", "shopdb"],
            ["192.168.1.13", "user_slave", "password_slave", "shopdb"],
        ];

        foreach ($slaveConfigs as $cfg) {
            $conn = new mysqli($cfg[0], $cfg[1], $cfg[2], $cfg[3]);
            if ($conn->connect_error) {
                die("Koneksi ke Slave {$cfg[0]} gagal: " . $conn->connect_error);
            }
            $this->slaves[] = $conn;
        }

        $this->slaveCount = count($this->slaves);
    }

    // ROUND ROBIN untuk SELECT
    private function getSlaveConnection() {
        $conn = $this->slaves[$this->slaveIndex];
        $this->slaveIndex = ($this->slaveIndex + 1) % $this->slaveCount;
        return $conn;
    }

    // Query SELECT → ke SLAVE (bergantian)
    public function queryRead($sql) {
        $conn = $this->getSlaveConnection();
        $result = $conn->query($sql);
        if (!$result) {
            die("Error SELECT di Slave: " . $conn->error);
        }
        return $result;
    }

    // Query INSERT/UPDATE/DELETE → ke MASTER
    public function queryWrite($sql) {
        $result = $this->master->query($sql);
        if (!$result) {
            die("Error Write di Master: " . $this->master->error);
        }
        return $result;
    }

    public function __destruct() {
        $this->master->close();
        foreach ($this->slaves as $conn) {
            $conn->close();
        }
    }
}
?>

Contoh Penggunaan

<?php
require_once "DatabaseCluster.php";

$db = new DatabaseCluster();

// INSERT ke MASTER
$db->queryWrite("INSERT INTO products (name, price) VALUES ('Produk X', 50000)");

// SELECT ke SLAVE (bergantian setiap query)
for ($i = 1; $i <= 5; $i++) {
    $result = $db->queryRead("SELECT * FROM products LIMIT 3");
    echo "<b>Query ke-$i:</b><br>";
    while ($row = $result->fetch_assoc()) {
        echo $row['name'] . " - Rp " . $row['price'] . "<br>";
    }
    echo "<hr>";
}
?>

Jika script dijalankan query SELECT akan diarahkan ke slave1, slave2, slave3, lalu balik lagi ke slave1 (round robin).

Bagaimana Jika Terjadi Failover?

Failover artinya:

  • Kalau master down, sistem otomatis pilih salah satu slave untuk jadi pengganti master sementara.
  • Biasanya di production dipakai orchestrator (MHA, Orchestrator, ProxySQL), tapi kita bisa buat logic sederhana di PHP.

Skema

Master (utama untuk write): 192.168.1.10

Slaves (read & backup):

  • 192.168.1.11
  • 192.168.1.12

Contoh Implementasi PHP MySQLi (Failover)

<?php
class DatabaseCluster {
    private $master;
    private $slaves = [];
    private $slaveCount;
    private $slaveIndex = 0;

    private $currentMasterConfig;

    public function __construct() {
        // MASTER (untuk INSERT/UPDATE/DELETE)
        $this->currentMasterConfig = ["192.168.1.10", "user_master", "password_master", "shopdb"];
        $this->connectMaster();

        // SLAVES (untuk SELECT)
        $slaveConfigs = [
            ["192.168.1.11", "user_slave", "password_slave", "shopdb"],
            ["192.168.1.12", "user_slave", "password_slave", "shopdb"],
        ];

        foreach ($slaveConfigs as $cfg) {
            $conn = new mysqli($cfg[0], $cfg[1], $cfg[2], $cfg[3]);
            if ($conn->connect_error) {
                echo "⚠️ Koneksi ke Slave {$cfg[0]} gagal: " . $conn->connect_error . "<br>";
            } else {
                $this->slaves[] = $conn;
            }
        }

        $this->slaveCount = count($this->slaves);
    }

    // Fungsi untuk koneksi ke MASTER
    private function connectMaster() {
        $this->master = @new mysqli(
            $this->currentMasterConfig[0],
            $this->currentMasterConfig[1],
            $this->currentMasterConfig[2],
            $this->currentMasterConfig[3]
        );

        if ($this->master->connect_error) {
            echo "⚠️ Master DB down ({$this->currentMasterConfig[0]}). Ganti ke slave sebagai master.<br>";
            $this->promoteSlaveAsMaster();
        }
    }

    // Jika master down, pilih salah satu slave jadi master sementara
    private function promoteSlaveAsMaster() {
        if ($this->slaveCount > 0) {
            $this->master = $this->slaves[0]; // Slave pertama jadi master sementara
            echo "✅ Slave dipromosikan jadi master sementara.<br>";
        } else {
            die("❌ Tidak ada slave tersedia untuk failover!");
        }
    }

    // ROUND ROBIN untuk SELECT
    private function getSlaveConnection() {
        if ($this->slaveCount == 0) {
            return $this->master; // fallback ke master kalau tidak ada slave
        }
        $conn = $this->slaves[$this->slaveIndex];
        $this->slaveIndex = ($this->slaveIndex + 1) % $this->slaveCount;
        return $conn;
    }

    // Query SELECT → ke SLAVE
    public function queryRead($sql) {
        $conn = $this->getSlaveConnection();
        $result = $conn->query($sql);
        if (!$result) {
            die("Error SELECT: " . $conn->error);
        }
        return $result;
    }

    // Query INSERT/UPDATE/DELETE → ke MASTER (atau slave jika failover)
    public function queryWrite($sql) {
        $result = $this->master->query($sql);
        if (!$result) {
            die("Error Write: " . $this->master->error);
        }
        return $result;
    }

    public function __destruct() {
        if ($this->master && $this->master->ping()) {
            $this->master->close();
        }
        foreach ($this->slaves as $conn) {
            if ($conn && $conn->ping()) {
                $conn->close();
            }
        }
    }
}
?>

Contoh Penggunaan:

<?php
require_once "DatabaseCluster.php";

$db = new DatabaseCluster();

// Coba INSERT (jika master down, otomatis dialihkan ke slave yang dipromosikan)
$db->queryWrite("INSERT INTO products (name, price) VALUES ('Produk Failover', 75000)");

// SELECT (round robin ke slave)
$result = $db->queryRead("SELECT * FROM products LIMIT 5");
while ($row = $result->fetch_assoc()) {
    echo $row['name'] . " - Rp " . $row['price'] . "<br>";
}
?>

Penjelasan

  • Saat master mati → koneksi gagal → otomatis slave pertama dipromosikan jadi master sementara.
  • Query write (INSERT/UPDATE/DELETE) otomatis diarahkan ke master baru (slave).
  • Query read tetap memakai round robin antar slave.
  • Bisa ditambah log error/alert (misalnya kirim email ke admin kalau master down).

Implementasi dengan Load Balancing dan Failover Otomatis

Load balancing untuk query SELECT → pilih slave yang sehat (ping ok).

Failover otomatis untuk WRITE → kalau master down, cari slave terbaik lalu jadikan master sementara.

Kalau ada banyak slave, sistem bisa skip yang error.

Skema

Master (utama untuk write) → 192.168.1.10

Slaves (read + backup):

  • 192.168.1.11
  • 192.168.1.12
  • 192.168.1.13

Class Database Cluster (Load Balancing + Failover)

<?php
class DatabaseCluster {
    private $master;
    private $masterConfig;
    private $slaves = [];
    private $slaveConfigs;
    private $slaveCount;
    private $slaveIndex = 0;

    public function __construct() {
        // MASTER (utama untuk write)
        $this->masterConfig = ["192.168.1.10", "user_master", "password_master", "shopdb"];
        $this->connectMaster();

        // SLAVES (untuk read + backup)
        $this->slaveConfigs = [
            ["192.168.1.11", "user_slave", "password_slave", "shopdb"],
            ["192.168.1.12", "user_slave", "password_slave", "shopdb"],
            ["192.168.1.13", "user_slave", "password_slave", "shopdb"],
        ];
        $this->connectSlaves();
    }

    /** ===============================
     *  MASTER HANDLER
     *  =============================== */
    private function connectMaster() {
        $this->master = @new mysqli(
            $this->masterConfig[0],
            $this->masterConfig[1],
            $this->masterConfig[2],
            $this->masterConfig[3]
        );

        if ($this->master->connect_error) {
            echo "⚠️ Master {$this->masterConfig[0]} gagal, cari slave sebagai master baru...<br>";
            $this->promoteSlaveAsMaster();
        }
    }

    private function promoteSlaveAsMaster() {
        foreach ($this->slaveConfigs as $cfg) {
            $conn = @new mysqli($cfg[0], $cfg[1], $cfg[2], $cfg[3]);
            if (!$conn->connect_error) {
                $this->master = $conn;
                echo "✅ Slave {$cfg[0]} dipromosikan jadi master sementara.<br>";
                return;
            }
        }
        die("❌ Tidak ada slave sehat untuk dijadikan master!");
    }

    /** ===============================
     *  SLAVE HANDLER
     *  =============================== */
    private function connectSlaves() {
        foreach ($this->slaveConfigs as $cfg) {
            $conn = @new mysqli($cfg[0], $cfg[1], $cfg[2], $cfg[3]);
            if (!$conn->connect_error) {
                $this->slaves[] = $conn;
            } else {
                echo "⚠️ Slave {$cfg[0]} tidak bisa dihubungi, dilewati.<br>";
            }
        }
        $this->slaveCount = count($this->slaves);
    }

    private function getHealthySlave() {
        if ($this->slaveCount == 0) {
            // fallback ke master
            return $this->master;
        }

        // Round robin untuk sebar beban
        for ($i = 0; $i < $this->slaveCount; $i++) {
            $conn = $this->slaves[$this->slaveIndex];
            $this->slaveIndex = ($this->slaveIndex + 1) % $this->slaveCount;

            if ($conn->ping()) {
                return $conn;
            }
        }

        // Kalau semua slave mati → fallback ke master
        echo "⚠️ Semua slave mati, fallback ke master.<br>";
        return $this->master;
    }

    /** ===============================
     *  QUERY HANDLER
     *  =============================== */
    public function queryRead($sql) {
        $conn = $this->getHealthySlave();
        $result = $conn->query($sql);
        if (!$result) {
            die("Error SELECT: " . $conn->error);
        }
        return $result;
    }

    public function queryWrite($sql) {
        if (!$this->master->ping()) {
            echo "⚠️ Master mati saat query, coba failover...<br>";
            $this->promoteSlaveAsMaster();
        }

        $result = $this->master->query($sql);
        if (!$result) {
            die("Error WRITE: " . $this->master->error);
        }
        return $result;
    }

    /** ===============================
     *  CLEANUP
     *  =============================== */
    public function __destruct() {
        if ($this->master && $this->master->ping()) {
            $this->master->close();
        }
        foreach ($this->slaves as $conn) {
            if ($conn && $conn->ping()) {
                $conn->close();
            }
        }
    }
}
?>

Contoh Penggunaan:

<?php
require_once "DatabaseCluster.php";

$db = new DatabaseCluster();

// WRITE → ke master, atau slave dipromosikan kalau master mati
$db->queryWrite("INSERT INTO products (name, price) VALUES ('Produk LoadBalance', 99000)");

// READ → ke slave (round robin, skip yang mati)
for ($i = 1; $i <= 5; $i++) {
    $result = $db->queryRead("SELECT * FROM products LIMIT 3");
    echo "<b>Query ke-$i:</b><br>";
    while ($row = $result->fetch_assoc()) {
        echo $row['name'] . " - Rp " . $row['price'] . "<br>";
    }
    echo "<hr>";
}
?>

Penjelasan

  • Load Balancing SELECT → round robin antar slave, skip yang mati.
  • Failover WRITE → kalau master down, cari slave sehat → langsung dipromosikan jadi master sementara.
  • Fallback → kalau semua slave mati, SELECT diarahkan ke master.
  • Bisa dikembangkan dengan health check lebih advance (misalnya test query SELECT 1 tiap 5 detik).
  • Bisa ditambahkan notifikasi (misalnya kirim email/Telegram kalau master down).

Implementasi health check otomatis berkala

Tujuannya:

  • Secara berkala (misalnya setiap 10 detik via cron job), sistem akan cek apakah master/slave sehat.
  • Kalau ada node mati → ditandai statusnya down, tidak dipakai untuk query.
  • Kalau master mati → otomatis pilih slave sehat jadi master baru.

Struktur Tabel (opsional, untuk monitor)

Bisa simpan status server di tabel db_nodes misalnya:

CREATE TABLE db_nodes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    role ENUM('master','slave') NOT NULL,
    host VARCHAR(100) NOT NULL,
    user VARCHAR(50) NOT NULL,
    pass VARCHAR(50) NOT NULL,
    dbname VARCHAR(50) NOT NULL,
    status ENUM('up','down') DEFAULT 'up',
    last_check TIMESTAMP NULL
);

Script Health Check (cron job, misalnya healthcheck.php)

<?php
// Jalankan via cron setiap 10 detik
// * * * * * php /var/www/html/healthcheck.php

$mysqli = new mysqli("127.0.0.1", "root", "password", "clusterdb");

// Ambil semua node
$res = $mysqli->query("SELECT * FROM db_nodes");
while ($node = $res->fetch_assoc()) {
    $conn = @new mysqli($node['host'], $node['user'], $node['pass'], $node['dbname']);
    
    if ($conn->connect_error || !$conn->ping()) {
        // Tandai DOWN
        $mysqli->query("UPDATE db_nodes SET status='down', last_check=NOW() WHERE id={$node['id']}");
        echo "❌ {$node['role']} {$node['host']} DOWN\n";
    } else {
        // Tandai UP
        $mysqli->query("UPDATE db_nodes SET status='up', last_check=NOW() WHERE id={$node['id']}");
        echo "✅ {$node['role']} {$node['host']} UP\n";
    }
    $conn->close();
}
?>

Cluster Manager (gunakan hasil health check)

<?php
class DatabaseCluster {
    private $master;
    private $slaves = [];
    private $slaveIndex = 0;

    public function __construct() {
        $this->loadNodes();
    }

    // Ambil node dari tabel db_nodes yang sehat
    private function loadNodes() {
        $db = new mysqli("127.0.0.1", "root", "password", "clusterdb");

        // Master
        $res = $db->query("SELECT * FROM db_nodes WHERE role='master' AND status='up' LIMIT 1");
        if ($res->num_rows > 0) {
            $node = $res->fetch_assoc();
            $this->master = new mysqli($node['host'], $node['user'], $node['pass'], $node['dbname']);
        } else {
            // Failover: pilih slave sehat sebagai master
            $res2 = $db->query("SELECT * FROM db_nodes WHERE role='slave' AND status='up' LIMIT 1");
            if ($res2->num_rows > 0) {
                $node = $res2->fetch_assoc();
                $this->master = new mysqli($node['host'], $node['user'], $node['pass'], $node['dbname']);
                echo "⚠️ Master mati, slave {$node['host']} dipromosikan sementara.\n";
            } else {
                die("❌ Tidak ada node sehat untuk dijadikan master.");
            }
        }

        // Slaves
        $res = $db->query("SELECT * FROM db_nodes WHERE role='slave' AND status='up'");
        while ($node = $res->fetch_assoc()) {
            $this->slaves[] = new mysqli($node['host'], $node['user'], $node['pass'], $node['dbname']);
        }
    }

    // Round robin slave
    private function getSlave() {
        if (count($this->slaves) == 0) return $this->master;
        $conn = $this->slaves[$this->slaveIndex];
        $this->slaveIndex = ($this->slaveIndex + 1) % count($this->slaves);
        return $conn;
    }

    // Query SELECT → slave (atau master fallback)
    public function queryRead($sql) {
        $conn = $this->getSlave();
        $res = $conn->query($sql);
        if (!$res) die("Error SELECT: " . $conn->error);
        return $res;
    }

    // Query WRITE → master
    public function queryWrite($sql) {
        $res = $this->master->query($sql);
        if (!$res) die("Error WRITE: " . $this->master->error);
        return $res;
    }
}
?>

Contoh Penggunaan

<?php
require_once "DatabaseCluster.php";

$db = new DatabaseCluster();

// WRITE → ke master
$db->queryWrite("INSERT INTO products (name, price) VALUES ('Produk HealthCheck', 123456)");

// READ → ke slave yang sehat (round robin)
$res = $db->queryRead("SELECT * FROM products LIMIT 5");
while ($row = $res->fetch_assoc()) {
    echo $row['name'] . " - Rp " . $row['price'] . "<br>";
}
?>

Penjelasan

  • Cron job health check: cek koneksi tiap node (ping) → update status up/down di DB.
  • Cluster Manager: hanya pakai node yang statusnya up.
  • Failover otomatis: kalau master down, pilih salah satu slave sehat jadi master sementara.
  • Load balancing: query SELECT didistribusikan antar slave yang sehat.
  • Bisa diperluas dengan: Logging ke file (untuk audit), kirim notifikasi (email/Telegram) kalau master down, auto reconfig db_nodes kalau slave dipromosikan jadi master permanen.

Related posts:

  1. Database Cluster dengan Arsitektur Master-Slave
  2. Sharding Database: Cara Efektif Skala Data Besar untuk Performa & Skalabilitas Maksimal