mysql 主从复制原理、实现方式 以及 主从同步延迟的处理方式

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

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


一、主从复制核心原理

1. 三线程协作模型
Binlog Dump Thread
I/O Thread
SQL Thread
主库 Master
从库 Slave
Relay Log
Slave DB
线程所在位置职责
Binlog Dump Thread主库监听从库请求,推送二进制日志(binlog)
I/O Thread从库连接主库请求binlog,写入中继日志(relay log)
SQL Thread从库读取relay log,解析并重放SQL(实现数据同步)
2. 数据同步流程
  1. 主库数据变更
    • 事务提交时写入 binlog(二进制日志)
    • binlog 格式:STATEMENT(SQL语句)、ROW(行数据变更)、MIXED(混合模式)
  2. 从库发起连接
    • 从库通过 CHANGE MASTER TO 命令指定主库信息
  3. 日志传输
    • 主库的 Binlog Dump 线程读取 binlog 发送给从库
  4. 日志中继
    • 从库的 I/O 线程接收数据,写入 relay log
  5. 数据重放
    • 从库的 SQL 线程读取 relay log 解析执行SQL

📌 关键点

  • 主从同步是异步的(默认模式),主库不等待从库确认
  • 从库通过 master_log_file + master_log_pos 记录同步位置

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

1. 基于二进制日志位置(Position-Based)

配置步骤

  1. 主库配置 (my.cnf):
    [mysqld]
    server_id = 1                # 唯一ID(主从不能重复)
    log_bin = /var/lib/mysql/mysql-bin  # 开启binlog
    binlog_format = ROW          # 推荐ROW格式(数据一致性更强)
    
  2. 创建复制账号(主库执行):
    CREATE USER 'repl'@'%' IDENTIFIED BY 'Slave@123';
    GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
    
  3. 从库配置 (my.cnf):
    [mysqld]
    server_id = 2  # 不同于主库
    relay_log = /var/lib/mysql/relay-bin
    read_only = ON  # 从库设为只读(防止误写)
    
  4. 初始化从库数据
    # 主库备份
    mysqldump -uroot -p --master-data=2 --single-transaction -A > master_db.sql
    
    # 从库导入
    mysql -uroot -p < master_db.sql
    
  5. 启动复制(从库执行):
    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位置

配置步骤

  1. 主从库配置my.cnf 新增):
    gtid_mode = ON
    enforce_gtid_consistency = ON
    
  2. 从库启动复制
    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被清除重建从库或使用备份恢复

六、生产环境最佳实践

  1. 强制使用ROW模式
    binlog_format = ROW  # 避免函数导致主从不一致
    
  2. 从库备份
    mysqldump --single-transaction --master-data=2 -A > backup.sql
    
  3. 延迟监控告警
    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;
    
  4. 高可用架构
    半同步复制
    异步复制
    # 主库故障自动切换
    应用
    ProxySQL
    Master
    Slave1
    Slave2
    Keepalived

💡 终极建议

  • 生产环境必用 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
  • 架构
    App
    MySQL Router
    Group Replication
    Node1
    Node2
    Node3

四、关键预防措施(治未病)

1. 实时监控体系
# 监控延迟脚本(从库执行)
mysql -e "SHOW SLAVE STATUS\G" | grep "Seconds_Behind_Master"
  • 告警阈值:延迟 > 60秒触发告警
  • 可视化:Prometheus + Grafana 监控关键指标:
    • seconds_behind_master
    • slave_sql_running_state
2. 主从配置规范
配置项主库从库作用
log_slave_updatesOFFON级联复制必备
read_onlyOFFON防止从库误写
innodb_buffer_pool_size70%内存> 主库加速重放
3. 事务设计规范
  • 禁止LOCK TABLES 显式锁表
  • 控制:单事务操作行数 < 1万行
  • 避免:长时间未提交的事务(监控 information_schema.INNODB_TRX

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

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

💡 MySQL 8.0 WRITESET 黑科技
通过事务修改的数据行哈希值判断并行度,即使无主键表也能高效并行(比LOGICAL_CLOCK提升50%+性能)


六、企业级高可用架构推荐

半同步复制
异步复制
实时采集
实时采集
告警
应用
ProxySQL 集群
主库集群
延迟<1s的从库
延迟<5s的从库
监控中心
运维平台

关键设计

  1. 读请求分级路由
    • 强一致性读 → 主库
    • 弱一致性读 → 低延迟从库
    • 分析类查询 → 高延迟从库
  2. 自动熔断:从库延迟>10秒自动切出读池
  3. 数据校验:每周定时执行 pt-table-checksum

通过上述分层解决方案,可彻底根治主从延迟问题。核心要点

  1. 紧急情况先恢复服务(跳过事务/切主库)
  2. 中期优化并行复制和硬件配置
  3. 长期通过分库分表或MGR升级架构
  4. 建立预防性监控和开发规范
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值