本文詳解MySQL主從複製原理與配置，以及讀寫分離的實現方案，從單機到高可用架構。

前言

單機MySQL的問題：

• 單點故障
• 讀寫壓力集中
• 無法水平擴展

主從複製是MySQL高可用的基礎：

• 數據冗餘，提高可用性
• 讀寫分離，提升性能
• 實時備份，降低風險

今天來詳解MySQL主從複製的實戰配置。

---

一、主從複製原理

1.1 複製流程

┌─────────────────────────────────────────────────────────┐
│                      Master                              │
│  ┌─────────┐    ┌─────────────┐                        │
│  │ 數據變更 │ →  │  Binlog     │                        │
│  └─────────┘    └──────┬──────┘                        │
└─────────────────────────┼────────────────────────────────┘
│ ① 傳輸binlog
↓
┌─────────────────────────────────────────────────────────┐
│                      Slave                               │
│  ┌─────────────┐    ┌─────────────┐    ┌─────────────┐ │
│  │  IO Thread  │ →  │ Relay Log   │ →  │ SQL Thread  │ │
│  │  接收binlog │    │  中繼日誌    │    │  回放執行   │ │
│  └─────────────┘    └─────────────┘    └─────────────┘ │
└─────────────────────────────────────────────────────────┘

1.2 複製模式

模式	説明	優缺點
異步複製	主庫不等從庫確認	性能好，可能丟數據
半同步	至少一個從庫確認	折中方案
組複製(MGR)	Paxos協議	強一致，複雜

---

二、環境準備

2.1 Docker Compose部署

# docker-compose.yml
version: '3.8'

services:
  mysql-master:
    image: mysql:8.0
    container_name: mysql-master
    environment:
      MYSQL_ROOT_PASSWORD: root123
    ports:
      - "3306:3306"
    volumes:
      - ./master/conf:/etc/mysql/conf.d
      - ./master/data:/var/lib/mysql
      - ./master/logs:/var/log/mysql
    command: --server-id=1 --log-bin=mysql-bin --binlog-format=ROW

  mysql-slave:
    image: mysql:8.0
    container_name: mysql-slave
    environment:
      MYSQL_ROOT_PASSWORD: root123
    ports:
      - "3307:3306"
    volumes:
      - ./slave/conf:/etc/mysql/conf.d
      - ./slave/data:/var/lib/mysql
      - ./slave/logs:/var/log/mysql
    command: --server-id=2 --log-bin=mysql-bin --binlog-format=ROW --read-only=1
    depends_on:
      - mysql-master

2.2 配置文件

Master配置：

# master/conf/my.cnf
[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-format = ROW
sync-binlog = 1

# 需要同步的數據庫（不配則同步所有）
# binlog-do-db = mydb

# 忽略的數據庫
binlog-ignore-db = mysql
binlog-ignore-db = information_schema
binlog-ignore-db = performance_schema
binlog-ignore-db = sys

# GTID模式（推薦）
gtid_mode = ON
enforce_gtid_consistency = ON

Slave配置：

# slave/conf/my.cnf
[mysqld]
server-id = 2
log-bin = mysql-bin
binlog-format = ROW
relay-log = relay-bin
read-only = 1

# GTID模式
gtid_mode = ON
enforce_gtid_consistency = ON

# 跳過某些錯誤（謹慎使用）
# slave-skip-errors = 1062

2.3 啓動服務

# 創建目錄
mkdir -p master/{conf,data,logs} slave/{conf,data,logs}

# 啓動
docker compose up -d

# 查看狀態
docker ps

---

三、配置主從複製

3.1 在Master創建複製用户

-- 連接Master
mysql -h 127.0.0.1 -P 3306 -uroot -proot123

-- 創建複製用户
CREATE USER 'repl'@'%' IDENTIFIED BY 'repl123';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;

-- 查看Master狀態
SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |      857 |              | mysql,...        |
+------------------+----------+--------------+------------------+

3.2 配置Slave

-- 連接Slave
mysql -h 127.0.0.1 -P 3307 -uroot -proot123

-- 方式1：傳統位點複製
CHANGE MASTER TO
    MASTER_HOST='mysql-master',
    MASTER_PORT=3306,
    MASTER_USER='repl',
    MASTER_PASSWORD='repl123',
    MASTER_LOG_FILE='mysql-bin.000003',
    MASTER_LOG_POS=857;

-- 方式2：GTID複製（推薦）
CHANGE MASTER TO
    MASTER_HOST='mysql-master',
    MASTER_PORT=3306,
    MASTER_USER='repl',
    MASTER_PASSWORD='repl123',
    MASTER_AUTO_POSITION=1;

-- 啓動複製
START SLAVE;

-- 查看複製狀態
SHOW SLAVE STATUS\G

3.3 驗證複製

-- 關鍵字段
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0

-- Master寫入數據
CREATE DATABASE testdb;
USE testdb;
CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(50));
INSERT INTO users VALUES (1, 'test');

