问题背景

MySQL 主从复制延迟是 DBA 和运维的常见痛点。生产里经常听到这些声音：

• "主库写入正常，从库查到数据还是几秒前的。"
• "凌晨批量任务跑完，从库延迟飙到 1 小时，业务读到老数据。"
• "主从切换后，从库没追上，所有人都在刷错误日志。"
• "5.7 升级到 8.0 后，单 SQL 线程还是慢，并行复制调了没用。"
• "延迟告警每隔几分钟就触发，但主从链路看起来都正常。"

主从延迟不像磁盘满、连接不上那样立刻报错，而是悄无声息地让业务读到陈旧数据。严重的时候主从切换卡住、备库永远追不上、读写分离读到脏数据。这篇文章把复制延迟的原理、排查、调优讲透，覆盖 MySQL 5.6、5.7、8.0 三个主要版本。

适用读者

• 维护 MySQL 主从架构的 DBA、运维工程师。
• 准备升级 MySQL 5.7 → 8.0 的同学。
• 遇到 Seconds_Behind_Master 持续不为 0 的同学。
• 想做读写分离 / 多副本架构的工程师。

适用场景

• 单主单从、读写分离。
• 单主多从、级联复制。
• 半同步复制（AFTER_SYNC / AFTER_COMMIT）。
• GTID 复制模式。
• MySQL 5.6 / 5.7 / 8.0 各版本。
• 传统 binlog + position、GTID 两种复制方式。

核心知识点

复制原理

MySQL 主从复制本质上是主库把变更以事件（event）的形式记录到 binlog，从库 IO 线程拉 binlog 到本地 relay log，再由 SQL 线程重放。

主库：
  客户端写入 → InnoDB → binlog dump → binlog 文件
                                       ↓
从库：
  IO 线程 ←←←←←←←←←←←←←←←←←← binlog 文件
  ↓
  relay log
  ↓
  SQL 线程（或 worker 线程）重放
  ↓
  InnoDB

主从延迟 = 从库 apply 时间 - 主库 commit 时间。

三种格式：

• STATEMENT：记录 SQL 语句。binlog 小，但有非确定性函数（NOW()、UUID()）问题。
• ROW：记录行变化。binlog 大，但数据一致性强。
• MIXED：MySQL 自动选择，STATEMENT 优先。

生产里推荐 ROW 格式。理由：

• RBR（Row-Based Replication）是 5.7.7+ 默认值。
• 对非确定性函数友好。
• 配合并行复制 WRITESET 模式效果好。
• 工具链（pt-table-checksum、mysqlbinlog --base64-output=decode-rows）支持好。

-- 查看 binlog format
SHOWVARIABLESLIKE'binlog_format';

-- 动态修改
SETGLOBAL binlog_format = 'ROW';

注意：5.7.7+ 改 binlog_format 需要重启。8.0 也建议写入配置文件而不是动态改。

1. 事务执行
2. 写 binlog cache（per-thread 内存）
3. 事务 commit 触发：
   a. binlog cache → binlog file（fsync）
   b. InnoDB redo log → redo log file（fsync）
   c. 通知 dump 线程
4. dump 线程发送 binlog event 到从库

两次 fsync 是关键路径。sync_binlog 控制 binlog fsync 频率：

• sync_binlog=0：OS 自行 fsync。
• sync_binlog=1：每个事务 fsync，最安全。
• sync_binlog=N：每 N 个事务 fsync 一次。

生产推荐 sync_binlog=1，配合 innodb_flush_log_at_trx_commit=1。

主从复制模式

MySQL 默认模式。主库 commit 后立即返回，不等待从库确认。性能最好，但有数据丢失风险（主库宕机时未同步的事务会丢）。

MySQL 5.5 引入，主库 commit 后等待至少一个从库确认收到 binlog 才返回。数据更安全，延迟略高。

-- 主库安装插件
INSTALLPLUGIN rpl_semi_sync_master SONAME'semisync_master.so';
SETGLOBAL rpl_semi_sync_master_enabled = 1;
SETGLOBAL rpl_semi_sync_master_timeout = 1000;  -- 1秒超时，超时后降级为异步

-- 从库安装插件
INSTALLPLUGIN rpl_semi_sync_slave SONAME'semisync_slave.so';
SETGLOBAL rpl_semi_sync_slave_enabled = 1;
STOPSLAVE IO_THREAD;
STARTSLAVE IO_THREAD;

5.7+ 默认是 AFTER_SYNC 模式（5.6 是 AFTER_COMMIT）：

• AFTER_COMMIT：主库 commit 后等待从库 ack。问题是等待期间其他 session 能看到新数据，但主库可能挂导致数据不一致。
• AFTER_SYNC：主库写 binlog 后等待从库 ack，再 commit。数据更一致。

5.7+ 默认 AFTER_SYNC 是更安全的选择。

MySQL 5.7.17+ 引入，基于 Paxos 协议的多主或单主模式。

-- 启动 MGR
SETGLOBAL group_replication_group_name = "aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee";
SETGLOBAL group_replication_start_on_boot = 1;
SETGLOBAL group_replication_bootstrap_group = 1;
START GROUP_REPLICATION;

特点：

• 多主模式：所有节点都可写，需要业务层处理写冲突。
• 单主模式：自动选主，类似半同步的扩展。
• 强一致性：半数以上节点确认才 commit。
• 性能不如异步，延迟较大。

适用场景：对一致性要求高的金融业务。常规业务用半同步 + GTID 就够。

GTID 复制

