一条SQL解释MySQL的主从复制原理

借用一张图，来解释 mysql主从复制原理 mysql主从复制原理

主备同步的详细过程和一致性保证机制如下：

  1. 主库事务提交流程（两阶段提交）
-- 第一阶段：准备阶段
XA PREPARE
- 写入redo log（prepare状态）
- 记录prepare_lsn

-- 第二阶段：提交阶段
XA COMMIT
- 写入binlog
- 写入redo log（commit状态）
  1. 详细执行步骤

a) 主库端：

  • 事务执行过程中产生redo log（内存中）
  • prepare阶段：将redo log写入磁盘（prepare状态）
  • 写入binlog到磁盘
  • commit阶段：将redo log状态改为commit
  • 如果开启半同步复制，等待从库确认
  • 返回客户端提交成功

b) 从库端：

  • IO线程接收主库binlog
  • 写入relay log
  • 返回接收确认（半同步复制）
  • SQL线程读取relay log
  • 重放SQL语句
  • 记录应用位点和GTID
  1. 一致性保证机制

a) 两阶段提交保证：

-- 主库配置
set global sync_binlog=1;  -- 确保binlog即时写入
set global innodb_flush_log_at_trx_commit=1;  -- 确保redo log即时写入

b) binlog与redo log关联：

  • binlog_log_pos：记录binlog位置
  • last_committed：记录提交序号
  • sequence_number：记录序列号

c) GTID保证：

-- 查看GTID模式
SHOW VARIABLES LIKE 'gtid_mode';

-- 查看已执行的GTID集合
SHOW MASTER STATUS;
SHOW SLAVE STATUS\G
  1. 关键流程保证：

a) 写入保证：

  • redo log prepare必须先于binlog写入
  • binlog写入成功后才能commit
  • relay log写入成功后才能确认接收

b) 崩溃恢复：

  • 如果崩溃点在prepare后、binlog写入前：回滚事务
  • 如果崩溃点在binlog写入后：提交事务
  • 依赖XID（事务ID）关联redo log和binlog
  1. 从库一致性保证：
-- 从库配置
set global relay_log_recovery=1;  -- 确保崩溃恢复
set global relay_log_info_repository='TABLE';  -- 使用表存储复制信息
  1. 监控和验证：
-- 查看主库事务状态
SHOW ENGINE INNODB STATUS\G

-- 查看复制状态
SHOW SLAVE STATUS\G

-- 查看复制延迟
SELECT TIMESTAMPDIFF(SECOND, 
    (SELECT UPDATE_TIME FROM information_schema.TABLES WHERE ...),
    NOW()) as replica_delay;
  1. 最佳实践建议：

a) 配置优化：

  • 启用semi-sync replication
  • 设置合适的超时参数
  • 配置足够的binlog保留时间

b) 监控建议：

  • 监控复制延迟
  • 监控主从事务差异
  • 设置复制错误告警

c) 定期维护：

  • 检查数据一致性
  • 进行主从切换演练
  • 清理过期binlog和relay log

通过这些机制的配合，MySQL能够保证主从之间的事务一致性。关键在于：

  1. 两阶段提交保证了redo log和binlog的一致
  2. GTID保证了复制的准确性和可追踪性
  3. 半同步复制保证了数据的可靠传输
  4. crash-safe特性保证了异常情况下的数据一致性

建议在实际应用中根据业务需求和性能要求，合理配置这些参数，并建立完善的监控机制。