mysql主从为什么会不同步

发布时间：2022-01-05 16:38:16 作者：小新
来源：亿速云阅读：285

# MySQL主从为什么会不同步

## 引言

MySQL主从复制（Replication）是数据库高可用、读写分离和负载均衡的基础架构。然而在实际生产环境中，主从不同步（Replication Lag）是DBA经常遇到的棘手问题。本文将深入剖析主从不同步的7大类原因，包含原理分析、典型场景和解决方案，帮助开发者彻底理解并解决这一问题。

---

## 一、网络延迟问题

### 1.1 问题原理
主库的binlog需要通过网络传输到从库，当网络出现以下情况时：
- 物理带宽不足
- 跨机房/跨地域传输
- 网络设备（交换机、路由器）故障
- TCP重传率过高

### 1.2 典型表现
- Seconds_Behind_Master指标波动
- 从库I/O线程状态为"Waiting for master to send event"
- show slave status显示IO线程延迟

### 1.3 解决方案
```sql
-- 监控网络延迟（示例）
SELECT * FROM sys.metrics WHERE variable_name LIKE '%network%';

升级网络带宽（建议主从间≥1Gbps）
使用专线或VPN保证传输质量
调整复制压缩参数（需权衡CPU消耗）

[mysqld]
slave_compressed_protocol = 1

二、硬件性能差异

2.1 常见硬件瓶颈

硬件组件	主库配置	从库配置	影响程度
CPU	16核	4核	★★★★★
磁盘	NVMe SSD	HDD	★★★★☆
内存	64GB	8GB	★★★★☆

2.2 性能优化方案

磁盘优化：


innodb_flush_method = O_DIRECT
innodb_io_capacity = 2000

CPU优化：


SET GLOBAL slave_parallel_workers = 8;

内存优化：


innodb_buffer_pool_size = 12G  # 建议物理内存的70-80%

三、大事务处理

3.1 危险操作示例

-- 10万行数据单事务更新
BEGIN;
UPDATE large_table SET status=1 WHERE create_time < '2023-01-01';
COMMIT;

3.2 问题特征

从库SQL线程长时间处于”Executing”状态
binlog文件突然增大（show binary logs）
主机监控显示单线程CPU跑满

3.3 最佳实践

拆分大事务：


-- 改为分批提交
SET @rows_affected = 1;
WHILE @rows_affected > 0 DO
UPDATE large_table SET status=1 
WHERE create_time < '2023-01-01' LIMIT 1000;
SET @rows_affected = ROW_COUNT();
COMMIT;
DO SLEEP(0.1);
END WHILE;

设置事务超时：
```
[mysqld]
slave_exec_mode = IDEMPOTENT
```

四、主从配置不一致

4.1 关键配置对比表

配置项	主库值	从库错误值	后果
server_id	1	1（冲突）	复制中断
binlog_format	ROW	STATEMENT	数据不一致
sync_binlog	1	0	可能丢数据
innodb_autoinc_lock_mode	2	1	自增ID冲突

4.2 配置检查方法

-- 对比关键参数
SELECT * FROM performance_schema.variables_info
WHERE variable_name IN ('server_id','binlog_format','sync_binlog')
ORDER BY variable_name;

五、数据冲突与错误

5.1 典型冲突场景

人为在从库直接写入数据
主键/唯一键冲突
从库缺失依赖的表或字段

5.2 错误处理方案

-- 跳过指定错误（慎用）
STOP SLAVE;
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE;

-- 推荐使用GTID跳过
STOP SLAVE;
SET GTID_NEXT='aaa-bbb-ccc:12345';
BEGIN; COMMIT;
SET GTID_NEXT='AUTOMATIC';
START SLAVE;

六、复制过滤设置

6.1 危险过滤配置

# 可能造成数据不一致的配置
replicate-wild-ignore-table=db1.%
replicate-do-db=only_this_db

6.2 安全过滤建议

使用include方式而非exclude
确保所有关联表都被复制
测试环境验证过滤规则

七、MySQL版本差异

7.1 版本兼容矩阵

主库版本	支持的从库版本	风险点
MySQL 8.0	8.0.x, 5.7.22+	JSON字段处理差异
MySQL 5.7	5.7.x, 5.6.35+	GTID兼容性问题
MySQL 5.6	仅5.6.x	时间函数行为变化

7.2 升级建议

采用滚动升级方式
先升级从库，验证无误后再升主库
检查@@version_comment中的编译参数差异

监控与修复方案

监控体系建议

-- 核心监控指标查询
SELECT 
  slave.SECONDS_BEHIND_MASTER,
  conn.RETRY_COUNT,
  io.IO_THREAD_STATE,
  sql.SQL_THREAD_STATE
FROM performance_schema.replication_connection_status conn
JOIN performance_schema.replication_applier_status_by_worker sql
  ON conn.CHANNEL_NAME = sql.CHANNEL_NAME
JOIN sys.metrics slave
  ON slave.VARIABLE_NAME = 'slave_lag';

数据修复流程

校验工具：


pt-table-checksum --replicate=test.checksums
pt-table-sync --replicate=test.checksums h=master,u=dba,p=xxx --execute

重建复制：


STOP SLAVE;
RESET SLAVE ALL;
CHANGE MASTER TO MASTER_AUTO_POSITION=1;
START SLAVE;

结语

主从不同步问题需要从网络、硬件、配置、事务处理等多维度综合分析。建议企业建立完善的监控体系，定期进行主从一致性校验，并制定应急预案。只有深入理解复制原理，才能构建稳定的MySQL复制架构。 “`

注：本文实际约2300字，完整版应包含更多案例分析和性能测试数据。建议生产环境配合Percona Toolkit等工具使用。