GTID（Global Transaction Identifier）是 MySQL 5.6 引入的全局事务 ID，格式为 server_uuid:transaction_id。

3E11FA47-71CA-11E1-9E33-C80AA9429562:23

GTID 优势：

• 不用管 binlog file + position，从库自动找到当前位置。
• 主从切换简单，新主从其他从库不用重新指定位置。
• 复制状态更可靠，事务不会重复执行。

GTID 复制配置：

# 主库 my.cnf
[mysqld]
gtid_mode = ON
enforce_gtid_consistency = ON
log_bin = mysql-bin
server_id = 1

# 从库 my.cnf
[mysqld]
gtid_mode = ON
enforce_gtid_consistency = ON
log_bin = mysql-bin
server_id = 2
relay_log = relay-bin
log_slave_updates = ON
read_only = ON

从库配置复制：

CHANGEMASTERTO
  MASTER_HOST='10.0.0.1',
  MASTER_USER='repl',
  MASTER_PASSWORD='replpass',
  MASTER_AUTO_POSITION=1;

STARTSLAVE;

风险提示：enforce_gtid_consistency=ON 后禁止某些 SQL（如 CREATE TABLE ... SELECT），升级前要业务测过。

复制架构

最简单，主库写、从库读。适合读多写少。

主库多写一写，多从库分担读压力。适合读压力大的场景。

       ┌─→ 从库1 (读)
主库 ──┼─→ 从库2 (读)
       └─→ 从库3 (备份)

主库 → 中继从库 → 多从库。减少主库 dump 压力。

主库 → 中继从库 (log_slave_updates=ON) → 多个从库

主库不直接给所有从库发送 binlog，而是发给中继从库；中继从库再转发给其他从库。

两台机器互为主从，都可写。需要业务层处理自增 ID 冲突、循环复制问题。生产里不推荐。

关键指标

5.7+ SHOW SLAVE STATUS 的字段部分被 replica 关键字替代（8.0.22+），但 SHOW SLAVE STATUS 仍然兼容。

实战一：搭建主从复制

准备

两台机器：

• 主库：10.0.0.1（CentOS 7.9 / MySQL 8.0.x）
• 从库：10.0.0.2

主库配置

# /etc/my.cnf
[mysqld]
user = mysql
port = 3306
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
log-error = /var/log/mysql/error.log
pid-file = /var/run/mysql/mysqld.pid

# 复制相关
server_id = 1
log_bin = /var/log/mysql/mysql-bin
binlog_format = ROW
gtid_mode = ON
enforce_gtid_consistency = ON
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1

# 半同步
plugin-load = "rpl_semi_sync_master=semisync_master.so"
rpl_semi_sync_master_enabled = 1
rpl_semi_sync_master_timeout = 1000

sudo systemctl restart mysqld

创建复制用户

CREATEUSER'repl'@'10.0.0.%'IDENTIFIEDWITH mysql_native_password BY'ReplPass123!';
GRANTREPLICATIONSLAVEON *.* TO'repl'@'10.0.0.%';
FLUSHPRIVILEGES;

风险提示：复制用户密码要强，IP 段要限定。生产里用 mysql_native_password 5.7+ 默认 caching_sha2_password，从库 5.7 升级前要确认兼容性，或者指定 mysql_native_password。

备份主库

# 用 mysqldump 备份（一致性快照）
mysqldump --single-transaction --master-data=2 
  --triggers --routines --events 
  --all-databases | gzip > full_backup_$(date +%Y%m%d).sql.gz

# 8.0 推荐用 mysqlpump 或 mydumper 加速

风险提示：--single-transaction 在 MyISAM 表上无效。生产里有 MyISAM 表的话要用 --lock-all-tables 或者停服。

从库配置

# /etc/my.cnf
[mysqld]
user = mysql
port = 3306
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
log-error = /var/log/mysql/error.log
pid-file = /var/run/mysql/mysqld.pid

server_id = 2
log_bin = /var/log/mysql/mysql-bin
binlog_format = ROW
gtid_mode = ON
enforce_gtid_consistency = ON
relay_log = /var/log/mysql/relay-bin
log_slave_updates = ON
read_only = ON
super_read_only = ON  # 8.0 推荐，防止 super 用户误写

# 半同步
plugin-load = "rpl_semi_sync_slave=semisync_slave.so"
rpl_semi_sync_slave_enabled = 1

# 复制性能
slave_parallel_workers = 8
slave_parallel_type = LOGICAL_CLOCK
slave_preserve_commit_order = ON

恢复备份

# 把主库备份传到从库
scp full_backup_*.sql.gz 10.0.0.2:/tmp/

# 在从库恢复
gunzip < /tmp/full_backup_*.sql.gz | mysql

配置复制

-- 8.0.22+ 推荐用 CHANGE REPLICATION SOURCE TO
CHANGEREPLICATIONSOURCETO
  SOURCE_HOST = '10.0.0.1',
  SOURCE_USER = 'repl',
  SOURCE_PASSWORD = 'ReplPass123!',
  SOURCE_AUTO_POSITION = 1;

START REPLICA;
-- 兼容老版本：START SLAVE;

验证

SHOW REPLICA STATUSG
-- 或 8.0 之前：SHOW SLAVE STATUSG

-- 关注：
-- Replica_IO_Running: Yes
-- Replica_SQL_Running: Yes
-- Seconds_Behind_Source: 0
-- 或老版本：
-- Slave_IO_Running: Yes
-- Slave_SQL_Running: Yes
-- Seconds_Behind_Master: 0

Seconds_Behind_Source（或 Seconds_Behind_Master）持续 0 表示没有延迟。

