Replication Basics

Replication Basics

Database replication continuously copies changes from a primary (master) server to one or more replica (slave) servers. It serves two purposes: high availability (if the primary fails, a replica can take over) and read scaling (direct read-heavy queries to replicas to reduce load on the primary). Replication is asynchronous by default — replicas may lag a few seconds behind the primary. This means replicas are not suitable for reads that require immediate consistency after writes.

What is replication?

MySQL replication flow:
  Primary server
    └── Binary log (records all changes)
         ↓ (I/O thread)
  Replica server
    └── Relay log (receives binary log events)
         ↓ (SQL thread applies events)
    └── Data directory (stays in sync with primary)

Lag = time between write on primary and apply on replica
     (milliseconds when healthy, grows if replica falls behind)

MySQL replication setup

# STEP 1: On the primary server, enable binary logging:
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Primary server mysqld.cnf additions

[mysqld]
server-id = 1            # Must be unique across all servers in the replication group
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW      # ROW format is most reliable

# Create a replication user:
sudo mysql
CREATE USER 'replicator'@'192.168.1.%' IDENTIFIED BY 'ReplPass!42';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'192.168.1.%';
FLUSH PRIVILEGES;

# Get the current binary log position:
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;    -- note the File and Position values
# Take a dump of the data, then unlock:
UNLOCK TABLES;

# STEP 2: On the replica server, configure and start replication:
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
# server-id = 2     ← different ID from primary

sudo mysql
CHANGE MASTER TO
  MASTER_HOST = '192.168.1.10',
  MASTER_USER = 'replicator',
  MASTER_PASSWORD = 'ReplPass!42',
  MASTER_LOG_FILE = 'mysql-bin.000001',    -- from SHOW MASTER STATUS
  MASTER_LOG_POS = 154;                    -- from SHOW MASTER STATUS
START SLAVE;

PostgreSQL streaming replication

# PostgreSQL uses streaming replication (more modern than MySQL's approach)
# STEP 1: On primary, in postgresql.conf:
# wal_level = replica
# max_wal_senders = 3
# wal_keep_size = 64MB

# Create replication role:
sudo -u postgres psql -c "CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'ReplPass!42';"

# In pg_hba.conf:
# host replication replicator 192.168.1.11/32 scram-sha-256

# STEP 2: On replica, take base backup from primary:
sudo systemctl stop postgresql
sudo -u postgres rm -rf /var/lib/postgresql/16/main/*
sudo -u postgres pg_basebackup -h 192.168.1.10 -U replicator -D /var/lib/postgresql/16/main -Xs -P

# Create standby.signal file to mark this as a replica:
sudo -u postgres touch /var/lib/postgresql/16/main/standby.signal
sudo systemctl start postgresql

Monitoring replication lag

# MySQL: check replication status on replica:
SHOW SLAVE STATUS\G

Key fields in SHOW SLAVE STATUS

Slave_IO_Running: Yes       ← IO thread running (connected to primary)
Slave_SQL_Running: Yes      ← SQL thread applying changes
Seconds_Behind_Master: 0    ← replication lag (0 = fully caught up)
Last_Error:                 ← empty = no errors

# PostgreSQL: check lag on primary (how far behind each replica is):
SELECT client_addr, write_lag, replay_lag FROM pg_stat_replication;

Conclusion

Replication is not a backup — if you accidentally drop a table, that DROP is immediately replicated to all replicas. Replication provides availability and read scaling, not data protection. Set up monitoring for Seconds_Behind_Master (MySQL) or replay_lag (PostgreSQL) — growing lag indicates the replica cannot keep up with the write rate and needs investigation. For high-availability failover, tools like Orchestrator (MySQL) or Patroni (PostgreSQL) automate the promotion of a replica to primary when the primary fails.