在 MySQL 8.0 主从中用副本锁和大事务制造复制延迟，练习 SecondsBehindSource 与并行复制

演练目标

• 用 Docker Compose 搭建 GTID 主从，确认复制线程和初始数据一致。
• 在副本上持有表锁并制造大批量写入，观察 Seconds_Behind_Source、relay log 和 processlist。
• 区分源端写入压力、单线程应用、大事务和副本锁等待，掌握恢复与验证顺序。

环境准备

隔离边界：只使用本机 Docker Compose 的 lab-mysql 项目，所有客户端命令都在容器内执行，不映射宿主机端口；不要连接或修改任何生产 MySQL。需要 Docker Compose v2，预留约 1 GB 内存。

docker version --format '{{.ServerVersion}}' && docker compose version
mkdir -p /tmp/lab-mysql
cat > /tmp/lab-mysql/docker-compose.yml <<'EOF'
name: lab-mysql
x-mysql: &mysql
  image: mysql:8.0.39
  environment:
    MYSQL_ROOT_PASSWORD: rootpass
services:
  source:
    <<: *mysql
    container_name: lab-mysql-source
    command: ["--server-id=1", "--log-bin=mysql-bin", "--binlog-format=ROW", "--gtid-mode=ON", "--enforce-gtid-consistency=ON"]
  replica:
    <<: *mysql
    container_name: lab-mysql-replica
    command: ["--server-id=2", "--log-bin=mysql-bin", "--binlog-format=ROW", "--gtid-mode=ON", "--enforce-gtid-consistency=ON", "--read-only=ON"]
EOF
docker compose -f /tmp/lab-mysql/docker-compose.yml up -d
for i in $(seq 1 60); do
  docker compose -f /tmp/lab-mysql/docker-compose.yml exec -T source mysql -uroot -prootpass -e 'SELECT 1' >/dev/null 2>&1 || { sleep 2; continue; }
  docker compose -f /tmp/lab-mysql/docker-compose.yml exec -T replica mysql -uroot -prootpass -e 'SELECT 1' >/dev/null 2>&1 && break
  sleep 2
done

初始化源端表、复制账号，并在副本上启动 GTID 复制：