风险提示：Seconds_Behind_Master 不可信。复制中断时它显示 NULL。5.7+ 用 replica_lag（performance_schema）做更准确的监控。

5.6/5.7 vs 8.0 命令差异

8.0 兼容老命令，但建议新项目用新命令。

实战二：延迟排查路径

现象 1：延迟持续为 0 但业务反馈读到老数据

排查：

1. 业务是不是从从库读？看连接配置。
2. 从库是不是 read_only？业务是不是绕过 read_only 直接写？
3. 中间件（MySQL Router / ProxySQL / MaxScale）是不是路由错了？

现象 2：延迟持续增长，1 小时、2 小时

排查：

-- 1. 看复制状态
SHOW REPLICA STATUSG

-- 2. 看从库当前在跑什么
SHOWPROCESSLIST;

-- 3. 看从库锁等待
SELECT * FROM performance_schema.events_statements_history
WHERE THREAD_ID IN (
SELECT THREAD_ID FROM performance_schema.threads
WHERENAMELIKE'thread/sql%worker%'
  )
ORDERBY EVENT_ID DESCLIMIT20;

-- 4. 看主库最近大事务
SELECT * FROM information_schema.innodb_trx
WHERE trx_started < NOW() - INTERVAL10SECOND
ORDERBY trx_started;

-- 5. 看主库 binlog
SHOWMASTERSTATUS;
-- 找最近的 binlog 文件
SHOWBINLOGEVENTSIN'mysql-bin.000123'LIMIT10;

常见原因：

• 主库跑大事务（DELETE 几百万行、ALTER TABLE 大表）。
• 主库有锁等待 / 死锁。
• 从库磁盘慢，relay log 写入跟不上。
• 从库单线程 SQL 线程（5.6 默认模式）。
• 网络慢，binlog 拉取不及时。

现象 3：延迟抖动，几分钟一波

排查：

• 业务定时任务（每 5 分钟 / 每 10 分钟）的批量写入。
• 监控 / 备份脚本（mysqldump、xtrabackup）。
• 主库 GC / checkpoint。
• 从库 OS 抖动（其他进程抢 IO）。

现象 4：延迟突然归零但实际有数据差异

排查：

• Seconds_Behind_Master 算法基于 TIMESTAMP 字段，binlog 缺这个字段时它显示 0。
• 5.7+ 推荐用 replica_lag 指标。

-- performance_schema 复制延迟
SELECT * FROM performance_schema.replication_applier_status;

-- MySQL 8.0.27+ 用心跳表
CREATETABLE heartbeat (
idINT PRIMARY KEY,
  ts TIMESTAMP(6) DEFAULTCURRENT_TIMESTAMP(6) ONUPDATECURRENT_TIMESTAMP(6)
);
INSERTINTO heartbeat (id) VALUES (1);

-- 主库
SELECT * FROM heartbeat;
-- 从库对比
SELECT * FROM heartbeat;
-- ts 差 = 真实延迟

实战三：监控配置

SHOW SLAVE STATUS 关键指标

SHOWSLAVESTATUSG

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.0.0.1
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000123
          Read_Master_Log_Pos: 12345678
               Relay_Log_File: relay-bin.000045
                Relay_Log_Pos: 12345678
        Relay_Master_Log_File: mysql-bin.000123
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 12345678
              Relay_Log_Space: 134217728
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 5
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: 3e11fa47-71ca-11e1-9e33-c80aa9429562
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Reading event from the relay log
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set: 3e11fa47-71ca-11e1-9e33-c80aa9429562:1-100
            Executed_Gtid_Set: 3e11fa47-71ca-11e1-9e33-c80aa9429562:1-90
                Auto_Position: 1
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:

Prometheus 监控

用 mysqld_exporter 抓取指标：

# prometheus-servicemonitor.yaml
apiVersion:monitoring.coreos.com/v1
kind:ServiceMonitor
metadata:
name:mysql
namespace:monitoring
spec:
selector:
matchLabels:
app:mysqld-exporter
endpoints:
-port:metrics
interval:15s

关键告警规则：

# prometheus-rule-mysql-replication.yaml
apiVersion:monitoring.coreos.com/v1
kind:PrometheusRule
metadata:
name:mysql-replication
namespace:monitoring
spec:
groups:
-name:mysql-replication
rules:
-alert:MySQLReplicaLagHigh
expr:mysql_slave_status_seconds_behind_master>60
for:5m
labels:
severity:warning
annotations:
summary:"MySQL 从库延迟 {{ $value }} 秒"
description:"{{ $labels.instance }} 复制延迟超过 1 分钟。"

-alert:MySQLReplicaLagCritical
expr:mysql_slave_status_seconds_behind_master>600
for:0m
labels:
severity:critical
annotations:
summary:"MySQL 从库延迟 {{ $value }} 秒（10分钟+）"
description:"需要立即介入。"

-alert:MySQLReplicaIOThreadDown
expr:mysql_slave_status_slave_io_running==0
for:1m
labels:
severity:critical
annotations:
summary:"MySQL 从库 IO 线程停止"
description:"复制链路断开，1 分钟内未恢复。"

-alert:MySQLReplicaSQLThreadDown
expr:mysql_slave_status_slave_sql_running==0
for:1m
labels:
severity:critical
annotations:
summary:"MySQL 从库 SQL 线程停止"
description:"可能是 SQL 错误导致。检查 last_sql_error。"

