WordPress + MySQL Replication

When you need replication

MySQL replication keeps a copy of your database on a second server. Writes go to the primary. Reads can be served from either.

WordPress benefits from replication in three scenarios:

1. High read traffic — your site has 10x more SELECT queries than INSERT/UPDATE.

2. Backups without disrupting production — back up from the replica without locking the primary.

3. Disaster recovery — if primary dies, promote replica.

For most WordPress sites under 1M monthly visits, replication is overkill. For sites with 10M+ visits or strict SLA, it's essential.

The architecture

[Visitors] → [Load Balancer]
                    │
            ┌───────┴───────┐
            │               │
       [Web Server 1]  [Web Server 2]
            │               │
            └───────┬───────┘
                    │
            ┌───────┴───────┐
            │               │
       [MySQL Primary] ──→ [MySQL Replica]
        (writes)            (reads only)

Setting up basic replication

Step 1 — On the primary

/etc/mysql/my.cnf:

[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW
expire_logs_days = 7
binlog_do_db = your_wp_database

Restart MySQL.

Create replication user:

CREATE USER 'replica'@'%' IDENTIFIED BY 'StrongPassword123!';
GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%';
FLUSH PRIVILEGES;

Lock and note position:

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
-- Record File and Position

In another terminal:

mysqldump --single-transaction --master-data your_wp_database > primary-dump.sql

Unlock:

UNLOCK TABLES;

Step 2 — On the replica

/etc/mysql/my.cnf:

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

Restart. Import dump:

mysql your_wp_database < primary-dump.sql

Configure replication:

CHANGE MASTER TO
  MASTER_HOST='primary.yoursite.com',
  MASTER_USER='replica',
  MASTER_PASSWORD='StrongPassword123!',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=12345;

START SLAVE;

Step 3 — Verify

SHOW SLAVE STATUS\G

Look for: - Slave_IO_Running: Yes - Slave_SQL_Running: Yes - Seconds_Behind_Master: 0

Pointing WordPress at the replica

Option 1: HyperDB

Free Automattic plugin. Configure wp-content/db-config.php:

$wpdb->add_database(array(
    'host'     => 'primary.yoursite.com',
    'user'     => DB_USER,
    'password' => DB_PASSWORD,
    'name'     => DB_NAME,
    'write'    => 1,
    'read'     => 1,
));

$wpdb->add_database(array(
    'host'     => 'replica.yoursite.com',
    'user'     => DB_USER,
    'password' => DB_PASSWORD,
    'name'     => DB_NAME,
    'write'    => 0,
    'read'     => 2,
));

Drop wp-content/db.php from HyperDB.

Option 2: ProxySQL

Web servers connect to ProxySQL. ProxySQL routes:

INSERTs → primary
SELECTs → replica

Zero WordPress code changes.

Gotchas

Replication lag

Replica is always slightly behind. HyperDB pins user sessions to primary briefly after writes.

Schema changes

ALTER TABLE on primary replicates. For large tables, causes minutes of lag. Use pt-online-schema-change (Percona Toolkit).

Backups

Use replica for mysqldump:

mysql -e "STOP SLAVE; FLUSH TABLES WITH READ LOCK;"
mysqldump --all-databases > /backup/full-dump.sql
mysql -e "UNLOCK TABLES; START SLAVE;"

Or mariabackup (no lock):

mariabackup --backup --target-dir=/backup/

Promoting replica to primary

If primary fails:

STOP SLAVE;
RESET MASTER;
SET GLOBAL read_only = 0;

Update WordPress to point to replica.

Monitoring replication health

Critical metric: Seconds_Behind_Master.

*/5 * * * * /var/www/yoursite/scripts/check-replication.sh

#!/bin/bash
LAG=$(mysql -BNe "SHOW SLAVE STATUS\G" | grep "Seconds_Behind_Master:" | awk '{print $2}')
if [ "$LAG" -gt 30 ]; then
    echo "MySQL lag: $LAG seconds" | mail -s "REPLICATION ALERT" admin@yoursite.com
fi

When NOT to use replication

Small WordPress site (< 100K monthly visits): complexity not worth it
Sites without read/write split logic: paying for replica with no benefit
Sites with strict consistency: use synchronous replication (Galera Cluster)

Alternative: managed replication

AWS RDS, Azure Database, Google Cloud SQL offer one-click read replicas. For most WordPress operations, much simpler than self-managing.

Cost higher but operational complexity drops to near zero.

Common mistakes

Replicating without binlog_format = ROW — STATEMENT format breaks
Not setting read_only on replica — accidental writes break replication
Forgetting to install db.php during HyperDB setup — silently disables it
No monitoring of lag — replica falls behind, no one notices

When to call a specialist

We've set up dozens of WordPress replication topologies. For most sites, we recommend managed RDS replicas — lower TCO.

Database scaling consultation. For broader infrastructure see emergency support.