mysql 主从复制原理、实现方式以及主从同步延迟的处理方式-优快云博客

本文链接：https://blog.youkuaiyun.com/csdn_tom_168/article/details/149156425

mysql 主从复制原理、实现方式

MySQL 主从复制是实现高可用、读写分离和灾难恢复的核心机制，其本质是主库（Master）将数据变更异步同步到从库（Slave）。以下是深度解析：

一、主从复制核心原理

1. 三线程协作模型

线程	所在位置	职责
Binlog Dump Thread	主库	监听从库请求，推送二进制日志（binlog）
I/O Thread	从库	连接主库请求binlog，写入中继日志（relay log）
SQL Thread	从库	读取relay log，解析并重放SQL（实现数据同步）

2. 数据同步流程

主库数据变更：
- 事务提交时写入 binlog（二进制日志）
- binlog 格式：STATEMENT（SQL语句）、ROW（行数据变更）、MIXED（混合模式）
从库发起连接：
- 从库通过 CHANGE MASTER TO 命令指定主库信息
日志传输：
- 主库的 Binlog Dump 线程读取 binlog 发送给从库
日志中继：
- 从库的 I/O 线程接收数据，写入 relay log
数据重放：
- 从库的 SQL 线程读取 relay log 解析执行SQL

📌 关键点：

主从同步是异步的（默认模式），主库不等待从库确认
从库通过 master_log_file + master_log_pos 记录同步位置

二、主从复制实现方式详解

1. 基于二进制日志位置（Position-Based）

配置步骤：

主库配置 (my.cnf)：

[mysqld]
server_id = 1                # 唯一ID（主从不能重复）
log_bin = /var/lib/mysql/mysql-bin  # 开启binlog
binlog_format = ROW          # 推荐ROW格式（数据一致性更强）

创建复制账号（主库执行）：

CREATE USER 'repl'@'%' IDENTIFIED BY 'Slave@123';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

从库配置 (my.cnf)：

[mysqld]
server_id = 2  # 不同于主库
relay_log = /var/lib/mysql/relay-bin
read_only = ON  # 从库设为只读（防止误写）

初始化从库数据：

# 主库备份
mysqldump -uroot -p --master-data=2 --single-transaction -A > master_db.sql

# 从库导入
mysql -uroot -p < master_db.sql

启动复制（从库执行）：

CHANGE MASTER TO
  MASTER_HOST = '192.168.1.100',
  MASTER_USER = 'repl',
  MASTER_PASSWORD = 'Slave@123',
  MASTER_LOG_FILE = 'mysql-bin.000001',  -- 备份文件中的值
  MASTER_LOG_POS = 154;                  -- 备份文件中的值

START SLAVE;

2. 基于GTID（Global Transaction Identifier）

优势：自动跟踪事务位置，无需手动维护binlog位置

配置步骤：

主从库配置（my.cnf 新增）：

gtid_mode = ON
enforce_gtid_consistency = ON

从库启动复制：

CHANGE MASTER TO
  MASTER_HOST = '192.168.1.100',
  MASTER_USER = 'repl',
  MASTER_PASSWORD = 'Slave@123',
  MASTER_AUTO_POSITION = 1;  # 启用GTID自动定位

START SLAVE;

GTID 格式：
server_uuid:transaction_id（如 3E11FA47-71CA-11E1-9E33-C80AA9429562:5）

三、主从复制模式对比

复制模式	原理	优点	缺点
异步复制	主库提交事务后立即返回，不等待从库确认（默认模式）	性能高	数据可能丢失（主库宕机时）
半同步复制	主库提交事务后需等待至少一个从库确认收到binlog	保证数据安全	性能下降（可调超时时间）
组复制（MGR）	基于Paxos协议实现多主同步（MySQL 5.7+）	高可用+自动故障切换	配置复杂

开启半同步复制：

-- 主从库安装插件
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

-- 主库
SET GLOBAL rpl_semi_sync_master_enabled = 1;
SET GLOBAL rpl_semi_sync_master_timeout = 1000;  -- 超时时间(ms)

-- 从库
SET GLOBAL rpl_semi_sync_slave_enabled = 1;

四、主从延迟问题与优化

1. 延迟原因

从库单线程重放SQL（5.6+支持并行复制）
主库大事务（如批量删除）
从库性能不足（CPU/IO瓶颈）
网络带宽不足

2. 解决方案

方法	操作说明
并行复制	启用多线程回放（需MySQL 5.6+）： `slave_parallel_workers = 8`
增强半同步	MySQL 5.7+ Loss-Less Semi-Sync（避免ACK等待导致延迟）
分库分表	减少单库压力
MTS按逻辑时钟	5.7+ 设置 `slave_parallel_type = LOGICAL_CLOCK`
跳过大事务	紧急处理：`SET GLOBAL sql_slave_skip_counter = 1`

五、主从复制监控与故障处理

1. 关键监控命令

-- 查看从库状态
SHOW SLAVE STATUS\G

关注字段：

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0 # 延迟秒数
Last_IO_Error: 错误信息
Retrieved_Gtid_Set: 已接收GTID
Executed_Gtid_Set: 已执行GTID