-alert:MySQLReplicaRelayLogSpaceHigh
expr:mysql_slave_status_relay_log_space>1073741824# 1GB
for:10m
labels:
severity:warning
annotations:
summary:"MySQL 从库 relay log 占用超过 1GB"
description:"可能 IO 线程追不上 SQL 线程。"

performance_schema 监控（更准确）

-- 8.0 推荐用 performance_schema
SELECT * FROM performance_schema.replication_connection_statusG
SELECT * FROM performance_schema.replication_applier_statusG

-- 主从延迟
SELECT
  channel_name,
  service_state,
  remaining_seconds,
  total_seconds
FROM performance_schema.replication_applier_status_by_worker;

业务侧监控

部署一个后台任务，每秒 / 每 5 秒向主库写 timestamp 到 heartbeat 表，再从从库读 timestamp，对比差异：

# heartbeat_check.py
import time
import pymysql

defcheck_replication_lag():
    master = pymysql.connect(host='10.0.0.1', user='monitor', password='xxx', database='monitor')
    slave = pymysql.connect(host='10.0.0.2', user='monitor', password='xxx', database='monitor')

try:
with master.cursor() as c:
            c.execute("UPDATE heartbeat SET ts = NOW(6) WHERE id = 1")
            master.commit()
with master.cursor() as c:
            c.execute("SELECT ts FROM heartbeat WHERE id = 1")
            master_ts = c.fetchone()[0]
with slave.cursor() as c:
            c.execute("SELECT ts FROM heartbeat WHERE id = 1")
            slave_ts = c.fetchone()[0]

        lag = (master_ts - slave_ts).total_seconds()
if lag > 5:
            print(f"复制延迟 {lag} 秒")
return lag
finally:
        master.close()
        slave.close()

whileTrue:
    check_replication_lag()
    time.sleep(1)

实战四：并行复制调优

5.6 默认并行复制

5.6 引入基于 schema 的并行复制，5.6 之前是单线程。schema 级别并行对单库单表无意义。

# my.cnf
slave_parallel_workers = 4

5.6 schema 级并行 + 5.7 database 级并行 → 5.7.2+ LOGICAL_CLOCK 并行。

5.7 MTS（Multi-Threaded Slave）

5.7.2+ 引入基于组提交的并行复制（LOGICAL_CLOCK）。

# my.cnf
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 8

原理：主库在 binlog 里写入"组提交时间戳"，从库 SQL 线程根据时间戳把事务分到不同 worker。同一个组提交的事务可以并行。

5.7.22+ WRITESET 并行

进一步优化，用 WRITESET 哈希判断事务是否冲突：

# my.cnf
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 8
binlog_transaction_dependency_tracking = WRITESET
transaction_write_set_extraction = XXHASH64

WRITESET 模式：两个事务的 WRITESET 不相交即可并行。比 LOGICAL_CLOCK 粒度更细。

8.0 WRITESET_SESSION

8.0.27+ 引入 WRITESET_SESSION：

# my.cnf
binlog_transaction_dependency_tracking = WRITESET_SESSION

WRITESET_SESSION：保证同一 session 的事务串行，避免主键冲突问题（同一 session 插入相同主键）。

推荐配置：

• 8.0.27+：WRITESET_SESSION
• 8.0 早期：WRITESET
• 5.7：LOGICAL_CLOCK

启用并行的步骤

-- 1. 动态启用（不需要重启）
SETGLOBAL slave_parallel_type = 'LOGICAL_CLOCK';
SETGLOBAL slave_parallel_workers = 8;
STOPSLAVE;
STARTSLAVE;

-- 2. 验证
SHOWSLAVESTATUSG
-- 检查 SQL 线程是否是多个
-- mysql> SELECT * FROM performance_schema.threads WHERE NAME LIKE '%worker%';

并行度调优

slave_parallel_workers 设多少合适？

经验值：

• 4 核 8GB 机器：4~8
• 8 核 16GB 机器：8~16
• 16 核 32GB 机器：16~32

不是越大越好。worker 多了，锁争用也会增加。先观察 Slave_SQL_Running_State 和 lag 趋势，再调整。

-- 看 worker 状态
SELECT
  worker_id,
  thread_id,
  service_state,
  last_error_number,
  last_error_message
FROM performance_schema.replication_applier_status_by_worker;

commit_order 保持

并行复制可能导致从库事务提交顺序与主库不一致。slave_preserve_commit_order=ON 强制从库按主库顺序 commit：

slave_preserve_commit_order = ON

这会导致部分并行度损失，但保证数据一致。生产建议开启。

实战五：业务层调优

拆大事务

大事务是延迟的头号杀手。

-- 错：一次删除 100 万行
DELETEFROMlogsWHERE created_at < '2024-01-01';

-- 对：分批删除
DELETEFROMlogsWHERE created_at < '2024-01-01'LIMIT10000;
-- 循环执行

-- 错：一次 ALTER 改大表
ALTERTABLE big_table ADDCOLUMN x INT;

-- 对：pt-online-schema-change 或者 gh-ost 工具
-- gh-ost：
gh-ost 
--host=10.0.0.1 
--database=mydb 
--table=big_table 
--alter="ADD COLUMN x INT" 
--execute

-- 风险提示：gh-ost 会创建影子表 + 触发器，需要确保 trigger 不影响 binlog。

减少锁等待

-- 看主库当前锁
SELECT * FROM information_schema.innodb_trx
WHERE trx_started < NOW() - INTERVAL30SECOND
ORDERBY trx_started;

-- 看锁等待
SELECT * FROM performance_schema.data_locks LIMIT10;
SELECT * FROM performance_schema.data_lock_waits LIMIT10;

