Database Replication (MySQL/MariaDB)

Database replication keeps a secondary database instance continuously synchronized with a primary. For WordPress VPS setups, replication is most commonly used for read scaling, safer backups (dump from replica), and failover planning. Replication operates at the transaction/log level (binlog events), which is fundamentally safer than copying raw database files with tools like rsync.

Quick Summary

Replication workflow: enable binlogging on the primary, create a replication user, seed the replica with an initial dump, then start replication and verify the replica is caught up.

warning

Replication is not a backup. A bad write (DROP/DELETE) on the primary will replicate to the replica. Keep independent backups.

How Replication Works

Core Concepts

Term	Meaning	Why it matters
Primary (source)	The main writable DB	Your source of truth
Replica	The DB that replays changes	Used for reads/backups/failover
Binlog	Log of changes on primary	Replication is driven by binlog events
Relay log	Replica-local log of events	Buffer between primary and replica
Lag	How far replica is behind	Large lag reduces usefulness

Prerequisites

• Two servers (or two DB instances) with network connectivity.
• Stable identifiers and firewall rules (allow DB traffic only between hosts).
• MySQL/MariaDB admin access.
• Time window to seed initial data.

caution

Replication setup is version- and distro-specific. Treat this page as a conceptual runbook and verify exact commands for your MySQL/MariaDB version.

Setup Overview (Conceptual Steps)

Step 1: Configure the primary

Enable binlogging and set a unique server-id.

/etc/mysql/mysql.conf.d/mysqld.cnf (example)

[mysqld]
server-id=1
log_bin=mysql-bin
binlog_format=ROW

Restart the database service after config changes (restart commands vary by distro).

Step 2: Create a replication user

create-replication-user.sql

CREATE USER 'repl_user'@'REPLICA_IP' IDENTIFIED BY 'REPLACE_ME_WITH_A_STRONG_PASSWORD';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'REPLICA_IP';
FLUSH PRIVILEGES;

Expected output:

example-output-create-repl-user.txt

(no output on success)

Step 3: Record the current binlog position

show-master-status.sql

SHOW MASTER STATUS;

Expected output (example):

example-output-show-master-status.txt

+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000123 |  4567890 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

Use case: The replica needs a starting point.

Step 4: Seed the replica (initial full copy)

Replication replays new changes. The replica must start with an initial copy of the data.

Dump the primary:

seed-dump-from-primary.sh

mysqldump -u root -p --single-transaction --quick --all-databases > seed.sql

Expected output:

example-output-seed-dump.txt

(no output on success)

Transfer and import to the replica (transfer method varies).

Step 5: Configure and start replication on the replica

Set a unique replica server id:

/etc/mysql/mysql.conf.d/mysqld.cnf (replica example)

[mysqld]
server-id=2

Configure the connection and start replication.

MariaDB / Older MySQL

configure-replica-mariadb.sql

CHANGE MASTER TO
  MASTER_HOST='PRIMARY_IP',
  MASTER_USER='repl_user',
  MASTER_PASSWORD='REPLACE_ME',
  MASTER_LOG_FILE='mysql-bin.000123',
  MASTER_LOG_POS=4567890;

START SLAVE;

MySQL (Newer Terms)

configure-replica-mysql.sql

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='PRIMARY_IP',
  SOURCE_USER='repl_user',
  SOURCE_PASSWORD='REPLACE_ME',
  SOURCE_LOG_FILE='mysql-bin.000123',
  SOURCE_LOG_POS=4567890;

START REPLICA;

Expected output:

example-output-start-replication.txt

(no output on success)

Step 6: Verify replication health

MariaDB / Older MySQL

verify-replication-mariadb.sql

SHOW SLAVE STATUS\G

Expected output (key fields):

example-output-show-slave-status.txt

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0

MySQL (Newer Terms)

verify-replication-mysql.sql

SHOW REPLICA STATUS\G

Expected output (key fields):

example-output-show-replica-status.txt

Replica_IO_Running: Yes
Replica_SQL_Running: Yes
Seconds_Behind_Source: 0

Use case: Confirm the replica is actively receiving and applying changes.

WordPress VPS Use Cases

Scenario	How replication helps	Recommended pattern
Safer backups	Dump from replica to reduce primary load	Dump replica during off-peak
Read scaling	Serve reads from replica (advanced)	Requires app-level routing
Failover planning	Replica can be promoted	Requires tested DR runbooks

Troubleshooting

Symptom	Likely cause	What to check
Replica lag increases	Disk/CPU bottleneck or large writes	Seconds_Behind_*, disk I/O, long transactions
I/O thread not running	Network/firewall or auth	Host reachability, replication user grants
SQL thread stopped	Replication error applying events	Error fields in status output

Best Practices

• Restrict DB port exposure to only known IPs.
• Use a dedicated replication user with only replication privileges.
• Monitor lag and set alerts.
• Keep independent backups (replication replicates mistakes).

Cheat Sheet

replication-cheat-sheet.sql

-- primary
SHOW MASTER STATUS;

-- replica (MariaDB/older)
SHOW SLAVE STATUS\G

-- replica (newer MySQL)
SHOW REPLICA STATUS\G

What's Next

• Backup and Migration
• Performance Tuning Basics
• User Privileges