Skip to content
Flowchart Learning

Database Replication

Chapter 73: Database Replication

Section titled “Chapter 73: Database Replication”

Comprehensive Guide to Database Replication

Section titled “Comprehensive Guide to Database Replication”

Why This Matters in DevOps/SRE

Section titled “Why This Matters in DevOps/SRE”

Database replication is fundamental to building high-availability systems. It enables read scaling, provides failover capability, and ensures data redundancy. As a DevOps/SRE, you’ll configure replication, monitor lag, and handle failover scenarios - critical skills for maintaining uptime.

┌─────────────────────────────────────────────────────────────────────────────┐
│                    DATABASE REPLICATION IN HIGH AVAILABILITY                  │
├─────────────────────────────────────────────────────────────────────────────┤
│                                                                             │
│  ┌─────────────────────────────────────────────────────────────────────┐    │
│  │                    REPLICATION TOPOLOGIES                             │    │
│  │                                                                       │    │
│  │   Master-Slave:          │   Master-Master:                         │    │
│  │   ┌──────┐               │   ┌──────┐       ┌──────┐             │    │
│  │   │Master│──► Slave      │   │Master1│◄────►│Master2│             │    │
│  │   │      │──► Slave      │   └──────┘       └──────┘             │    │
│  │   └──────┘               │   (active-active)                     │    │
│  │   (read scaling)         │                                         │    │
│  │                                                                       │    │
│  └─────────────────────────────────────────────────────────────────────┘    │
│                                                                             │
│  ┌─────────────────────────────────────────────────────────────────────┐    │
│  │                    FAILOVER SCENARIO                                 │    │
│  │                                                                       │    │
│  │   Primary DB fails                                                    │    │
│  │        │                                                              │    │
│  │        ▼                                                              │    │
│  │   Detect failure (health check)                                      │    │
│  │        │                                                              │    │
│  │        ▼                                                              │    │
│  │   Promote replica to primary                                          │    │
│  │        │                                                              │    │
│  │        ▼                                                              │    │
│  │   Update application connection string                               │    │
│  │        │                                                              │    │
│  │        ▼                                                              │    │
│  │   Traffic flows to new primary                                        │    │
│  │                                                                       │    │
│  └─────────────────────────────────────────────────────────────────────┘    │
│                                                                             │
│  ┌─────────────────────────────────────────────────────────────────────┐    │
│  │                    MONITORING METRICS                                 │    │
│  │                                                                       │    │
│  │   • Replication lag (seconds behind master)                         │    │
│  │   • IO thread status (Running/Stopped)                              │    │
│  │   • SQL thread status                                               │    │
│  │   • Binlog position                                                 │    │
│  │                                                                       │    │
│  └─────────────────────────────────────────────────────────────────────┘    │
│                                                                             │
└─────────────────────────────────────────────────────────────────────────────┘

73.1 MySQL/MariaDB Replication

Section titled “73.1 MySQL/MariaDB Replication”

Replication Types

Section titled “Replication Types”
                 MySQL Replication Types
+------------------------------------------------------------------+
|                                                                   |
|    Master-Slave:                                                   |
|    +----------------------------------------------------------+   |
|    | • One master, multiple slaves                            |   |
|    | • Read scaling                                           |   |
|    | • Backup offloading                                      |   |
|    | • Asynchronous                                          |   |
|    +----------------------------------------------------------+   |
|                                                                   |
|    Master-Master:                                                 |
|    +----------------------------------------------------------+   |
|    | • Two masters active                                     |   |
|    | • Write to either                                       |   |
|    | • Auto-increment offset for conflict avoidance          |   |
|    +----------------------------------------------------------+   |
|                                                                   |
|    Replication Formats:                                           |
|    +----------------------------------------------------------+   |
|    | Statement-based | SQL statements replicated              |   |
|    | Row-based     | Rows changes replicated                |   |
|    | Mixed         | Auto-select based on statement         |   |
|    +----------------------------------------------------------+   |
|                                                                   |
+------------------------------------------------------------------+

Master Configuration