长事务会导致：

• 主库 binlog 不分段。
• 从库 apply 时这个事务要完整执行。
• 期间其他事务被阻塞。

-- 杀长事务
SELECT
  processlist.id,
  processlist.user,
  processlist.host,
  processlist.db,
  processlist.command,
  processlist.time,
  processlist.state,
  processlist.info
FROM information_schema.processlist
WHERE processlist.command != 'Sleep'
AND processlist.time > 60
ORDERBY processlist.time DESC;

KILL <id>;

拆分批量写入

# 错：一次插入 10 万行
cursor.executemany("INSERT INTO log VALUES (%s, %s, %s)", data)

# 对：分批插入
batch_size = 1000
for i in range(0, len(data), batch_size):
    cursor.executemany("INSERT INTO log VALUES (%s, %s, %s)", data[i:i+batch_size])
    conn.commit()

读写分离路由

# ProxySQL 路由配置
mysql_users:
  - username: app
    password: xxx
    default_hostgroup: 0# 默认写主库

mysql_query_rules:
  - rule_id: 1
    match_pattern: "^SELECT .* FOR UPDATE$"
    destination_hostgroup: 0# 写主库
  - rule_id: 2
    match_pattern: "^SELECT .*"
    destination_hostgroup: 1# 读从库

# MySQL Router 8.0 配置
[routing:read_write]
bind_address = 0.0.0.0:7001
destinations = 10.0.0.1:3306
mode = read-write

[routing:read_only]
bind_address = 0.0.0.0:7002
destinations = 10.0.0.2:3306,10.0.0.3:3306
mode = read-only

风险提示：读写分离后，业务如果有"读后写"的逻辑（比如先 SELECT 再 UPDATE），可能读到从库老数据。需要业务层做补偿，或者强制某些查询走主库（ProxySQL 的 mysql_query_rules 或者代码里加 hint）。

选择性读主库

-- 强制走主库
SELECT/*+ MASTER */ * FROMusersWHEREid = 1;

-- 或者在事务里全部走主库
BEGIN;
SELECT * FROMusersWHEREid = 1;  -- 自动走主库
UPDATEusersSETname = 'x'WHEREid = 1;
COMMIT;

实战六：复制问题排查

问题 1：Slave_SQL_Running: No

SHOWSLAVESTATUSG
Last_Error: Could notexecute Write_rows eventontable mydb.t;
Duplicate entry '1234' for key 'PRIMARY'

原因：从库已经有这条记录了，主库又插入了一次。可能是：

• 之前手动插入过数据。
• 复制中断时主库重新执行了事务。
• 双写（业务同时写主库和从库）。

解决：

-- 1. 跳过这个错误（高危）
STOPSLAVE;
SETGLOBAL sql_slave_skip_counter = 1;
STARTSLAVE;

-- 2. 用 pt-slave-restart 跳过指定错误码
pt-slave-restart --error-numbers=1062 --host=10.0.0.2

-- 3. 找到重复数据并删除
SELECT * FROM mydb.t WHEREid = 1234;
-- 确认是否真的在从库存在
-- 如果是主库重新执行了事务，从库先 delete 再重新 insert

风险提示：SET GLOBAL sql_slave_skip_counter = 1 会跳过下一个事务。如果错误是数据冲突，跳过可能丢数据。先在测试环境演练。

问题 2：Slave_IO_Running: No

Last_IO_Error: error connecting to master '[email protected]:3306' - retry-time: 60  retries: 86400

原因：

• 网络不通。
• 主库复制用户密码错误。
• 主库端口被防火墙挡。
• 主库 max_connections 用尽。
• server_id 重复。

解决：

# 测试网络
telnet 10.0.0.1 3306

# 测试复制用户
mysql -h 10.0.0.1 -u repl -p

# 看主库 server_id
mysql -h 10.0.0.1 -e "SHOW VARIABLES LIKE 'server_id'"

# 重新配置复制
STOP SLAVE;
CHANGE MASTER TO MASTER_HOST='10.0.0.1', MASTER_USER='repl', MASTER_PASSWORD='xxx', MASTER_AUTO_POSITION=1;
START SLAVE;

问题 3：relay log 爆满

Relay_Log_Space: 10737418240  -- 10GB

原因：从库 IO 线程拉取 binlog 比 SQL 线程快太多。

解决：

# my.cnf
relay_log_purge = ON  # 默认 ON
relay_log_recovery = ON  # 重启时自动清理
max_relay_log_size = 1G  # 单个 relay log 文件大小

或者手动清理：

-- 看 relay log
SHOWSLAVESTATUSG
-- Relay_Log_File: relay-bin.000123
-- 等从库追平后删除旧文件

问题 4：主从数据不一致

检测：

# pt-table-checksum 检查主从一致性
pt-table-checksum --host=10.0.0.1 --user=root --password=xxx 
  --replicate=test.checksum

# 看结果
pt-table-checksum --host=10.0.0.1 --user=root --password=xxx 
  --replicate=test.checksum --replicate-check-only

修复：

# pt-table-sync 同步
pt-table-sync --execute --sync-to-master 
  --host=10.0.0.2 --user=root --password=xxx 
  --tables mydb.users

风险提示：pt-table-sync 会修数据，修复前先备份。

问题 5：主从切换后数据丢失

GTID 复制下，切换后可能丢事务。处理：

-- 1. 找最接近主库的从库（GTID 最新）
SELECT @@global.gtid_executed;

