Database_replication

Chapter 73: Database Replication

Comprehensive Guide to Database Replication

---

73.1 MySQL/MariaDB Replication

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

# /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

[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

# 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

# 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

Streaming Replication

# 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

-- 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

# 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

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

---

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        |  |
|    +----------------------------------------------------------+  |
|                                                                   |
+------------------------------------------------------------------+