Configure MySQL Primary-Replica Replication with GTID for WordPress Read Scaling

Home » Linux » Configure MySQL Primary-Replica Replication with GTID for WordPress Read Scaling
February 10, 2023 Linux, MySQL
Configure MySQL Primary-Replica Replication with GTID for WordPress Read Scaling

MySQL replication creates one or more read replicas that receive a continuous stream of change events from the primary server, maintaining an identical copy of the database with a replication lag typically under one second on a well-tuned network. For high-traffic WordPress sites, routing all SELECT queries to a read replica while reserving the primary for writes reduces primary server load by 60–80 percent on read-heavy blogs and content sites. WordPress’s $wpdb class does not natively support read/write splitting — the HyperDB drop-in or the LudicrousDB fork add this capability by transparently routing queries to the correct server based on whether they are reads or writes. MySQL binary log (binlog) is the replication mechanism: the primary writes every data-changing statement or row image to the binlog, and replicas connect, read new events from the binlog, and apply them to their own copy of the data. Row-based replication (binlog_format = ROW) replicates the actual changed rows rather than the SQL statements, making it safer than statement-based replication for queries that use non-deterministic functions like NOW() or RAND(). GTID (Global Transaction Identifier) replication simplifies failover — the replica can automatically find its position in the primary’s binlog using a transaction ID rather than a binary log file name and offset. Monitoring replication lag with SHOW REPLICA STATUS and alerting when Seconds_Behind_Source exceeds a threshold prevents stale reads from reaching users during periods of high write load. Read replicas also serve as zero-impact backup sources — running mysqldump against the replica instead of the primary eliminates the dump’s I/O impact on live traffic. The MySQL backup post describes the dump process that benefits directly from using a replica as the backup source. The SSH hardening post covers the server-level security that should protect the replication channel — replicas should connect to the primary over an encrypted network or a VPN. Size the replica server identically to the primary — under-provisioning the replica causes it to fall behind during write spikes, defeating the purpose of read distribution.

Problem: Single-server WordPress MySQL installations become a bottleneck under read-heavy traffic — all SELECT, INSERT, and UPDATE queries compete for the same I/O and CPU resources, with no way to scale reads independently of writes without additional infrastructure.

Solution: Configure MySQL primary-replica replication with GTID and row-based binlog, install the HyperDB drop-in to split WordPress reads to the replica, and monitor replication lag with a cron-based health check script.

# --- On the PRIMARY MySQL server ---

# my.cnf additions (restart MySQL after editing)
# [mysqld]
# server-id          = 1
# log_bin            = /var/log/mysql/mysql-bin.log
# binlog_format      = ROW
# gtid_mode          = ON
# enforce_gtid_consistency = ON
# binlog_expire_logs_seconds = 604800   # keep 7 days of binlogs

# Create a replication user on the primary
mysql -u root -p << 'SQL'
CREATE USER 'repl'@'10.0.0.%' IDENTIFIED BY 'StrongReplicaPass!1';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.0.0.%';
FLUSH PRIVILEGES;
SQL

# Export a consistent snapshot of the primary (for initial replica setup)
mysqldump     --single-transaction     --source-data=2     --set-gtid-purged=ON     --all-databases | gzip > /tmp/primary-snapshot.sql.gz

# --- On the REPLICA MySQL server ---

# my.cnf (replica)
# server-id = 2
# read_only = ON
# log_bin   = OFF   # replicas don't need to write binlog unless chaining

# Import primary snapshot, then configure replication
mysql -u root -p << 'SQL'
CHANGE REPLICATION SOURCE TO
    SOURCE_HOST      = '10.0.0.1',
    SOURCE_USER      = 'repl',
    SOURCE_PASSWORD  = 'StrongReplicaPass!1',
    SOURCE_AUTO_POSITION = 1;  -- GTID-based, no file/position needed
START REPLICA;
SHOW REPLICA STATUS\G
SQL

# Monitor replication lag (add to cron every minute)
LAG=$(mysql -u root -p"$DB_PASS" -Nse "SHOW REPLICA STATUS\G"     | grep 'Seconds_Behind_Source' | awk '{print $2}')
[ "$LAG" -gt 30 ] && echo "ALERT: replication lag ${LAG}s" | mail -s "MySQL lag" ops@example.com

NOTE: Set read_only = ON in the replica's my.cnf to prevent accidental writes directly to the replica — without it, any direct write to the replica that is not replicated from the primary will cause a replication conflict and stop the replication thread, requiring manual intervention to resolve.

Related posts