正文

MySQL主从延迟导致数据不一致谁背锅？从binlog原理到实时校验一键排查修复

/0 浏览量
0904

说实话，凌晨三点被报警电话吵醒的时候，你最不想听到的就是DBA说“数据对不上”。

我见过太多团队因为主从延迟背锅打架：业务方说“为什么我刚才在主库写的东西，从库读出来是旧的？”，DBA说“这是网络抖动正常的，你业务逻辑有问题”，运维说“服务器资源够用啊，为什么慢？”……最后往往是大家互相甩锅，问题却还没解决。

今天咱们不聊虚的，直接把这个坑扒开看看，到底是谁的责任，以及怎么一次性把问题搞定。

先搞清楚：MySQL主从复制到底是怎么工作的？

在你吐槽延迟之前，得先知道MySQL主从复制的本质是什么。很多人以为数据是实时同步的，其实不是。

MySQL的主从复制是一个异步的三步走流程：

第一步：主库写binlog

当你的应用往主库（Master）写入一条数据时，MySQL的存储引擎（比如InnoDB）会先把这个事务写到redo log（用于崩溃恢复），然后记录binlog（用于主从复制）。binlog是二进制格式的逻辑日志，记录的是“SQL语句”或者“行变更”。

-- 假设你执行了这条语句
INSERT INTO users (name, email) VALUES ('张三', 'zhangsan@example.com');

-- 主库会生成一条binlog事件，大致内容如下（伪代码示意）：
BINLOG_EVENT:
  - Event Type: WRITE_ROWS_EVENT
  - Table: test.users
  - Row Data: [name=张三, email=zhangsan@example.com]
  - Timestamp: 1719000000
  - Server ID: 1  (主库ID)

第二步：主库把binlog推给从库

主库有一个专门的事件叫Binlog Dump Thread。它的工作很简单：监听主库的binlog，一旦有新的事件产生，就把这部分binlog发送给从库。

注意，这里是推送，不是主库主动问“你同步了吗？”，而是主库只管写，写完就推。

第三步：从库回放binlog

从库（Slave/Replica）收到binlog后，会保存到本地的relay log（中继日志）中，然后由SQL Thread（SQL线程）负责解析并执行这些SQL，最终应用到从库的数据库中。

主库(Master)                     从库(Slave)
     |                               |
     |--- binlog (网络传输) -------->|--- relay log (落盘)
     |                               |
     |                               |--- SQL Thread (回放) --> 执行SQL
     |                               |      |
     |                               |      v
     |                               |   从库数据更新

关键点来了： 这个过程是异步的。主库写完了binlog就返回给应用“写入成功”，根本不管从库有没有收到、有没有执行完。这就是延迟产生的根源。

为什么会有延迟？谁该背锅？

延迟不是单一原因造成的，我给你拆解几个最常见的场景，你看看你家是哪一种。

场景一：主库写得多，从库跟不上

这是最经典的情况。想象一下，主库每秒处理1000个写入事务，每个事务都很小，0.1毫秒就完了。但是从库在回放的时候，因为某些原因（比如慢查询、锁竞争），每秒只能回放500个事务。

谁背锅？ 从库性能不够。

-- 检查一下从库的复制状态
SHOW SLAVE STATUS\G

-- 你会看到这两个关键指标：
Seconds_Behind_Master: 15  -- 落后主库15秒
Last_SQL_Error:          0

如果Seconds_Behind_Master持续偏高，说明从库回放速度跟不上主库写入速度。这时候你可以进一步排查从库的慢查询：

-- 开启慢查询日志，看看从库回放时卡在哪些SQL上
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.1;  -- 0.1秒以上的都记录

-- 查看正在执行的SQL
SHOW PROCESSLIST;

-- 看看有没有锁等待
SELECT * FROM information_schema.innodb_trx;

场景二：大事务导致从库卡顿