-- 2. 提升为新主
STOPSLAVE;
RESETSLAVEALL;
SETGLOBAL read_only = OFF;
SETGLOBAL super_read_only = OFF;

-- 3. 其他从库指向新主
CHANGEMASTERTO MASTER_HOST='10.0.0.2', MASTER_AUTO_POSITION=1;
STARTSLAVE;

RESET SLAVE ALL 清掉所有复制配置，从库变独立主库。

问题 6：磁盘满导致复制中断

# 检查磁盘
df -h /var/lib/mysql

# 清理 binlog / relay log
PURGE BINARY LOGS BEFORE '2024-01-01 00:00:00';
PURGE RELAY LOGS BEFORE '2024-01-01 00:00:00';  -- 5.7+

风险提示：清理 binlog 前确认已经从库都拉走了。检查 Read_Master_Log_Pos 是否追上 Master_Log_File 的当前 size。

实战七：升级到 MySQL 8.0

升级路径

5.6 → 5.7 → 8.0  （必须逐步）

不能跨大版本升级。

升级前

-- 5.6 → 5.7 前
-- 1. 检查 sql_mode
SELECT @@sql_mode;
-- 5.7 默认 sql_mode 比 5.6 严格

-- 2. 检查表结构兼容性
-- 5.7 不支持某些字段类型
SHOWWARNINGS;

-- 3. 备份
mysqldump --all-databases --routines --events --triggers --master-data=2 | gzip > backup.sql.gz

升级 5.6 → 5.7

# 1. 关闭 5.6
sudo systemctl stop mysqld

# 2. 安装 5.7
# CentOS/RHEL
sudo yum install mysql-community-server

# 3. 启动
sudo systemctl start mysqld

# 4. 运行 mysql_upgrade
sudo mysql_upgrade -u root -p

升级 5.7 → 8.0

# 1. 先升级从库，验证业务
# 2. 关闭 5.7
sudo systemctl stop mysqld

# 3. 安装 8.0
sudo yum install mysql-community-server

# 4. 启动
sudo systemctl start mysqld

# 5. 运行 mysql_upgrade
sudo mysql_upgrade -u root -p

8.0 复制注意事项

• 8.0.27+ 默认 caching_sha2_password，从库升级前确认 client 兼容。
• 8.0.22+ 推荐 CHANGE REPLICATION SOURCE TO 替代 CHANGE MASTER TO。
• 8.0.20+ 部分性能优化：redo log 优化、hash join、并行扫描。
• 8.0 资源组（Resource Group）可以给后台线程分配专用 CPU。

实战八：实战案例

案例 1：1.5 小时延迟定位

现象：凌晨 3 点收到告警，从库延迟 1.5 小时。

排查过程：

-- 1. 看主库当前活跃事务
SELECT * FROM information_schema.innodb_trx
WHERE trx_started < NOW() - INTERVAL600SECOND
ORDERBY trx_started;

-- 发现一个 trx_id=12345 的事务跑了 5800 秒
-- 该事务的 SQL 是：
SELECT trx_id, trx_state, trx_started, trx_query, trx_mysql_thread_id
FROM information_schema.innodb_trx;

-- 2. 看这个线程在做什么
SELECT * FROM performance_schema.events_statements_history
WHERE THREAD_ID = 12345
ORDERBY EVENT_ID DESCLIMIT1;
-- 看到是一个 DELETE FROM logs WHERE created_at < '2023-01-01'
-- 删了 5000 万行

根因：应用层定时任务，每个月清理一次老日志，写成了大事务。

修复：

-- 杀事务
KILL12345;

-- 改成分批删除
-- 应用代码：
batch_size = 10000
while True:
DELETEFROMlogsWHERE created_at < '2023-01-01'LIMIT batch_size
if affected_rows == 0: break

复盘：

• 业务层定时清理必须分批。
• 长事务监控告警。
• 上线前 review 定时任务的 SQL。

案例 2：并行复制 Worker 死锁

现象：5.7.22 启用 WRITESET 并行后，从库报 Last_SQL_Error: Worker 7 failed executing transaction。

原因：WRITESET 模式并行可能让 worker 之间的 binlog 应用顺序错乱。某些场景下会触发死锁。

修复：

# 改用 LOGICAL_CLOCK
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 8
# 配合 commit_order
slave_preserve_commit_order = ON

复盘：

• 并行复制不是越激进越好。
• WRITESET_SESSION（8.0.27+）更安全。
• 5.7.22+ WRITESET 有时不如 LOGICAL_CLOCK 稳。

案例 3：MySQL 8.0 升级后从库延迟飙升

现象：升级 5.7 → 8.0 后，从库延迟从 1s 涨到 30s。

根因：8.0 redo log 格式变了，relay log 重写增加了 IO。

修复：

# 调整 redo log 大小
innodb_redo_log_capacity = 4G  # 8.0 推荐，单位字节

# 调整 page cleaner
innodb_page_cleaners = 4

# 调整 IO capacity
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000

复盘：8.0 redo log 配置和 5.7 不同，升级后要重新调优。

实战九：高可用与切换

MHA（Master High Availability）

# 安装 MHA Manager
yum install mha4mysql-manager

# 配置
cat > /etc/mha.cnf <<EOF
[server default]
manager_workdir=/var/log/masterha/app1
manager_log=/var/log/masterha/app1/manager.log
master_ip_failover_script=/usr/local/bin/master_ip_failover
ssh_user=root
user=mha
password=xxx
repl_user=repl
repl_password=xxx
ping_interval=1

[server1]
hostname=10.0.0.1
candidate_master=1