docker compose -f /tmp/lab-mysql/docker-compose.yml exec -T source mysql -uroot -prootpass <<'SQL'
CREATE DATABASE IF NOT EXISTS lab;
CREATE TABLE IF NOT EXISTS lab.events (id BIGINT AUTO_INCREMENT PRIMARY KEY, payload VARCHAR(255) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE USER IF NOT EXISTS 'repl'@'%' IDENTIFIED BY 'replpass';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
SQL
docker compose -f /tmp/lab-mysql/docker-compose.yml exec -T replica mysql -uroot -prootpass <<'SQL'
CHANGE REPLICATION SOURCE TO SOURCE_HOST='source', SOURCE_USER='repl', SOURCE_PASSWORD='replpass', SOURCE_AUTO_POSITION=1, GET_SOURCE_PUBLIC_KEY=1;
START REPLICA;
SQL
for i in $(seq 1 30); do
  docker compose -f /tmp/lab-mysql/docker-compose.yml exec -T replica mysql -uroot -prootpass -e 'SHOW REPLICA STATUS\G' 2>/dev/null | awk '/Replica_IO_Running: Yes/{io=1} /Replica_SQL_Running: Yes/{sql=1} END{exit !(io&&sql)}' && break; sleep 2
done

复制线程都为 Yes 即表示 GTID 复制已启动。MySQL 8.0.22+ 的 SHOW REPLICA STATUS 输出 Seconds_Behind_Source，旧版本文档和旧输出中写作 Seconds_Behind_Master。

注入故障

在副本上开一个会话持有 lab.events 的写锁，阻塞复制 SQL 线程，然后在源端执行 5 万行的大事务：

docker compose -f /tmp/lab-mysql/docker-compose.yml exec -T replica mysql -uroot -prootpass -e 'LOCK TABLES lab.events WRITE; SELECT SLEEP(120);' > /tmp/lab-mysql/lock.log 2>&1 &
sleep 2
docker compose -f /tmp/lab-mysql/docker-compose.yml exec -T replica mysql -uroot -prootpass -N -e "SELECT ID FROM information_schema.PROCESSLIST WHERE INFO LIKE 'SELECT SLEEP(120)%' LIMIT 1" > /tmp/lab-mysql/lock_id
cat /tmp/lab-mysql/lock_id
docker compose -f /tmp/lab-mysql/docker-compose.yml exec -T source mysql -uroot -prootpass -e "SET SESSION cte_max_recursion_depth=100000; INSERT INTO lab.events (payload) WITH RECURSIVE seq AS (SELECT 1 AS n UNION ALL SELECT n+1 FROM seq WHERE n < 50000) SELECT CONCAT('event-', n) FROM seq;"

观察现象

sleep 5
docker compose -f /tmp/lab-mysql/docker-compose.yml exec -T replica mysql -uroot -prootpass -e 'SHOW REPLICA STATUS\G' | grep -E 'Replica_IO_Running|Replica_SQL_Running|Seconds_Behind_Source|Relay_Log_File|Relay_Log_Pos|Exec_Source_Log_Pos|Read_Source_Log_Pos'
docker compose -f /tmp/lab-mysql/docker-compose.yml exec -T replica mysql -uroot -prootpass -e 'SELECT ID, COMMAND, TIME, STATE, LEFT(INFO,80) FROM information_schema.PROCESSLIST WHERE COMMAND <> "Sleep"\G'
docker compose -f /tmp/lab-mysql/docker-compose.yml exec -T source mysql -uroot -prootpass -e 'SHOW MASTER STATUS\G'

预期 Replica_IO_Running 和 Replica_SQL_Running 仍为 Yes，但 Seconds_Behind_Source 明显大于 0；Read_Source_Log_Pos 已经追上源端，而 Exec_Source_Log_Pos/relay log 应用位置落后，说明瓶颈在 SQL 线程。副本 processlist 中能看到复制线程处于 Waiting for table ... lock 或 metadata lock 等待。源端 SHOW MASTER STATUS 显示 binlog 文件和 GTID 已推进；MySQL 8.2+ 可用 SHOW BINARY LOG STATUS 替代。

判断与定位

1. 先写假设：源端写入突发、单线程应用、大事务、副本锁等待、网络延迟，还是副本磁盘 IO 不足。先看 IO 线程是否追上，再看 SQL 线程位置。
2. 用 SHOW REPLICA STATUS\G 对比 Read_Source_Log_Pos 与 Exec_Source_Log_Pos，并看 Relay_Log_File、Relay_Log_Pos、Seconds_Behind_Source。IO 线程正常但 SQL 线程落后，问题在应用侧；两个位置都落后，先查网络和源端 binlog 读取。
3. 在副本上查 information_schema.PROCESSLIST 和 performance_schema，确认 SQL 线程是否在等表锁、行锁或磁盘 IO。本实验的 LOCK TABLES ... WRITE 会直接阻塞应用。
4. 容易误判：看到 Replica_SQL_Running: Yes 就认为复制正常。线程处于运行状态也可能卡在锁等待或大事务上；另一个误判是只看 Seconds_Behind_Source=0，它在复制空闲或刚启动时可能暂时为 0，必须结合 relay log 位置和实际行数校验。Seconds_Behind_Source=NULL 时先确认 SQL 线程和心跳，不要用跳过事务掩盖问题。
5. 大事务在源端是一个事务，在副本上可能长时间不可分割地应用；即使开启并行复制，单个大事务也主要落在一个 worker 上。replica_parallel_workers 只能提高可并行事务的吞吐，不能解决锁等待或单表热点。

修复

杀掉副本上的锁会话，让 SQL 线程继续应用，等待延迟归零并核对行数：

docker compose -f /tmp/lab-mysql/docker-compose.yml exec -T replica mysql -uroot -prootpass -e "KILL $(cat /tmp/lab-mysql/lock_id);"
for i in $(seq 1 60); do lag=$(docker compose -f /tmp/lab-mysql/docker-compose.yml exec -T replica mysql -uroot -prootpass -e 'SHOW REPLICA STATUS\G' | awk '/Seconds_Behind_Source:/ {print $2; exit}'); [ "$lag" = "0" ] && break; sleep 2; done
echo "lag=$lag"
docker compose -f /tmp/lab-mysql/docker-compose.yml exec -T source mysql -uroot -prootpass -N -e 'SELECT COUNT(*) FROM lab.events;'
docker compose -f /tmp/lab-mysql/docker-compose.yml exec -T replica mysql -uroot -prootpass -N -e 'SELECT COUNT(*) FROM lab.events;'

预期 lag=0，源端和副本都返回 50000。生产中可评估开启并行复制：

docker compose -f /tmp/lab-mysql/docker-compose.yml exec -T replica mysql -uroot -prootpass -e "SET GLOBAL replica_parallel_workers=4; STOP REPLICA SQL_THREAD; START REPLICA SQL_THREAD;"

并行复制需要事务之间具备并行度；单大事务、单表热点和锁等待仍需从业务拆分、批量大小和副本负载入手。修复后持续观察延迟、relay log 增长和复制错误，不要只确认一次 lag=0。

清理与回滚

docker compose -f /tmp/lab-mysql/docker-compose.yml down -v --remove-orphans
rm -rf /tmp/lab-mysql

只删除本实验的 Compose 项目和数据库卷；不要对生产副本执行 KILL、down -v 或跳过复制事务。

复盘

• 为什么 Replica_SQL_Running: Yes 仍可能表示复制延迟很高？
• 并行复制能缓解哪类延迟，不能解决哪类延迟？
• 现有监控能否区分源端写入压力、副本锁等待和网络延迟？

延伸阅读

• MySQL 复制延迟
• MySQL 复制中断
• MySQL Router
• MySQL 速查

读完自测

1. Seconds_Behind_Source 很大但 IO 线程已追上，瓶颈通常在哪一侧？
2. 为什么开启并行复制后，单个大事务仍可能造成明显延迟？
3. 修复延迟后，为什么要同时核对 relay log 位置和源/副本行数？