有些业务场景会一次性插入或更新几十万条数据，比如数据迁移、批量导入。这种大事务在主库上可能几秒钟就完了，但在从库上，因为要逐个执行这些SQL，可能就要几分钟甚至更久。

谁背锅？ 业务代码设计问题。

-- 典型的坏味道：大事务
START TRANSACTION;
FOR i IN 1..100000 LOOP
    INSERT INTO orders (...) VALUES (...);
END LOOP;
COMMIT;

主库上InnoDB可以并行处理这些插入（取决于配置），但从库的SQL Thread是单线程回放的。一个大事务会阻塞整个从库的复制。

-- 检查从库是否卡在大事务上
SELECT * FROM information_schema.innodb_trx 
WHERE TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) > 10;

-- 检查relay log的进度
SHOW SLAVE STATUS\G
-- 看 Relay_Master_Log_File 和 Exec_Master_Log_Pos

场景三：网络抖动或带宽不足

如果主库和从库不在同一个机房，或者网络带宽有限，binlog传输本身就会成为瓶颈。特别是当主库写入量很大时，生成的binlog体积可观，网络传输延迟就会很明显。

谁背锅？ 基础设施问题，或者架构设计问题。

# 检查主从之间的网络延迟
ping -c 10 <slave_ip>

# 检查网络带宽使用情况
iftop -i eth0

# 检查binlog传输是否慢
SHOW MASTER STATUS\G
-- 看 Binlog_Do_DB 和 Binlog_Ignore_DB 配置是否合理

场景四：从库被用于查询，抢了资源

这是很多团队的通病：为了省钱，从库既做复制，又对外提供读服务。当查询压力大的时候，CPU、IO、连接数都被查询占满了，复制线程自然就跑不快了。

谁背锅？ 架构设计问题，或者资源分配问题。

-- 检查从库的负载情况
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Threads_running';

-- 检查CPU和IO使用率
-- 在Linux上执行
top -p $(pgrep mysqld)
iostat -x 1 5

场景五：时钟不同步

这个比较隐蔽，但影响很大。MySQL的Seconds_Behind_Master是通过比较主库binlog的时间戳和从库当前时间来计算的。如果主库和从库的时钟不同步，这个值就会不准。

谁背锅？ 运维问题。

# 检查主库和从库的时间同步
chronyc sources -v
date
ntpstat

怎么实时校验主从数据一致性？

光知道延迟原因还不够，你得能发现数据不一致。有时候延迟只是暂时的，但数据不一致可能是永久性的。

这里我给你介绍几个主流方案：

方案一：pt-table-checksum（经典方案）

Percona Toolkit里的pt-table-checksum是业界最常用的工具。它的工作原理是在主库上执行特定的校验SQL，生成校验和，然后在从库上执行同样的SQL，对比结果。

# 安装Percona Toolkit
yum install percona-toolkit -y
# 或者
apt-get install percona-toolkit

# 基本用法
pt-table-checksum \
  --host=127.0.0.1 \
  --user=repl_user \
  --password=repl_password \
  --database=mydb \
  --tables=mytable \
  --create-replicate-table \
  --replicate=percona.checksums

# 参数解释：
# --create-replicate-table: 自动创建校验表
# --replicate: 校验结果存储的表

工作原理：

-- pt-table-checksum在主库执行的SQL大致如下：
SELECT CRC32(CONCAT_WS(',', id, name, email, UPDATE_TIME)) 
FROM users 
WHERE id BETWEEN 1000 AND 2000;

-- 这个结果会被写入checksum表
INSERT INTO percona.checksums 
(table_schema, table_name, chunk_index, lower_boundary, upper_boundary, 
 this_crc, this_cnt, master_crc, master_cnt) 
VALUES ('mydb', 'users', 'PRIMARY', 1000, 2000, 'abc123', 100, NULL, NULL);

-- 然后从库会执行同样的SQL，对比校验和

优点： 成熟稳定，支持分片校验，对主库影响小。 缺点： 只能检测不一致，不能自动修复；大表校验时间长。

