Database Replication Architectures: Master-Slave vs. Multi-Master
This article provides a comprehensive overview of database replication, a fundamental concept in system design for achieving high scalability, availability, and data durability. It deep-dives into two primary replication patterns: Master-Slave and Multi-Master, explaining their architectures, operational modes, and considerations like conflict resolution. Practical MySQL configuration examples are included.
Read original on Dev.to #systemdesignThe Importance of Database Replication in System Design
Database replication is a cornerstone of robust system design, essential for building scalable, highly available, and durable applications. By creating and maintaining copies of data across multiple database instances, replication addresses critical non-functional requirements. Its core objectives include achieving horizontal scalability by offloading read-heavy workloads to replica nodes, ensuring high availability through failover mechanisms, and improving disaster recovery by geographically dispersing data copies.
Replication Types: Statement-Based vs. Row-Based
Replication can operate by logging SQL statements (statement-based) or by recording actual row changes (row-based). Row-based replication is generally preferred in modern database engines due to its reliability, especially when dealing with schema changes or non-deterministic operations, as it logs the precise before-and-after images of rows.
Master-Slave Replication: Architecture and Modes
Master-Slave (Primary-Replica) Replication designates one master node for all write operations and one or more slave nodes for read operations and as hot standbys. The master writes all committed transactions to a transaction log (e.g., MySQL's binary log or PostgreSQL's WAL), which slave nodes then read and apply to synchronize their data. This model is widely adopted for read scaling while maintaining strong write consistency on the single master.
Replication Modes
Asynchronous Replication: The master commits and returns success immediately, with replication to slaves happening in the background. This offers low write latency but can lead to replication lag. Synchronous Replication: The master waits for acknowledgment from at least one slave before committing. This guarantees zero lag for acknowledged writes but increases write latency and reduces throughput. Semi-synchronous Replication: A balanced approach where the master waits for a slave to receive (but not necessarily apply) the event, improving durability over asynchronous without full synchronous latency.
Multi-Master Replication: Distributed Writes and Conflict Resolution
Multi-Master Replication allows multiple nodes to accept write operations concurrently, with changes propagated bidirectionally among all masters. This architecture is crucial for globally distributed applications requiring low-latency writes from multiple regions. However, it introduces the critical challenge of conflict resolution to prevent data divergence when the same data is modified on different masters concurrently.
- Last Writer Wins (LWW): Uses timestamps or logical clocks to determine the winning change.
- Version Vectors or CRDTs: More sophisticated methods for merging concurrent changes.
- Custom Business Logic: Application-level reconciliation or database triggers for specific conflict handling.
- Certification-based Mechanisms: MySQL Group Replication, for example, aborts conflicting transactions to maintain consistency.
[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-format = ROW
expire-logs-days = 7
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
gtid_mode = ON
enforce_gtid_consistency = ON