Database Replication คือ — คู่มือ Replication

Database Replication

Database Replication คือ Master-Slave Multi-Master Synchronous Asynchronous WAL Binary Log Failover HA Read Scaling Disaster Recovery

เนื้อหาเกี่ยวข้อง — ดูเพิ่มเติมเรื่อง SASE Security Capacity Planning

Type	Write	Read	Consistency	Use Case
Master-Slave	Master only	Master + Slaves	Eventual (Async)	Read-heavy, Simple HA
Multi-Master	All nodes	All nodes	Eventual/Strong	Multi-region Write
Synchronous	Primary	Primary + Standby	Strong	Zero data loss
Semi-sync	Primary	Primary + Replicas	Near-strong	Balance Safety/Speed
Cascading	Primary	Primary + Chain	Eventual	Many replicas

Monitoring & Failover

# === Replication Monitoring & Automated Failover ===



@dataclass

class MonitorMetric:

    metric: str

    query: str

    threshold: str

    action: str



monitoring = [

    MonitorMetric("Replication Lag (MySQL)",

        "SHOW SLAVE STATUS → Seconds_Behind_Master",

        "> 5 seconds → Warning, > 30s → Critical",

        "Check slow queries on Slave, Network, Disk I/O"),

    MonitorMetric("Replication Lag (PostgreSQL)",

        "SELECT extract(epoch from now()-pg_last_xact_replay_timestamp())",

        "> 5 seconds → Warning, > 30s → Critical",

        "Check WAL throughput, Network, Slave resources"),

    MonitorMetric("Slave IO/SQL Thread",

        "SHOW SLAVE STATUS → Slave_IO_Running, Slave_SQL_Running",

        "No → Critical",

        "Check connectivity, Error log, SKIP counter"),

    MonitorMetric("WAL Sender State (PG)",

        "SELECT state FROM pg_stat_replication",

        "NULL (disconnected) → Critical",

        "Check network, Standby status, Slot"),

    MonitorMetric("Replication Slot Lag",

        "SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)",

        "> 1GB → Warning",

        "Slot retained WAL ใช้ Disk มาก ลบ Slot ถ้า Replica ตาย"),

]



@dataclass

class FailoverTool:

    tool: str

    database: str

    method: str

    rpo_rto: str



failover_tools = [

    FailoverTool("Orchestrator",

        "MySQL",

        "Auto-detect failure + Promote best Slave + Reroute traffic",

        "RPO: ~0s (GTID), RTO: 10-30s"),

    FailoverTool("Patroni + etcd",

        "PostgreSQL",

        "Leader election via etcd + Auto-promote Standby",

        "RPO: 0s (sync) / ~1s (async), RTO: 10-30s"),

    FailoverTool("PgBouncer",

        "PostgreSQL",

        "Connection pooling + Route to new Primary",

        "ลด Connection overhead + Seamless failover"),

    FailoverTool("ProxySQL",

        "MySQL",

        "Query routing + Read/Write split + Auto-failover",

        "Application ไม่ต้องเปลี่ยน Connection String"),

]



print("=== Monitoring ===")

for m in monitoring:

    print(f"  [{m.metric}]")

    print(f"    Query: {m.query}")

    print(f"    Threshold: {m.threshold}")



print("\n=== Failover Tools ===")

for f in failover_tools:

    print(f"  [{f.tool}] {f.database}")

    print(f"    Method: {f.method}")

    print(f"    RPO/RTO: {f.rpo_rto}")

เคล็ดลับ

GTID: ใช้ GTID (MySQL) สำหรับ Failover ง่ายกว่า Position-based
Slot: ใช้ Replication Slot (PostgreSQL) ป้องกัน WAL ถูกลบ
Monitor: ตั้ง Alert Replication Lag > 5 วินาที เสมอ
Failover: ใช้ Orchestrator/Patroni Automated Failover
Test: ทดสอบ Failover เป็นระยะ Disaster Recovery Drill

Database Replication คืออะไร

สำเนาข้อมูลอัตโนมัติ HA Read Scaling DR Synchronous Asynchronous Semi-sync Master-Slave Multi-Master WAL Binary Log Failover

แนะนำเพิ่มเติม — ดูสัญญาณเทรดที่ XM Signal

เนื้อหาเกี่ยวข้อง — บทความที่เกี่ยวข้อง: Terraform State 12 Factor App

เนื้อหาเกี่ยวข้อง — บทความที่เกี่ยวข้อง: Redis Streams Service Mesh Setup — คู่มือฉบับสมบูรณ์ 2026