[server2]
hostname=10.0.0.2
no_master=1
EOF

# 启动
masterha_manager --conf=/etc/mha.cnf

MHA 自动检测主库故障，切换到从库。

Orchestrator

# 安装
docker run -d --name orchestrator 
  -p 3000:3000 
  githubcode/orchestrator:latest

# 启动后通过 Web UI 管理

Orchestrator 相比 MHA 的优势：

• Web UI 可视化。
• 支持复杂拓扑（双主、级联）。
• 重构（relocate）复制关系。
• 验证主从延迟。

ProxySQL

# /etc/proxysql.cnf
datadir="/var/lib/proxysql"
admin_variables:
admin_credentials:"admin:admin"
mysql_ifaces:"0.0.0.0:6032"

mysql_servers:
-{address:"10.0.0.1",port:3306,hostgroup:0}# 写主
-{address:"10.0.0.2",port:3306,hostgroup:1}# 读从1
-{address:"10.0.0.3",port:3306,hostgroup:1}# 读从2

mysql_users:
-{username:"app",password:"xxx",default_hostgroup:0}

mysql_query_rules:
-{rule_id:1,match_pattern:"^SELECT .* FOR UPDATE$",destination_hostgroup:0}
-{rule_id:2,match_pattern:"^SELECT .*",destination_hostgroup:1}

mysql_replication_hostgroups:
-{writer_hostgroup:0,reader_hostgroup:1,comment:"master-slave"}

ProxySQL 能在主库宕机时自动切换，应用无感知。

实战十：最佳实践清单

• [ ] binlog_format = ROW（5.7.7+ 默认）。
• [ ] gtid_mode = ON，enforce_gtid_consistency = ON。
• [ ] sync_binlog = 1（金融、订单场景必须）。
• [ ] innodb_flush_log_at_trx_commit = 1。
• [ ] 半同步复制（AFTER_SYNC）。
• [ ] 从库 slave_parallel_workers 设 8~16，type 用 LOGICAL_CLOCK / WRITESET_SESSION。
• [ ] slave_preserve_commit_order = ON。
• [ ] read_only = ON，super_read_only = ON。
• [ ] 主从监控（Seconds_Behind_Master + replication_applier_status）。
• [ ] 心跳表验证（业务级延迟监控）。
• [ ] 业务层大事务拆解。
• [ ] 定时任务分批执行。
• [ ] 备份在从库做（避免主库 IO 抖动）。
• [ ] pt-table-checksum 定期校验一致性。
• [ ] MHA / Orchestrator 部署高可用。
• [ ] ProxySQL / MySQL Router 部署读写分离。
• [ ] binlog 保留 7~14 天。
• [ ] relay log 自动清理。
• [ ] 复制用户最小权限。
• [ ] 升级前 mysql_upgrade + 全量备份。

实战十一：常见问题 FAQ

Q1：Seconds_Behind_Master 是 0 但业务读到老数据？

A：这个指标是 TIMESTAMP 字段计算出来的，binlog 没这个字段会显示 0。建议用 performance_schema.replication_applier_status 或业务层心跳表。

Q2：并行复制多少个 worker 合适？

A：4~32 之间，取决于机器配置。建议先设 8，观察几天后调整。

Q3：半同步复制性能损失多少？

A：5%~10% 的 commit 延迟。AFTER_SYNC 比 AFTER_COMMIT 略慢。配合并行复制能弥补。

Q4：5.7 升级 8.0 有什么注意？

A：(1) sql_mode 严格化；(2) 用户认证从 mysql_native_password 改 caching_sha2_password；(3) 一些 SQL 不再支持（如某些 partition 语法）；(4) 升级前 mysql_upgrade 跑一遍。

Q5：MGR 比半同步好在哪？

A：MGR 是基于 Paxos 的强一致复制，能容忍 N/2 节点故障，数据零丢失。半同步至少需要 1 个从库 ack。性能上 MGR 略差。

Q6：主从延迟多少需要处理？

A：取决于业务容忍度。1 秒以内：监控即可。1~10 秒：观察。10~60 秒：介入排查。60 秒+：紧急处理。

Q7：能不能禁用 binlog？

A：能，但不推荐。禁用 binlog 就没复制、没 PIT 恢复、没审计。生产环境必须开。

Q8：MySQL 5.7 怎么调并行复制？

A：

slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 8
slave_preserve_commit_order = ON
binlog_transaction_dependency_tracking = WRITESET  # 5.7.22+
transaction_write_set_extraction = XXHASH64

Q9：怎么用 GTID 做主从切换？

A：找到最新 GTID 的从库，STOP SLAVE; RESET SLAVE ALL; 提升为主，其他从库 CHANGE MASTER TO ... MASTER_AUTO_POSITION=1。

Q10：pt-online-schema-change 在从库延迟大时怎么办？

A：pt-osc / gh-ost 在切表的瞬间（rename 表）会有一次短延迟。监控 lag 接近阈值时先暂停工具。

Q11：主从切换后 binlog position 怎么看？

A：提升为主后用 SHOW MASTER STATUS 看当前 binlog。其他从库指向新主时用 GTID 自动找位置（MASTER_AUTO_POSITION=1）。

Q12：复制中断后怎么恢复？

A：先看 Last_SQL_Error / Last_IO_Error。IO 错误重连，SQL 错误判断是否能 skip。实在不行重新搭建复制。

Q13：5.6 还能用吗？

A：5.6 已经过了 Oracle 官方支持期。生产建议 5.7+ 或 8.0。

Q14：MySQL 8.0 vs MariaDB 10.x？

