MySQL Master-Slave Replication for WordPress Read Scaling

MySQL replication streams every write from a master (source) server to one or more replica (slave) servers in near real-time. For WordPress, this enables read scaling — sending all SELECT queries to replicas while writes go to the master — using the HyperDB drop-in or a custom db.php.

Problem: A high-traffic WordPress or WooCommerce site generates more concurrent database reads than a single MySQL server can handle — adding read replicas improves capacity but requires routing read queries away from the primary.

Solution: Set up MySQL replication with CHANGE REPLICATION SOURCE TO (MySQL 8) pointing the replica at the primary's binary log position. In WordPress, use the HyperDB drop-in to route SELECT queries to replicas and writes to the primary — configure each server with a read weight in db-config.php.

The examples below configure binary logging on the master, create a replication user, run the initial CHANGE MASTER TO on the replica, and show a minimal WordPress db.php that routes reads to a replica.

# ── MASTER SERVER (my.cnf) ──
[mysqld]
server-id        = 1
log_bin          = /var/log/mysql/mysql-bin.log
binlog_format    = ROW          # safest format for WordPress
expire_logs_days = 7
max_binlog_size  = 100M
# Restrict binary logging to the WordPress database
binlog_do_db     = helloadmin

# ── REPLICA SERVER (my.cnf) ──
[mysqld]
server-id        = 2
relay_log        = /var/log/mysql/mysql-relay-bin.log
read_only        = ON           # prevents accidental writes to replica
log_slave_updates = ON          # allows chaining replicas

# Restart both MySQL instances after editing my.cnf

Create the replication user and start replication:

-- ── ON THE MASTER ──
-- Create a dedicated replication user (principle of least privilege)
CREATE USER 'replicator'@'REPLICA_IP' IDENTIFIED WITH mysql_native_password BY 'StrongPassword!';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'REPLICA_IP';
FLUSH PRIVILEGES;

-- Take a consistent snapshot
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
-- Note: File = 'mysql-bin.000001', Position = 154
-- Export the DB while the lock is held:
-- mysqldump -u root helloadmin > helloadmin_snapshot.sql
UNLOCK TABLES;

-- ── ON THE REPLICA ──
-- Import the snapshot first:
-- mysql -u root helloadmin < helloadmin_snapshot.sql

CHANGE MASTER TO
  MASTER_HOST      = 'MASTER_IP',
  MASTER_USER      = 'replicator',
  MASTER_PASSWORD  = 'StrongPassword!',
  MASTER_LOG_FILE  = 'mysql-bin.000001',
  MASTER_LOG_POS   = 154;

START SLAVE;

-- Verify replication is running:
SHOW SLAVE STATUS\G
-- Look for: Slave_IO_Running: Yes, Slave_SQL_Running: Yes, Seconds_Behind_Master: 0

Point WordPress reads to the replica with a minimal db.php:

<?php
// wp-content/db.php — WordPress loads this as a DB drop-in
// Routes SELECT queries to the replica, writes to the master

define( 'DB_REPLICA_HOST', 'REPLICA_IP' );

class WP_DB_With_Replica extends wpdb {
    protected function db_connect( $allow_bail = true ) {
        parent::db_connect( $allow_bail );
        // Open a second connection to the replica
        $this->replica = new mysqli( DB_REPLICA_HOST, DB_USER, DB_PASSWORD, DB_NAME );
    }

    public function query( $query ) {
        // Send SELECTs to the replica, everything else to master
        if ( $this->replica && preg_match( '/^\s*SELECT\s/i', $query ) ) {
            $this->dbh = $this->replica;
        } else {
            $this->dbh = $this->dbhmaster ?? $this->dbh;
        }
        return parent::query( $query );
    }
}

// Override the global $wpdb with our subclass
$wpdb = new WP_DB_With_Replica( DB_USER, DB_PASSWORD, DB_NAME, DB_HOST );

NOTE: For production use, prefer the HyperDB drop-in (Automattic/hyperdb on GitHub) over a custom db.php — it handles connection failures, replica lag detection, and multiple replicas out of the box.

Cancel reply