-- Slave查詢驗證
USE testdb;
SELECT * FROM users;
+----+------+
| id | name |
+----+------+
|  1 | test |
+----+------+

---

四、讀寫分離

4.1 方案對比

方案	優點	缺點
代碼層面	簡單，無額外組件	代碼侵入
中間件	透明，功能豐富	增加組件
MySQL Router	官方支持	功能有限

4.2 代碼層實現（Spring Boot）

// 數據源配置
@Configuration
public class DataSourceConfig {
    
    @Bean
    @ConfigurationProperties("spring.datasource.master")
    public DataSource masterDataSource() {
        return DataSourceBuilder.create().build();
    }
    
    @Bean
    @ConfigurationProperties("spring.datasource.slave")
    public DataSource slaveDataSource() {
        return DataSourceBuilder.create().build();
    }
    
    @Bean
    public DataSource routingDataSource(
            @Qualifier("masterDataSource") DataSource master,
            @Qualifier("slaveDataSource") DataSource slave) {
        
        Map<Object, Object> targetDataSources = new HashMap<>();
        targetDataSources.put("master", master);
        targetDataSources.put("slave", slave);
        
        RoutingDataSource routing = new RoutingDataSource();
        routing.setTargetDataSources(targetDataSources);
        routing.setDefaultTargetDataSource(master);
        return routing;
    }
}

// 動態數據源
public class RoutingDataSource extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        return DataSourceContextHolder.getDataSource();
    }
}

// 數據源上下文
public class DataSourceContextHolder {
    private static final ThreadLocal<String> CONTEXT = new ThreadLocal<>();
    
    public static void setMaster() { CONTEXT.set("master"); }
    public static void setSlave() { CONTEXT.set("slave"); }
    public static String getDataSource() { return CONTEXT.get(); }
    public static void clear() { CONTEXT.remove(); }
}

// AOP切面
@Aspect
@Component
public class DataSourceAspect {
    
    @Before("@annotation(readOnly)")
    public void setReadDataSource(ReadOnly readOnly) {
        DataSourceContextHolder.setSlave();
    }
    
    @Before("@annotation(org.springframework.transaction.annotation.Transactional)")
    public void setWriteDataSource() {
        DataSourceContextHolder.setMaster();
    }
    
    @After("execution(* com.example.service.*.*(..))")
    public void clear() {
        DataSourceContextHolder.clear();
    }
}

4.3 中間件方案（ShardingSphere）

# application.yml
spring:
  shardingsphere:
    datasource:
      names: master,slave
      master:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://192.168.1.1:3306/mydb
        username: root
        password: root123
      slave:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://192.168.1.2:3306/mydb
        username: root
        password: root123
    rules:
      readwrite-splitting:
        data-sources:
          readwrite_ds:
            static-strategy:
              write-data-source-name: master
              read-data-source-names: slave
            load-balancer-name: round_robin
        load-balancers:
          round_robin:
            type: ROUND_ROBIN

---

五、高可用架構

5.1 MHA架構

┌─────────────────────────────────────────────────────┐
│                    MHA Manager                       │
│                   (監控+故障轉移)                    │
└─────────────────────────────────────────────────────┘
        ↓               ↓               ↓
┌──────────────┐ ┌──────────────┐ ┌──────────────┐
│   Master     │ │   Slave1     │ │   Slave2     │
│ (可寫)       │ │ (候選Master) │ │ (只讀)       │
└──────────────┘ └──────────────┘ └──────────────┘

5.2 MGR組複製

-- 所有節點配置
[mysqld]
server_id = 1
gtid_mode = ON
enforce_gtid_consistency = ON
binlog_checksum = NONE

# 組複製配置
plugin_load_add = 'group_replication.so'
group_replication_group_name = "aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee"
group_replication_start_on_boot = OFF
group_replication_local_address = "192.168.1.1:33061"
group_replication_group_seeds = "192.168.1.1:33061,192.168.1.2:33061,192.168.1.3:33061"
group_replication_bootstrap_group = OFF

---

六、跨機房部署

6.1 場景挑戰

需求：
- 主庫在總部機房
- 從庫在分部機房（異地災備）
- 兩個機房網絡不通

傳統方案：
- 專線：成本高
- 公網暴露MySQL端口：風險大

6.2 組網方案

使用組網軟件（如星空組網）打通網絡：

