Master-Slave Mode

Applicable Scenarios

MySQL Master-Slave Replication refers to a database architecture mode where data can be replicated from one MySQL database server master node to one or multiple slave nodes.

1. Read-Write Separation

The master node is responsible for handling write operations (INSERT, UPDATE, DELETE, etc.), while slave nodes can share the load of read operations (SELECT). This architecture is particularly suitable for application scenarios with more reads and fewer writes.

2. Data Backup

Slave nodes can serve as real-time data backups of the master node. When the master node fails, services can be quickly switched to the slave node to continue providing services.

3. Data Analysis

Data analysis and big data processing can be performed on the replica without affecting the master database’s performance.

4. High Availability

High-availability system architecture can be built by configuring multiple slave nodes.

Main Purposes

1. Real-time Disaster Recovery (High Availability Guarantee)

  • Automatic failover mechanism: When the master database fails, business can be quickly switched to the replica to continue providing services, achieving business continuity

2. Read-Write Separation (Read Performance Scaling)

  • Load balancing: Distribute read requests to multiple replicas to reduce pressure on the master database

3. Data Backup (Business-Unaware Backup)

  • Backup advantages: Does not affect master database performance, flexible backup time, backup consistency

Prerequisites

1. Network Connection Requirements

  • Slave server must be able to access master server via TCP/IP protocol
  • Firewall needs to open MySQL service port (default 3306)

2. Master Database Configuration Requirements

  • Binary log (binlog) must be enabled
  • Need to create a user with replication permissions

3. Server Identifier Requirements

  • Each MySQL server must have a unique server-id

Implementation Principles

Master-Slave Replication

Master-slave replication is the core mechanism for MySQL database to achieve data synchronization. Its overall workflow can be divided into three key steps:

  1. Master Binlog Logging Phase

    • When the master executes any data-modifying operation, it records these change events in order to the Binary Log (Binlog)

  2. Slave Relay Log Writing Phase

    • The slave’s I/O Thread establishes a connection with the master and periodically polls for new binlog events
    • After receiving data, the slave’s I/O Thread writes these change events to the local Relay Log

  3. Slave Data Replay Phase

    • The slave’s SQL Thread monitors Relay Log changes in real-time
    • Parses event content in Relay Log and executes the same SQL statements on the slave in transaction order

Core Thread Function Description

Thread NameServerMain Responsibilities
Binlog Dump ThreadMasterReads binlog content and sends to slave
I/O ThreadSlaveReceives master binlog and writes to Relay Log
SQL ThreadSlaveParses and executes events in Relay Log

Semi-Synchronous Replication

Semi-Synchronous Replication is a replication mechanism provided by MySQL that sits between asynchronous replication and fully synchronous replication.

Background and Principles

In traditional asynchronous replication mode, after the master executes a transaction, it immediately returns to the client, without caring whether the slave has received and applied these changes.

Semi-synchronous replication solves this problem by introducing an ACK confirmation mechanism:

  1. Master executes transaction and writes to binary log (binlog)
  2. Master waits for at least one slave to confirm receiving and writing to relay log
  3. After receiving confirmation, master completes transaction commit and returns response to client
  4. If no confirmation is received within timeout, automatically degrades to asynchronous replication mode
Transaction Write and Replication Process

When semi-synchronous replication is enabled, the complete process of master transaction writing is as follows:

  1. InnoDB Redo File Write (Prepare Write)

    • Transaction starts, writes redo log (prepare state)

  2. Binlog File Flush & Sync to Binlog File

    • Generate binlog events and flush to disk

  3. Waiting for Slave Confirmation (Key Step in Semi-Sync)

    • Master sends binlog to slave
    • Waits for slave to return ACK confirmation

  4. InnoDB Redo File Commit (Commit Write)

    • After receiving slave confirmation, commits redo log (commit state)

  5. Return Client Response

    • Returns transaction execution result to application
Application Scenarios and Configuration Suggestions

Typical application scenarios:

  • Financial trading systems
  • Order payment systems
  • Other businesses with high requirements for data consistency

Configuration suggestions:

# Master configuration
plugin-load = "rpl_semi_sync_master=semisync_master.so"
rpl_semi_sync_master_enabled = 1
rpl_semi_sync_master_timeout = 10000 # 10 second timeout

# Slave configuration
plugin-load = "rpl_semi_sync_slave=semisync_slave.so"
rpl_semi_sync_slave_enabled = 1

“Master-slave replication is the core mechanism for MySQL database to achieve data synchronization.”

Data High Availability

  • Database cluster solutions

Scalability

Adding Replicas

  • Build database replication environment

Database Sharding

  • Vertical sharding
  • Horizontal sharding

Consistency Design

Synchronization Delay Monitoring

  • Master-slave delay time
  • Replication thread status
  • Network delay

Master-Slave Mode Overview

Core Features

  1. Clear role division
  2. Data synchronization mechanism
  3. Fault handling

Advantages and Disadvantages

Advantages:

  • Improves system availability
  • Enhances read performance
  • Achieves data redundancy

Disadvantages:

  • Data consistency is difficult to guarantee
  • Master node may become a performance bottleneck