Implementasi Database Cluster Master Slave dengan PHP MySqli

By | 9 October 2025
1 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. Kombinasi Database Cluster Master Slave dan Sharding Database untuk Solusi Jutaan Data
  3. Contoh Implementasi Sharding Database dengan PHP & MySQLi
  4. Menampilkan Data dari Database MySQL dengan PHP: Panduan Lengkap Versi MySQLi dan PDO
  5. Insert Data ke Database MySql dengan PHP (Versi MySqli dan PDO)
  6. Koneksi PHP ke Database MySQL (MySQLi)
  7. CRUD PHP MySQLi OOP Prepared Statement
  8. CRUD PHP MySQL Menggunakan MySQLi Object Oriented (OOP)
  9. Sharding Database: Cara Efektif Skala Data Besar untuk Performa & Skalabilitas Maksimal
  10. Edit Data di PHP dan MySQL: Panduan Lengkap dengan MySQLi & PDO

One thought on “Implementasi Database Cluster Master Slave dengan PHP MySqli”

  1. Pingback: Kombinasi Database Cluster Master Slave dan Sharding Database untuk Solusi Jutaan Data - Tutorial

Comments are closed.