方案二：mysqlrplsync（MySQL官方工具）

MySQL官方的Replication Tools里有一个mysqlrplsync工具，专门用来做主从一致性校验。

# 安装MySQL Shell
yum install mysql-shell -y

# 使用mysqlrplsync
mysqlrplsync --master=root:password@master_host \
             --slave=root:password@slave_host \
             --database=mydb

方案三：自研实时校验（适合大规模场景）

如果你的数据量很大，pt-table-checksum太慢了，可以考虑自研一个实时校验系统。核心思路是：

  1. 监听binlog：使用如Maxwell、Canal等工具监听主库的binlog
  2. 计算校验和：对每条变更计算校验和
  3. 从库比对：在从库上同样计算校验和，实时对比
# 伪代码示例
import canal.client
import hashlib

def calculate_checksum(row):
    """计算单行数据的校验和"""
    data = str(row['values'])
    return hashlib.md5(data.encode()).hexdigest()

def check_consistency(master_event, slave_event):
    """对比主从事件"""
    master_crc = calculate_checksum(master_event)
    slave_crc = calculate_checksum(slave_event)
    
    if master_crc != slave_crc:
        log_error(f"数据不一致! 表={master_event['table']}, 主库CRC={master_crc}, 从库CRC={slave_crc}")
        return False
    return True

# 监听主库binlog
client = canal.client.Client()
client.connect('master_host', 11111)
client.subscribe('mydb')

while True:
    messages = client.get(100)
    for message in messages:
        for entry in message.entries:
            if entry.entry_type == canal protocal.EntryType.ROWDATA:
                # 对比主从数据
                check_consistency(entry.master_event, entry.slave_event)

方案四：使用监控工具（ Prometheus + Grafana ）

你可以把复制延迟指标暴露给Prometheus，然后用Grafana做可视化监控，设置告警。

# Prometheus配置文件片段
scrape_configs:
  - job_name: 'mysql_replication'
    static_configs:
      - targets: ['slave_host:9104']  # mysql_exporter端口

-- 在从库上执行的查询，用于导出复制状态
SELECT 
  VARIABLE_VALUE AS Seconds_Behind_Master
FROM information_schema.global_status 
WHERE VARIABLE_NAME = 'Seconds_Behind_Master';

# Grafana告警规则
groups:
  - name: replication
    rules:
      - alert: ReplicationDelay
        expr: mysql_slave_seconds_behind_master > 60
        for: 5m
        labels:
          severity: critical
        annotations:
          summary: "MySQL从库延迟超过60秒"
          description: "从库延迟当前为 {{ $value }} 秒"

发现问题后，怎么一键修复？

校验发现了数据不一致，接下来就是修复。不同场景有不同的修复策略。

策略一：跳过错误事务（谨慎使用）

如果从库因为某个SQL执行失败而停止复制，你可以选择跳过这个事务。

-- 停止从库复制
STOP SLAVE;

-- 跳过下一个事务
SET GLOBAL sql_slave_skip_counter = 1;

-- 重新启动复制
START SLAVE;

-- 检查状态
SHOW SLAVE STATUS\G

注意： 跳过事务可能导致数据进一步不一致，只适用于你知道这个事务可以安全跳过的场景（比如测试数据、临时表操作等）。

策略二：重新同步（最彻底）

如果数据不一致很严重，最稳妥的办法是重新同步。

# 1. 在主库上锁定表，确保数据一致
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS\G  -- 记录binlog文件和位置

# 2. 备份主库数据
mysqldump -u root -p --all-databases --single-transaction --flush-logs > backup.sql

# 3. 解锁主库
UNLOCK TABLES;

# 4. 在从库上停止复制
STOP SLAVE;

# 5. 清空从库数据并恢复
RESET SLAVE ALL;
source backup.sql;

# 6. 重新配置主从关系
CHANGE MASTER TO
  MASTER_HOST='master_host',
  MASTER_USER='repl_user',
  MASTER_PASSWORD='password',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=1234;