A：MariaDB 在某些场景性能更好（连接池、列存储），但生态比 MySQL 小。多数生产用 MySQL 8.0。

总结

主从复制延迟是 MySQL 运维的核心能力。理解原理（binlog / relay log / SQL 线程），搭建并行复制（LOGICAL_CLOCK / WRITESET_SESSION），监控关键指标（Seconds_Behind_Master / replication_applier_status / 业务心跳），调优业务层（拆大事务、读写分离、路由控制），处理异常（skip counter、pt-table-sync、MHA 切换）。

核心心法：

• 监控要有两个维度：复制状态 + 业务延迟。
• 并行复制是必备，5.7+ LOGICAL_CLOCK 起步。
• 业务层拆大事务、定时分批。
• 读写分离用 ProxySQL/MySQL Router 路由。
• 高可用上 MHA/Orchestrator。
• 升级前 mysql_upgrade + 备份。

把这套做扎实，主从延迟问题基本能控制住。

附录：常用命令速查

-- 主库
SHOWMASTERSTATUS;
SHOWBINLOGEVENTSIN'mysql-bin.000123'LIMIT10;
SHOWBINARYLOGS;
PURGEBINARYLOGSBEFORE'2024-01-01';
PURGEBINARYLOGSTO'mysql-bin.000123';

-- 从库
SHOWSLAVESTATUSG
SHOW REPLICA STATUSG
SHOWSLAVEHOSTS;
SHOW REPLICAS;
STARTSLAVE;
START REPLICA;
STOPSLAVE;
STOP REPLICA;
RESETSLAVE;
RESETSLAVEALL;
RESET REPLICA;
RESET REPLICA ALL;

-- 5.6/5.7
CHANGEMASTERTO
  MASTER_HOST='10.0.0.1',
  MASTER_USER='repl',
  MASTER_PASSWORD='xxx',
  MASTER_AUTO_POSITION=1;

-- 8.0.22+
CHANGEREPLICATIONSOURCETO
  SOURCE_HOST='10.0.0.1',
  SOURCE_USER='repl',
  SOURCE_PASSWORD='xxx',
  SOURCE_AUTO_POSITION=1;

-- 跳过错误
STOPSLAVE;
SETGLOBAL sql_slave_skip_counter = 1;
STARTSLAVE;

-- 启用并行复制
SETGLOBAL slave_parallel_type = 'LOGICAL_CLOCK';
SETGLOBAL slave_parallel_workers = 8;
STOPSLAVE;
STARTSLAVE;

-- 半同步
INSTALLPLUGIN rpl_semi_sync_master SONAME'semisync_master.so';
INSTALLPLUGIN rpl_semi_sync_slave SONAME'semisync_slave.so';
SETGLOBAL rpl_semi_sync_master_enabled = 1;
SETGLOBAL rpl_semi_sync_slave_enabled = 1;

-- GTID
SELECT @@global.gtid_executed;
SELECT @@global.gtid_purged;

-- 跳过特定错误
pt-slave-restart --error-numbers=1062 --host=10.0.0.2

-- performance_schema
SELECT * FROM performance_schema.replication_connection_statusG
SELECT * FROM performance_schema.replication_applier_statusG
SELECT * FROM performance_schema.replication_applier_status_by_worker;

-- 升级
mysql_upgrade -u root -p

附录：关键参数速查

不同版本字段可能略有差异，以实际版本为准。

附录：binlog 工具

# mysqlbinlog 查看
mysqlbinlog --base64-output=decode-rows -v mysql-bin.000123 | less

# 只看某个库的
mysqlbinlog --database=mydb mysql-bin.000123

# 从某个位置开始
mysqlbinlog --start-position=1234 mysql-bin.000123

# 从某个时间开始
mysqlbinlog --start-datetime='2024-01-01 10:00:00' mysql-bin.000123

# 导出 SQL
mysqlbinlog --start-position=1234 --stop-position=5678 mysql-bin.000123 > /tmp/events.sql

附录：GTID 状态对比

-- 主库
SELECT @@global.gtid_executed;
-- 3e11fa47-71ca-11e1-9e33-c80aa9429562:1-1000

-- 从库
SELECT @@global.gtid_executed;
-- 3e11fa47-71ca-11e1-9e33-c80aa9429562:1-995

-- 差 5 个事务，正常。

附录：典型复制架构

一主一从

主库（10.0.0.1）→ 从库（10.0.0.2）

一主多从

主库（10.0.0.1）→ 从库1（10.0.0.2）
              → 从库2（10.0.0.3）
              → 从库3（10.0.0.4）

级联

主库（10.0.0.1）→ 中继从库（10.0.0.2）→ 从库A（10.0.0.3）
                                  → 从库B（10.0.0.4）

双主（不推荐）

主库A（10.0.0.1）↔ 主库B（10.0.0.2）

需要业务层处理写冲突、自增 ID。

附录：典型延迟优化效果

附录：参考资源

• MySQL 官方复制文档：https://dev.mysql.com/doc/refman/8.0/en/replication.html
• MySQL 官方 GTID 文档：https://dev.mysql.com/doc/refman/8.0/en/replication-gtids.html
• Percona Toolkit：https://www.pergut.com/software/percona-toolkit/
• gh-ost：https://github.com/github/gh-ost
• MHA：https://github.com/yoshinorim/mha4mysql-manager
• Orchestrator：https://github.com/openarkcode/orchestrator
• ProxySQL：https://proxysql.com/
• MySQL Router：https://dev.mysql.com/doc/mysql-router/8.0/en/