zt's Wiki

外观

MySQL Binlog机制与主从复制

约 1691 字大约 6 分钟

mysqlbinlogreplication

2025-05-03

Binlog(Binary Log)是 MySQL Server 层的日志，记录所有对数据库的变更操作。它是主从复制的基础，也是数据恢复和审计的重要工具。

Binlog 与 Redo Log 的区别

维度BinlogRedo Log
层级Server 层InnoDB 引擎层
内容逻辑日志(SQL 语句或行变更)物理日志(页的修改)
写入方式追加写入，文件切换循环写入，覆盖旧日志
用途复制、恢复崩溃恢复
所有引擎是仅 InnoDB

Binlog 格式

三种格式对比

-- 查看当前格式
SHOW VARIABLES LIKE 'binlog_format';

-- 设置格式（推荐 ROW）
SET GLOBAL binlog_format = 'ROW';

-- ROW 格式下的详细程度
SET GLOBAL binlog_row_image = 'FULL';    -- 记录所有列（默认）
SET GLOBAL binlog_row_image = 'MINIMAL'; -- 仅记录变更列和主键
SET GLOBAL binlog_row_image = 'NOBLOB';  -- 不含未修改的 BLOB

Binlog Event 结构

两阶段提交 (2PC)

Binlog 和 Redo Log 之间通过两阶段提交保证一致性：

Binlog Group Commit

为提高性能，MySQL 将多个事务的 Binlog 写入合并为一次 fsync：

-- 控制 Group Commit
SET GLOBAL binlog_group_commit_sync_delay = 0;        -- 等待时间(微秒)
SET GLOBAL binlog_group_commit_sync_no_delay_count = 0; -- 最大等待事务数

主从复制架构

异步复制 (Asynchronous)

半同步复制 (Semi-Synchronous)

-- 安装半同步插件
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 = 3000;  -- 超时退化为异步(ms)
SET GLOBAL rpl_semi_sync_master_wait_for_slave_count = 1;

-- 从库配置
SET GLOBAL rpl_semi_sync_slave_enabled = 1;

组复制 (Group Replication)

组复制通过 Paxos 协议保证多数节点确认后才提交事务，支持：

  • 单主模式：一个节点可写，其他只读
  • 多主模式：所有节点可写，通过认证(Certification)检测冲突

GTID 复制

GTID(Global Transaction Identifier)为每个事务分配全局唯一标识，简化复制管理。

-- 启用 GTID
SET GLOBAL gtid_mode = ON;
SET GLOBAL enforce_gtid_consistency = ON;

-- GTID 格式：server_uuid:transaction_id
-- 例如：3E11FA47-71CA-11E1-9E33-C80AA9429562:1-100

-- 查看已执行的 GTID 集合
SHOW GLOBAL VARIABLES LIKE 'gtid_executed';

-- 基于 GTID 配置从库
CHANGE REPLICATION SOURCE TO
    SOURCE_HOST = '192.168.1.100',
    SOURCE_PORT = 3306,
    SOURCE_USER = 'repl',
    SOURCE_PASSWORD = 'password',
    SOURCE_AUTO_POSITION = 1;  -- 自动定位 GTID

GTID 优势

传统复制GTID 复制
基于文件名 + 偏移量定位基于全局唯一 ID 定位
切换主库需手动计算位点自动定位，切换简单
无法避免重复执行幂等性，已执行的不会重复

并行复制

从库的 SQL Thread 默认是单线程重放，容易造成主从延迟。并行复制(MTS, Multi-Threaded Slave)可以加速。

-- MySQL 8.0 推荐配置
SET GLOBAL replica_parallel_type = 'LOGICAL_CLOCK';
SET GLOBAL replica_parallel_workers = 8;
SET GLOBAL replica_preserve_commit_order = ON;

-- MySQL 8.0.27+ 新增 WRITESET 模式
SET GLOBAL binlog_transaction_dependency_tracking = 'WRITESET';

Binlog 工具

mysqlbinlog

# 查看 binlog 事件列表
mysqlbinlog --no-defaults -v mysql-bin.000001

# 指定时间范围
mysqlbinlog --start-datetime='2025-05-03 10:00:00' \
            --stop-datetime='2025-05-03 11:00:00' \
            mysql-bin.000001

# 基于 GTID 过滤
mysqlbinlog --include-gtids='uuid:1-100' mysql-bin.000001

# 数据恢复：重放 binlog
mysqlbinlog mysql-bin.000001 | mysql -u root -p

# ROW 格式解析为可读 SQL
mysqlbinlog --base64-output=decode-rows -v mysql-bin.000001

Binlog 管理

-- 查看所有 binlog 文件
SHOW BINARY LOGS;

-- 查看当前正在写入的 binlog
SHOW MASTER STATUS;

-- 查看 binlog 事件
SHOW BINLOG EVENTS IN 'mysql-bin.000001' LIMIT 20;

-- 手动切换 binlog 文件
FLUSH BINARY LOGS;

-- 清理过期 binlog
PURGE BINARY LOGS BEFORE '2025-05-01 00:00:00';
PURGE BINARY LOGS TO 'mysql-bin.000005';

-- 自动过期（推荐）
SET GLOBAL binlog_expire_logs_seconds = 604800;  -- 7天

常见复制问题排查

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

-- 关键字段
-- Replica_IO_Running: Yes       -- IO Thread 状态
-- Replica_SQL_Running: Yes       -- SQL Thread 状态
-- Seconds_Behind_Source: 0       -- 主从延迟(秒)
-- Last_Error                     -- 最后一个错误
-- Retrieved_Gtid_Set             -- 已接收的 GTID
-- Executed_Gtid_Set              -- 已执行的 GTID
问题原因解决方案
IO Thread 停止网络问题/权限问题检查连接和 repl 用户权限
SQL Thread 停止主从数据不一致SET GLOBAL sql_slave_skip_counter = 1 或修复数据
延迟持续增大从库单线程/大事务启用并行复制，拆分大事务
GTID 不一致从库有本地事务使用 gtid_purged 跳过

总结

  • Binlog 是 MySQL 复制和数据恢复的基石，ROW 格式是生产环境的推荐选择
  • 两阶段提交保证 Binlog 与 Redo Log 的一致性
  • GTID 简化了复制管理，是现代 MySQL 部署的标准配置
  • 并行复制(WRITESET)显著降低主从延迟
  • 半同步/组复制提升数据安全性，但增加了写入延迟

贡献者

withesse

更新日志

2026/3/14 13:09
查看所有更新日志
  • 9f6c2-feat: organize wiki content and refresh site setup于