# 7. 启动复制
START SLAVE;

# 8. 验证
SHOW SLAVE STATUS\G

策略三：GTID模式下的自动修复

如果你使用了GTID（全局事务标识符），MySQL会自动跟踪已经执行的事务，重新同步会更容易。

-- 在从库上执行
STOP SLAVE;
RESET SLAVE ALL;

-- 使用GTID方式重新同步
CHANGE MASTER TO
  MASTER_HOST='master_host',
  MASTER_USER='repl_user',
  MASTER_PASSWORD='password',
  MASTER_AUTO_POSITION = 1;

START SLAVE;

策略四：使用pt-table-sync自动修复

Percona Toolkit还提供了pt-table-sync工具，可以自动修复不一致的数据。

# 语法：pt-table-sync --execute 主库连接信息 从库连接信息
pt-table-sync \
  --execute \
  --print \
  --replicate percona.checksums \
  h=master_host,u=root,p=password \
  hs=slave_host,u=root,p=password

# 参数解释：
# --execute: 真正执行修复（不加这个参数只会打印要执行的SQL）
# --print: 打印要执行的SQL（调试用）
# --replicate: 指定pt-table-checksum生成的校验表

工作原理： pt-table-sync会读取pt-table-checksum生成的校验表，找出主从不一致的行，然后生成修复SQL（INSERT/UPDATE/DELETE），应用到从库上。

-- 生成的修复SQL示例
REPLACE INTO `mydb`.`users` (`id`, `name`, `email`) VALUES (1, '张三', 'zhangsan@example.com');
DELETE FROM `mydb`.`users` WHERE `id` = 2;
UPDATE `mydb`.`users` SET `email` = 'new_email@example.com' WHERE `id` = 3;

如何预防？架构层面的最佳实践

修好了问题只是治标，预防问题才是治本。我给你几个架构层面的建议：

1. 启用半同步复制（Semi-Synchronous Replication）

标准的主从复制是异步的，主库不保证从库收到数据。半同步复制要求至少一个从库确认收到binlog后，主库才返回写入成功。

-- 在主库上安装插件
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 = 1000;  -- 1秒超时

-- 在从库上安装插件
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

-- 启用
SET GLOBAL rpl_semi_sync_slave_enabled = 1;

-- 重启从库的IO线程
STOP SLAVE IO_THREAD;
START SLAVE IO_THREAD;

优点： 数据安全性更高，主库宕机时从库至少有一个是完整的。 缺点： 写入延迟会增加，因为要等待从库确认。

2. 使用并行复制

MySQL 5.7+支持并行复制，从库可以多个SQL Thread并发回放，大大提高同步速度。

-- 查看从库的并行复制配置
SHOW VARIABLES LIKE 'slave_parallel%';

-- 推荐配置
SET GLOBAL slave_parallel_type = 'LOGICAL_CLOCK';  -- 基于事务的并行复制
SET GLOBAL slave_parallel_workers = 4;  -- 4个并行线程

原理： MySQL会根据事务的GTID或者数据库名，将不同的事务分发到不同的线程并行执行。

3. 避免大事务

这是业务代码层面的优化。把大事务拆分成小批次：

-- 错误的做法：一次插入10万条
INSERT INTO orders (...) VALUES (...), (...), ...;  -- 10万条

-- 正确的做法：分批插入
FOR i IN 1..100 LOOP
    INSERT INTO orders (...) VALUES (...), (...);  -- 每批1000条
    COMMIT;
END LOOP;

# Python代码示例
batch_size = 1000
for i in range(0, total_count, batch_size):
    batch = data[i:i+batch_size]
    cursor.executemany("INSERT INTO orders (...) VALUES (...)", batch)
    connection.commit()

4. 从库不承载写业务

确保从库只用于读查询，不要有任何写入操作。如果从库被意外写入，会导致主从不一致。

-- 展开阅读全文 --