2. 常见故障处理

问题	解决方案
主键冲突	检查是否有人误写入从库 → 设置 `read_only=ON`
数据不一致	使用 `pt-table-checksum` 校验数据，`pt-table-sync` 修复
SQL线程停止	查看 `Last_SQL_Error`，跳过错误或手动修复数据
主库binlog被清除	重建从库或使用备份恢复

六、生产环境最佳实践

强制使用ROW模式：

binlog_format = ROW  # 避免函数导致主从不一致

从库备份：

mysqldump --single-transaction --master-data=2 -A > backup.sql

延迟监控告警：

ALTER EVENT monitor_replica_delay
ON SCHEDULE EVERY 60 SECOND
DO 
  IF (SELECT Seconds_Behind_Master FROM sys.replica_status > 300) THEN
     CALL send_alert('主从延迟超过5分钟!');
  END IF;

高可用架构：

💡 终极建议：

生产环境必用 GTID + 半同步复制
从库至少部署2个（一个半同步，一个异步用于备份）
使用 ProxySQL 自动管理故障转移和读写分离

主从同步延迟的处理方式

MySQL主从同步延迟是生产环境常见问题，会导致数据不一致、业务逻辑错误等严重后果。以下是系统性解决方案，按紧急程度和实施难度分层处理：

一、紧急恢复手段（快速见效）

1. 跳过导致延迟的事务（慎用！仅限非关键数据）

-- 查看同步错误（从库执行）
STOP SLAVE;
SHOW SLAVE STATUS\G -- 查看Last_SQL_Error

-- 跳过1个事务（GTID模式需额外处理）
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE;

2. 临时切换读流量到主库

中间件层（如ProxySQL）：

-- 将读组指向主库
UPDATE mysql_query_rules SET destination_hostgroup=10 
WHERE active=1 AND match_pattern='^SELECT';
LOAD RULES TO RUNTIME;

二、中期优化策略（架构与配置调整）

1. 启用多线程并行复制（MTS）

-- 从库配置 my.cnf
slave_parallel_workers = 8      # 推荐CPU核数的50%-80%
slave_parallel_type = LOGICAL_CLOCK  # MySQL 5.7+ 必选
slave_preserve_commit_order = ON     # 保证事务顺序

原理：LOGICAL_CLOCK 基于事务依赖关系并行重放，提升吞吐量

2. 优化主库写入

场景	优化方案
大事务	拆分事务（如10万行DELETE → 每次删1000行）
无主键/索引表写入	强制所有表必须有主键（`innodb_force_primary_key=ON`）
批量插入	使用`LOAD DATA`替代`INSERT INTO ... VALUES(...),(...)`

3. 升级硬件与配置

从库硬件：提升IOPS（SSD磁盘）、增加内存（减少磁盘IO）

参数调优：

# 从库配置
innodb_flush_log_at_trx_commit = 2  # 牺牲部分持久性提升IO性能
sync_binlog = 0                     # 禁用binlog刷盘
relay_log_recovery = ON             # 崩溃后安全恢复

三、长期根治方案（架构升级）

1. 分库分表降低单库压力

工具：ShardingSphere、MyCAT
效果：将热点数据分散到不同主从集群

2. 使用多源复制（Multi-Source Replication）

-- 从库配置多个主库通道
CHANGE MASTER TO MASTER_HOST='master1' ... FOR CHANNEL 'ch1';
CHANGE MASTER TO MASTER_HOST='master2' ... FOR CHANNEL 'ch2';
START SLAVE FOR CHANNEL 'ch1';
START SLAVE FOR CHANNEL 'ch2';

适用场景：多个业务库汇总到一个分析库

3. 切换为Group Replication（MGR）

优势：原生支持多主写入、自动故障切换、数据强一致
要求：MySQL 5.7.17+，网络延迟<5ms
架构：

四、关键预防措施（治未病）

1. 实时监控体系

# 监控延迟脚本（从库执行）
mysql -e "SHOW SLAVE STATUS\G" | grep "Seconds_Behind_Master"

告警阈值：延迟 > 60秒触发告警
可视化：Prometheus + Grafana 监控关键指标：
- seconds_behind_master
- slave_sql_running_state

2. 主从配置规范

配置项	主库	从库	作用
`log_slave_updates`	OFF	ON	级联复制必备
`read_only`	OFF	ON	防止从库误写
`innodb_buffer_pool_size`	70%内存	> 主库	加速重放

3. 事务设计规范

禁止：LOCK TABLES 显式锁表
控制：单事务操作行数 < 1万行
避免：长时间未提交的事务（监控 information_schema.INNODB_TRX）

五、不同延迟场景的终极解决方案

延迟原因	根治方案
SQL单线程重放慢	MySQL 8.0 的 WRITESET并行复制（`binlog_transaction_dependency_tracking=WRITESET`）
大事务阻塞	业务改造 → 小批量提交 + 错峰执行
从库硬件性能差	升级为同配置主机或使用物理机+NVMe SSD
跨地域同步	使用 MySQL Group Replication + 代理层自动路由