┌─────────────────────────────────────────────────────────┐
│                    組網虛擬局域網                         │
│                                                          │
│  ┌──────────────────┐      ┌──────────────────┐         │
│  │     總部機房      │      │     分部機房      │         │
│  │                  │      │                  │         │
│  │  Master          │      │  Slave           │         │
│  │  10.10.0.1:3306  │ ←同步─│  10.10.0.2:3306  │         │
│  │                  │      │                  │         │
│  └──────────────────┘      └──────────────────┘         │
│                                                          │
└─────────────────────────────────────────────────────────┘

Slave配置：

-- 使用組網IP連接Master
CHANGE MASTER TO
    MASTER_HOST='10.10.0.1',   -- 組網IP
    MASTER_PORT=3306,
    MASTER_USER='repl',
    MASTER_PASSWORD='repl123',
    MASTER_AUTO_POSITION=1;
    
START SLAVE;

優勢：

• 不需要公網暴露3306端口
• 加密傳輸，安全可靠
• 配置簡單
• 運維人員可通過組網遠程管理

6.3 遠程運維

# 通過組網IP遠程連接
mysql -h 10.10.0.1 -P 3306 -uroot -p

# 遠程備份
mysqldump -h 10.10.0.1 -uroot -p mydb > backup.sql

# 遠程監控
mysqlsh --uri root@10.10.0.1:3306 --js

---

七、監控與運維

7.1 監控指標

-- 複製延遲
SHOW SLAVE STATUS\G
-- Seconds_Behind_Master

-- 線程狀態
SHOW PROCESSLIST;

-- 複製錯誤
SHOW SLAVE STATUS\G
-- Last_Error, Last_IO_Error, Last_SQL_Error

7.2 監控腳本

#!/bin/bash
# check_replication.sh

MYSQL_CMD="mysql -h 127.0.0.1 -P 3307 -urepl -prepl123"

IO_RUNNING=$($MYSQL_CMD -e "SHOW SLAVE STATUS\G" | grep "Slave_IO_Running" | awk '{print $2}')
SQL_RUNNING=$($MYSQL_CMD -e "SHOW SLAVE STATUS\G" | grep "Slave_SQL_Running" | awk '{print $2}')
DELAY=$($MYSQL_CMD -e "SHOW SLAVE STATUS\G" | grep "Seconds_Behind_Master" | awk '{print $2}')

echo "IO Thread: $IO_RUNNING"
echo "SQL Thread: $SQL_RUNNING"
echo "Delay: ${DELAY}s"

if [ "$IO_RUNNING" != "Yes" ] || [ "$SQL_RUNNING" != "Yes" ]; then
    echo "ALERT: Replication is broken!"
    # 發送告警
fi

if [ "$DELAY" -gt 60 ]; then
    echo "ALERT: Replication delay > 60s"
fi

7.3 常見問題處理

複製中斷：

-- 查看錯誤
SHOW SLAVE STATUS\G

-- 跳過錯誤（謹慎）
STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE;

-- 或使用GTID跳過
SET GTID_NEXT='xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx:N';
BEGIN; COMMIT;
SET GTID_NEXT='AUTOMATIC';
START SLAVE;

主從切換：

-- 原Slave提升為Master
STOP SLAVE;
RESET SLAVE ALL;
SET GLOBAL read_only = 0;

-- 原Master降為Slave
CHANGE MASTER TO ...;
SET GLOBAL read_only = 1;
START SLAVE;

---

八、性能優化

8.1 並行複製

# MySQL 5.7+
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 4
slave_preserve_commit_order = 1

8.2 半同步複製

-- Master
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
SET GLOBAL rpl_semi_sync_master_enabled = 1;
SET GLOBAL rpl_semi_sync_master_timeout = 10000;

-- Slave
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
SET GLOBAL rpl_semi_sync_slave_enabled = 1;
STOP SLAVE; START SLAVE;

8.3 參數優化

# binlog優化
binlog_cache_size = 4M
max_binlog_size = 500M
expire_logs_days = 7

# 複製優化
slave_net_timeout = 60
sync_relay_log = 10000
relay_log_recovery = 1

---

九、總結

MySQL主從複製要點：

1. 基礎配置：server-id唯一，開啓binlog
2. GTID模式：推薦使用，簡化管理
3. 讀寫分離：中間件方案更優雅
4. 高可用：MHA/MGR實現自動故障轉移
5. 跨機房：組網打通後正常同步
6. 監控告警：複製狀態和延遲

生產環境清單：

☑ 主從複製配置完成
☑ 複製用户權限最小化
☑ 監控腳本部署
☑ 備份策略制定
☑ 故障切換演練

---

參考資料

1. MySQL官方複製文檔：https://dev.mysql.com/doc/refman/8.0/en/replication.html
2. MySQL高可用：https://dev.mysql.com/doc/mysql-ha-scalability/en/

---

💡 建議：生產環境務必使用GTID模式，配置半同步複製，定期進行主從切換演練。