MySql--binlog

发表于 2022-10-14 更新于 2026-09-14 分类于技术， mysql 置顶精品

阅读次数： 0 本文字数： 3.7k 阅读时长 ≈ 13 分钟

摘要

MySql知识点介绍: binlog相关
本文基于mysql-8.0.30，https://dev.mysql.com/doc/refman/8.0/en/

常见面试题

1.MySql同步到ES，如何保证数据一致性？

binlog相关

MySQL的二进制日志binlog可以说是MySQL最重要的日志，它记录了所有的DDL和DML语句（除了数据查询语句select）,其以事件形式记录，还包含语句所执行的消耗的时间，MySQL的二进制日志是事务安全型的，所以binlog内记录的是事务提交成功后的内容。

查看binlog状态

# 查看bin‐log是否开启
mysql> show variables like '%log_bin%';
+---------------------------------+----------------------------------------------------+
| Variable_name                   | Value                                              |
+---------------------------------+----------------------------------------------------+
| log_bin                         | ON                                                 |
| log_bin_basename                | /usr/local/soft/mysql8/datas/mysql/mysql-bin       |
| log_bin_index                   | /usr/local/soft/mysql8/datas/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF                                                |
| log_bin_use_v1_row_events       | OFF                                                |
| sql_log_bin                     | ON                                                 |
+---------------------------------+----------------------------------------------------+

# 查看最后一个bin‐log日志的相关信息
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000012 |   138605 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