Section titled “Master Configuration”
Terminal window
# /etc/mysql/my.cnf or /etc/mysql/mariadb.conf.d/server.cnf
[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-do-db = myapp          # Replicate specific database
binlog-ignore-db = mysql      # Ignore system database
expire_logs_days = 7
max_binlog_size = 100M


# For GTID (recommended)
gtid-mode = ON
enforce-gtid-consistency = ON


# Create replication user
CREATE USER 'repl'@'%' IDENTIFIED BY 'repl_password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;

Slave Configuration

Section titled “Slave Configuration”
Terminal window
[mysqld]
server-id = 2
relay-log = relay-bin
relay-log-index = relay-bin.index
read-only = 1                   # Prevent writes on slave
log-bin = mysql-bin            # For cascading replication


# For GTID
gtid-mode = ON
enforce-gtid-consistency = ON


# Skip errors (optional)
# skip-slave-start = ON
# slave-skip-errors = 1062,1050

Setting Up Replication

Section titled “Setting Up Replication”
Terminal window
# On master - get binlog position
SHOW MASTER STATUS;
# Note: File and Position values


# Or for GTID
SHOW MASTER STATUS\G


# On slave - configure master
CHANGE MASTER TO
    MASTER_HOST='master_host',
    MASTER_USER='repl',
    MASTER_PASSWORD='repl_password',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=1234;


# For GTID
CHANGE MASTER TO
    MASTER_HOST='master_host',
    MASTER_USER='repl',
    MASTER_PASSWORD='repl_password',
    MASTER_AUTO_POSITION=1;


# Start replication
START SLAVE;


# Check status
SHOW SLAVE STATUS\G


# Stop replication
STOP SLAVE;
RESET SLAVE ALL;

Monitoring Replication

Section titled “Monitoring Replication”
Terminal window
# Check slave status
SHOW SLAVE STATUS\G


# Key metrics:
# Slave_IO_Running: Yes
# Slave_SQL_Running: Yes
# Seconds_Behind_Master: 0
# Last_IO_Errno / Last_SQL_Errno


# On master
SHOW PROCESSLIST\G
SHOW MASTER STATUS;


# Replication threads
SHOW PROCESSLIST;
-- Should show "Binlog Dump" on master


# Skip failed transaction
STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE;

73.2 PostgreSQL Replication

Section titled “73.2 PostgreSQL Replication”

Streaming Replication

Section titled “Streaming Replication”
Terminal window
# On master - postgresql.conf
wal_level = replica
max_wal_senders = 3
max_replication_slots = 3
wal_keep_size = 1GB


# Create replication user
CREATE USER replicator WITH REPLICATION PASSWORD 'rep_password';


# pg_hba.conf - allow replication
host replication replicator 10.0.0.0/24 md5


# Create base backup on replica
pg_basebackup -h master -D /var/lib/postgresql/14/main -U replicator -P -Xs


# Or with replication slot
pg_basebackup -h master -D /var/lib/postgresql/14/main -U replicator -P --slot=replica_slot -Xs


# On replica - postgresql.conf
primary_conninfo = 'host=master port=5432 user=replicator password=rep_password'
hot_standby = on


# Create recovery signal
touch /var/lib/postgresql/14/main/standby.signal

Replication Monitoring

Section titled “Replication Monitoring”
Terminal window
-- On master
SELECT * FROM pg_stat_replication;
SELECT * FROM pg_replication_slots;


-- On replica
SELECT * FROM pg_stat_wal_receiver;
SELECT * FROM pg_stat_replication;


-- Check lag
SELECT now() - pg_last_xact_replay_timestamp() AS replication_lag;

Logical Replication

Section titled “Logical Replication”
Terminal window
# On master - create publication
CREATE PUBLICATION myapp_pub FOR ALL TABLES;


# On replica - create subscription
CREATE SUBSCRIPTION myapp_sub
    CONNECTION 'host=master port=5432 dbname=myapp user=replicator'
    PUBLICATION myapp_pub;


# Monitor
SELECT * FROM pg_stat_subscription;

73.3 Interview Questions

Section titled “73.3 Interview Questions”

Basic Questions

Section titled “Basic Questions”

  1. What is database replication?

    • Copying data from one database to another

  2. What is master-slave replication?

    • One master for writes, slaves for reads

  3. What is the difference between statement-based and row-based replication?

    • Statement: SQL statements replicated; Row: actual row changes

  4. What is GTID?

    • Global Transaction ID for easier replication management

  5. What is streaming replication in PostgreSQL?

    • WAL sent to replica in real-time

Common Mistakes & Anti-Patterns

Section titled “Common Mistakes & Anti-Patterns”

1. Not Monitoring Replication Lag

Section titled “1. Not Monitoring Replication Lag”

❌ WRONG: No visibility into lag

-- Not checking lag
SHOW SLAVE STATUS;  -- Not monitoring!

✅ CORRECT: Monitor lag continuously

-- MySQL
SHOW SLAVE STATUS\G
-- Check: Seconds_Behind_Master


-- PostgreSQL
SELECT now() - pg_last_xact_replay_timestamp() AS replication_lag;

2. Using async replication for critical data

Section titled “2. Using async replication for critical data”

❌ WRONG: Data loss on primary failure

# my.cnf - async by default
# (no sync related settings)

✅ CORRECT: Use semi-sync for critical data

# my.cnf
plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
rpl_semi_sync_master_enabled = 1
rpl_semi_sync_slave_enabled = 1

3. Not having replication user security

Section titled “3. Not having replication user security”

❌ WRONG: Weak replication credentials

-- DON'T
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY 'password';

✅ CORRECT: Secure replication user

-- Limit to specific IPs
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.0.1.%' IDENTIFIED BY 'strong_password';

4. Promoting wrong replica

Section titled “4. Promoting wrong replica”

❌ WRONG: Manual promotion without checking position

Terminal window
# Blind promotion - might lose data!
service mysql stop
# promote replica to master

✅ CORRECT: Use proper promotion procedure

Terminal window
# 1. Stop application writes
# 2. Check replication position on all replicas
# 3. Choose most up-to-date replica
# 4. Stop replication and promote
mysql> STOP SLAVE;
mysql> RESET MASTER;
# Update application to point to new master

5. No testing of failover

Section titled “5. No testing of failover”

❌ WRONG: Never tested failover until real outage

✅ CORRECT: Regular failover drills

Terminal window
# Schedule regular failover tests
# 1. Document failover procedure
# 2. Practice in staging
# 3. Conduct game day exercises
# 4. Automate with tools like Orchestrator or Patroni

Summary

Section titled “Summary”
                 Quick Reference
+------------------------------------------------------------------+
|                                                                   |
|    MySQL Commands:                                                |
|    +----------------------------------------------------------+  |
|    | SHOW MASTER STATUS              | Master position        |  |
|    | SHOW SLAVE STATUS\G            | Slave status          |  |
|    | START/STOP SLAVE               | Control replication   |  |
|    +----------------------------------------------------------+  |
|                                                                   |
|    PostgreSQL Commands:                                           |
|    +----------------------------------------------------------+  |
|    | pg_stat_replication           | Master status         |  |
|    | pg_stat_wal_receiver         | Replica status        |  |
|    | pg_basebackup                | Create replica        |  |
|    +----------------------------------------------------------+  |
|                                                                   |
+------------------------------------------------------------------+