Master-Slave Architecture

The master-slave architecture, also known as the primary-replica architecture, is a widely used database replication pattern. It involves a primary server (the master) handling all write operations and one or more secondary servers (the slaves) that replicate data from the master. This design offers many benefits, but also comes with its own set of limitations and challenges. This post will look at the details of this architecture, exploring its advantages, disadvantages, and various implementation aspects.

How Master-Slave Architecture Works

The core principle is simple: the master server is the single source of truth. All write operations – INSERT, UPDATE, DELETE – are directed exclusively to the master. The master then propagates these changes to the slave servers through a replication process. Slave servers, in turn, primarily handle read operations, thereby offloading the read load from the master. This distribution of workload improves performance and scalability, especially for applications with a high read-to-write ratio.

Here’s a visual representation using a Diagram:

graph TD
    Client[("Client Applications")]
    LB["Load Balancer"]
    Master[("Master DB<br/>Primary Node<br/>Handles Writes")]
    S1[("Slave DB 1<br/>Read Replica")]
    S2[("Slave DB 2<br/>Read Replica")]
    S3[("Slave DB 3<br/>Read Replica")]
    
    Client -->|Requests| LB
    LB -->|Write Queries| Master
    LB -->|Read Queries| S1
    LB -->|Read Queries| S2
    LB -->|Read Queries| S3
    
    Master -->|Replication| S1
    Master -->|Replication| S2
    Master -->|Replication| S3
    
    subgraph "Write Operations"
        Master
    end
    
    subgraph "Read Operations"
        S1
        S2
        S3
    end
    
    class Master master
    class S1,S2,S3 slave
    class Client client
    class LB lb

This diagram shows the master server handling all write operations and distributing the data to multiple slave servers. Read operations are then directed to the slaves.

Key components of the master-slave architecture:

  1. Client Applications

  2. Load Balancer

  3. Master Node

  4. Slave Nodes

Benefits:

Limitations:

Database Replication Methods

Replication ensures data consistency across distributed database systems. Three primary methods exist for replicating data from primary (master) to secondary (slave) nodes:

1. Statement-Based Replication (SBR)

Primary node sends SQL statements to replicas.

Implementation Example

-- On Primary
BEGIN TRANSACTION;
INSERT INTO users (name, email) VALUES ('John', 'john@example.com');
UPDATE products SET stock = stock - 1 WHERE id = 100;
COMMIT;

-- Replicated to secondaries with transaction boundaries

Advantages

Limitations

2. Row-Based Replication (RBR)

Primary node replicates actual data modifications.

Implementation Example

-- Binary log format
BEGIN
    Table: users
    Operation: INSERT
    Row: {
        id: 1,
        name: 'John',
        email: 'john@example.com',
        created_at: '2024-01-25 10:15:00'
    }
    
    Table: products
    Operation: UPDATE
    Before: {
        id: 100,
        stock: 10,
        last_updated: '2024-01-25 10:14:59'
    }
    After: {
        id: 100,
        stock: 9,
        last_updated: '2024-01-25 10:15:00'
    }
COMMIT

Advantages

Limitations

3. Write-Ahead Logging (WAL)

Records all changes in transaction logs before modifying the database.

Implementation Example

# WAL Entry Structure
LSN: 1234                           # Log Sequence Number
XID: T123                           # Transaction ID
Timestamp: 2024-01-25 10:15:00.123  # Microsecond precision
Operation: INSERT
Table: users
Schema: public
Columns: (id, name, email, created_at)
Values: (1, 'John', 'john@example.com', '2024-01-25 10:15:00')
Previous LSN: 1233                  # For rollback operations
Checksum: 0x1A2B3C4D               # Data integrity verification

Components

  1. Log Records
  2. LSN Management
  3. Checkpoint Processing

Advantages

Best Practices

  1. Replication Method Selection

  2. Monitoring

  3. Configuration

    # Primary node configuration
sync_binlog = 1                      # Ensures durability
innodb_flush_log_at_trx_commit = 1   # ACID compliance
binlog_format = ROW                  # For RBR
max_binlog_size = 1G                 # Log file size limit
binlog_rows_query_log_events = ON    # Enhanced debugging

  4. Security

Advantages of Master-Slave Architecture

Disadvantages of Master-Slave Architecture