MySQL Replication: Master-Slave Setup and Handling in PHP

MySQL Replication

Replication copies data from a master (source) to one or more replica (slave) servers. It enables horizontal read scaling, high availability, and zero-downtime backups.

Architecture:
┌──────────────┐   Binary Log    ┌──────────────────┐
│    MASTER    │ ─────────────► │    REPLICA 1     │
│  (Writes)    │                 │  (Reads, Backup) │
│  Port 3306   │ ─────────────► ├──────────────────┤
└──────────────┘                 │    REPLICA 2     │
                                 │  (Reads, DR)     │
                                 └──────────────────┘

Master: handles all INSERT/UPDATE/DELETE
Replicas: handle SELECT queries (read scaling)
           offload reporting queries
           hot standby for failover

How Replication Works

-- Master: records every change to binary log
-- my.cnf on master:
-- [mysqld]
-- server-id = 1
-- log-bin   = mysql-bin
-- binlog-do-db = ezycoders    -- replicate this DB only

-- Create replication user on master
CREATE USER 'replicator'@'%' IDENTIFIED BY 'secure_password';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%';

-- Get master status (note File and Position)
SHOW MASTER STATUS;
-- +------------------+----------+--------------+
-- | File             | Position | Binlog_Do_DB |
-- +------------------+----------+--------------+
-- | mysql-bin.000001 |      154 | ezycoders    |

-- On REPLICA:
CHANGE MASTER TO
    MASTER_HOST     = '192.168.1.100',
    MASTER_USER     = 'replicator',
    MASTER_PASSWORD = 'secure_password',
    MASTER_LOG_FILE = 'mysql-bin.000001',
    MASTER_LOG_POS  = 154;

START SLAVE;
SHOW SLAVE STATUS\G
-- Seconds_Behind_Master: 0  ← in sync
-- Slave_IO_Running:  Yes
-- Slave_SQL_Running: Yes

Handling Replication in Application Code

<?php
// Route reads to replica, writes to master
class DatabaseManager {
    private PDO $master;
    private PDO $replica;

    public function write(): PDO {
        return $this->master;   // INSERT UPDATE DELETE
    }

    public function read(): PDO {
        return $this->replica;  // SELECT (possibly slightly stale)
    }
}

// After a critical write, read from master to avoid stale read
function createPostAndReturn(array $data): array {
    $db->write()->prepare('INSERT INTO posts ...')->execute($data);
    $id = $db->write()->lastInsertId();

    // Read from MASTER (not replica) to guarantee seeing our own write
    return $db->write()->query("SELECT * FROM posts WHERE id = $id")->fetch();
}