# 会多一个最新的bin‐log日志
mysql> flush logs;
Query OK, 0 rows affected (0.02 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000013 |      157 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

# 清空所有的bin‐log日志
mysql> reset master;
Query OK, 0 rows affected (0.02 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      157 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

binlog数据恢复

1 2	# 查看binlog日志，重点关注begin和commit之间的内容， mysqlbinlog --no-defaults /usr/local/soft/mysql8/datas/mysql/mysql-bin.000001

如下为查看binlog日志的内容片段，end_log_pos后面的就是Position的值

BEGIN
/*!*/;
# at 311
#220928  8:22:15 server id 3306  end_log_pos 367 CRC32 0x4f3bcf2a 	Table_map: `test`.`film` mapped to number 186
# at 367
#220928  8:22:15 server id 3306  end_log_pos 412 CRC32 0x3fc87530 	Write_rows: table id 186 flags: STMT_END_F

BINLOG '
NwQ0YxPqDAAAOAAAAG8BAAAAALoAAAAAAAEABHRlc3QABGZpbG0AAgMPAh4AAgEBAAIBISrPO08=
NwQ0Yx7qDAAALQAAAJwBAAAAALoAAAAAAAEAAgAC/wAEAAAABHRlc3Qwdcg/
'/*!*/;
# at 412
#220928  8:22:15 server id 3306  end_log_pos 443 CRC32 0x0712832c 	Xid = 17662
COMMIT/*!*/;

常用的恢复方法

# 恢复全部数据
mysqlbinlog --no-defaults mysql-bin.000001 | mysql -uroot -p test(数据库名)

# 恢复指定位置数据
mysqlbinlog --no-defaults --start-position="367" --stop-position="443" mysql-bin.000001 | mysql -uroot -p test(数据库)

# 恢复指定时间段数据
mysqlbinlog --no-defaults --stop-date= "2022‐09‐28 08:22:15" --start-date= "2022‐09‐28 08:22:15" mysql-bin.000001 | mysql -uroot -p test(数据库)

一个数据恢复的小示例

假设数据库每天都有全量备份

1	mysqldump -uroot -ppassword -B dbName > $bakpath/dbName_$(date +%F).sql

突然某天数据库出现异常，比如关闭后无法重启，此时可以通过全量备份和binlog日志进行数据恢复
先将binlog日志备份

1	cp /usr/local/soft/mysql8/datas/mysql/mysql-bin.* $bakpath/

1	rm -rf /usr/local/soft/mysql8/datas/mysql/*

重新初始化数据库并启动

1 2	mysqld --user=mysql --initialize-insecure systemctl start mysqld

导入全量备份，假设查看备份完成时间是 2022-11-10 12:30:30

1	mysql < $bakpath/dbName_$(date +%F).sql

导入binlog数据

1
2

# 这里就从备份完成时间开始导入，实际上这个时间并不准确，因为备份文件中并没有记录最后的position，所以很难比较出准确的时间或者position
mysqlbinlog --no-defaults --start-date="2022-11-10 12:30:30" $bakpath/mysql-bin.* | mysql dbName

注意
如果数据库开启了GTID，需要先关闭再导入binlog数据，否则不能导入binlog数据
临时关闭方式: 更改 GTID_MODE 状态顺序为 ON<->ON_PERMISSIVE<->OFF_PERMISSIVE<->OFF ，需要按照顺序依次改变。

# 查看 GTID_MODE 当前状态
show global variables like 'gtid_mode';
# 修改GTID_MODE 状态为 ON_PERMISSIVE
set @@GLOBAL.GTID_MODE=ON_PERMISSIVE;
# 修改GTID_MODE 状态为 OFF_PERMISSIVE
set @@GLOBAL.GTID_MODE=OFF_PERMISSIVE;
# 修改GTID_MODE 状态为 OFF
set @@GLOBAL.GTID_MODE=OFF;

MySql同步到ES，如何保证数据一致性？

先把预期说死：ES 不是 MySQL 的同步副本，做不到和 InnoDB 同一毫秒的强一致。 主数据永远在 MySQL，ES 是为搜索/聚合服务的派生视图。工程上要保证的是：不丢、不乱序覆盖、可重放、可对账，延迟通常是秒级的最终一致。谁要读刚写入的那一行，去 MySQL 查，不要赌 ES 已经可搜。

三条同步路，一致性从弱到强

应用双写 MySQL + ES：两次网络调用没有原子性，MySQL 成功 ES 失败就会漂。并发更新也没有全局顺序。除非再上本地消息表（和业务同一事务写入 outbox），否则不要用双写当主方案。
定时 JDBC 拉取（Logstash jdbc 按 update_time 扫表）：实现简单，但删数据容易漏（要靠软删）、同一秒多条更新可能被 > 漏掉、对账窗口大。只适合变更很少、能接受分钟级延迟的场景。
听 binlog（Canal / Maxwell / Debezium）：和从库同一条链路。上文写过，binlog 里是事务提交成功后的事件，顺序就是提交顺序。这是面试和线上的主流答案。

Canal 已经能自动同步了，为什么还要接 Kafka

先分清职责：Canal 只做「伪装从库拉 binlog → 解析成结构化变更事件」，它不负责写 ES。写 ES 的永远是消费端（你自己的程序，或 canal-adapter）。所以链路天然分成「谁产出事件」和「谁消费事件」两段，Kafka 是可选的中间层，不是必需品。

Canal 有两种投递方式：

TCP 直连：消费端用 canal-client 直接从 canal server 拉，不需要 Kafka。小系统、单一下游就这么用，链路最短。
MQ 模式：canal server 直接把事件投给 Kafka / RocketMQ / RabbitMQ / Pulsar，消费端改成订阅 MQ。

规模上来后才需要 Kafka，理由按重要性排：

多个下游。ES、Redis 缓存、数仓、风控往往都要同一份变更。直连模式下每个下游各挂一个 canal instance，就是对主库开 N 个 dump 连接、传 N 份 binlog；接 Kafka 后 binlog 只拉一次，下游各用自己的 consumer group，互不干扰。这是最硬的理由。
削峰和反压。批量刷数据、大事务会让 binlog 瞬间涌出几十万行事件，而 ES bulk 写入有上限。直连时消费端一卡，位点就推不动；万一长时间追不上，binlog 被 binlog_expire_logs_seconds 清掉，就只能重做全量。Kafka 是能落盘、保留几天的大缓冲，把生产和消费速率解耦。
可重放。改了 ES mapping 要重建索引、或消费端写出 bug 需要重跑，把 offset 重置到几天前即可，不用回 MySQL 重做全量，也不赌 binlog 还在。而且 Kafka 里已经是解析好的结构化事件，比重新解析 binlog 便宜。
并行且保序。按主键 hash 分区，多个消费者实例并行处理不同分区，同一行仍然有序。直连模式想并发就得自己实现分片和保序。
可观测与故障隔离。Kafka 的 consumer lag 一眼能看出堆积多少；某条消息一直写不进 ES 可以进死信队列旁路掉，而不是卡死整条流。

所以判断标准很简单：只有 ES 一个下游、变更量不大、能接受消费端故障期间靠 binlog 保留期兜底，就别上 Kafka，canal-client 或 canal-adapter 直接写 ES 更省事。下游多、峰值高、需要重放，才值得付出维护 Kafka 的成本。Debezium 略有不同，它的典型形态就是跑在 Kafka Connect 上；想不要 Kafka 得用 Debezium Server 或嵌入式 Engine。

前置：binlog-format=ROW + binlog_row_image=FULL，两个都是 MySQL 8.0 的默认值（见 MySql单节点、主从、双主的构建方法）。

binlog_row_image 的关键不是「带旧值」：MINIMAL 下后镜像只有被改动的列，消费端拼不出整篇文档，只能再回查 MySQL；前镜像则只有主键，删 ES 文档其实够用。要整行覆盖就必须 FULL。

为什么 binlog_format 一定要是 ROW

根本原因：ROW 记的是「哪几行、变成了什么值」，STATEMENT 记的是「执行过哪句 SQL」。而下游要的是行，不是 SQL。

-- 假设执行了这么一条
UPDATE orders SET status = 1 WHERE create_time < '2024-01-01';

-- STATEMENT 格式的 binlog 里只有上面那行 SQL 原文
-- ROW 格式的 binlog 里是 N 条行事件，每条都带主键和各列的前后值

STATEMENT 下消费端不知道改了哪些行。上面这条可能命中 50 万行，也可能 0 行。想知道受影响的主键，消费端得自己实现一遍 SQL 解析 + 优化器 + 执行，这不现实。ES 的写入单位是一篇文档（_id = 主键），没有主键列表就无法转换成 ES 操作。
DELETE 同理。DELETE FROM orders WHERE ... 不告诉你删了哪些 _id，ROW 的前镜像才给出主键。
ES 需要整行，不是增量 SQL。覆盖一篇文档要该行所有列的当前值，只有 ROW（且 FULL）提供。
STATEMENT 本身就不可靠。NOW()、RAND()、UUID()、无 ORDER BY 的 LIMIT 更新、并发 AUTO_INCREMENT，重放结果和原库不一致——这也是主从复制推荐 ROW 的原因。
MIXED 也不行。它是由主库在写 binlog 时按语句二选一：平时用 STATEMENT，只有判定 STATEMENT 不安全时才改用 ROW。对 CDC 来说等于「有的语句解析得出、有的解析不出」，链路不可用。

补充：binlog_format 自 MySQL 8.0.34 起已被标记为废弃，未来版本只保留 ROW。所以这个前置条件在 8.0 上通常本来就满足，新系统也不该再考虑 STATEMENT/MIXED。

用 binlog 把一致性做扎实

全量 + 增量衔接（顺序不能反）：必须先记位点，再灌全量。正确做法是开一个 REPEATABLE READ 事务，先 SHOW MASTER STATUS 记下 File + Position（或 GTID），再在这个事务里按主键扫表灌 ES，最后从那个位点开始追增量。Debezium 的初始快照就是这个顺序。
- 反过来「先灌全量、结束时再记位点」会丢数据：某行在扫描早期被读走、扫描期间又被改，这条 UPDATE 的位点早于你记下的位点，增量阶段不会重放，ES 里永远是旧值。
- 位点取在前面，快照与增量必然有重叠，重叠靠下面的幂等消化，这是安全的。
幂等：ES 文档 _id 用 MySQL 主键，写入一律 index（覆盖）。Canal 至少一次投递，重启从上次位点重放，重复消息打上去还是同一篇文档。DELETE 事件对应 ES delete。
版本号防回退：只有幂等不够。重试、并发 bulk 都可能让旧事件后到，把新值盖回去。给写入带上单调递增的版本（行上的版本列、update_time 毫秒，或 binlog 位点编码成数字），用 ES 的 version_type=external，旧版本会被拒掉而不是覆盖。注意删除后的版本号只保留 index.gc_deletes（默认 60s），超过这个窗口的迟到旧事件仍可能把已删文档写回来。
保序：同一主键的变更要按顺序落到 ES。走 Kafka 就让 partition key = 主键，同一行进同一分区；直连模式下自己按主键 hash 分线程，绝不能把同一行的事件丢给不同线程并发处理。否则旧 UPDATE 后到，会把新值盖掉。不同行可以并行。
位点提交：消费成功写入 ES 之后，再提交 Canal/Kafka 位点。ES 失败就重试，不要跳过。Canal 伪装成从库，位点丢了就等于丢数据。
宽表/多表：订单 + 明细这种要拼成一篇 ES 文档的，不能只听一张表。听相关表的 binlog，用主键回查 MySQL 再整篇覆盖，或在消息里带足够字段本地拼。这是一致性最容易烂的地方。回查读到的是当前最新值，可能比手上这条事件还新，所以版本号要用回查时刻的值，不能用事件里的旧时间戳，否则后续正常事件会被判成旧版本而丢弃。
对账：定时按主键抽样或全量比对 count、max(update_time)、哈希。不一致的以 MySQL 为准重刷那一篇。binlog 只能保证「从某位点之后按顺序放」，保证不了历史已经漂了的数据自己回来。
可见性：ES 默认 refresh_interval=1s，写入成功不等于马上搜得到。搜索允许秒级延迟；如果业务是「写入后立刻搜到自己」，这条链路不该走 ES，或对该次写入 refresh=wait_for（吞吐会掉）。

-- 消费端必需
SHOW VARIABLES LIKE 'binlog_format';     -- ROW
SHOW VARIABLES LIKE 'binlog_row_image';  -- FULL

-- 全量前取一致位点：位点在扫表之前记，不是之后
START TRANSACTION WITH CONSISTENT SNAPSHOT;
SHOW MASTER STATUS;   -- 记下 File / Position / GTID，增量从这里追
-- 然后在本事务内按主键分批 SELECT 灌入 ES，最后 COMMIT

mysqldump --single-transaction --source-data=2 导出的文件头部也会带上快照那一刻的位点，可以直接拿来当增量起点。

常见翻车

Statement/Mixed binlog：消费端看不到完整行，无法稳定映射成 ES 文档。ROW 但 binlog_row_image=MINIMAL 也一样，后镜像缺列。
先灌全量、后记位点：看着能跑，实际丢的是「扫描期间被改过的行」，而且不对账根本发现不了。
只同步 INSERT/UPDATE，不处理 DELETE，ES 变垃圾堆。
双写 + 定时任务一起跑，没有版本号，后到的旧数据覆盖新数据。
大事务、从库延迟：Canal 挂在主库会抢 binlog 带宽；挂从库则一致性还受复制延迟影响，见 MySql单节点、主从、双主的构建方法半同步那节。
表结构变了 ES mapping 没变：加列可先 MySql--表信息相关里的 INSTANT，再更新 mapping；改类型往往要重建索引。

MySql同步到ES，如何保证数据一致性？

先定性：ES 是派生索引，和 MySQL 是最终一致，不是分布式事务。主库为准；刚写完要强一致的读走 MySQL。
主路径用 ROW + binlog_row_image=FULL（Canal/Debezium）+ 按主键分区的 MQ：提交顺序即同步顺序。
全量与增量的衔接：先记位点再灌全量（START TRANSACTION WITH CONSISTENT SNAPSHOT + SHOW MASTER STATUS），重叠靠幂等消化。顺序写反就会丢掉扫描期间被改的行。
一致性四件套：_id=主键 覆盖写入做幂等；同一主键保序；version_type=external 带单调版本号防旧事件回退；位点在 ES 写成功后再提交，失败重试。多表宽文档用主键回查整篇覆盖。
定时对账补洞；删除事件要删 ES。不要用无 outbox 的双写当主方案，也不要只靠 update_time 轮询指望不丢。