1. binlog 存储和格式
binlog 存储有两种格式：statement 和 row，可以通过参数 binlog_format 来进行控制
1.1 statement
# at 1109
#250208 15:20:20 server id 1 end_log_pos 1215 CRC32 0xa38f87d8 Query thread_id=11 exec_time=0 error_code=0
SET TIMESTAMP=1738999220/*!*/;
insert into t values(5,5,5,5)
/*!*/;
可以看到，statement 格式 binlog 是将语句直接记录，statement 格式要被官方弃用了
1.2 row
# at 422
#250208 15:03:40 server id 1 end_log_pos 661 CRC32 0xc9da182a Query thread_id=11 exec_time=0 error_code=0 Xid = 15
use `test`/*!*/;
SET TIMESTAMP=1738998220/*!*/;
/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
create table t (id int auto_increment primary key, c int, d int, e int, index c_idx (c), index d_e_idx (d,e)) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
/*!*/;
# at 865
#250208 15:18:57 server id 1 end_log_pos 917 CRC32 0x83145454 Write_rows: table id 89 flags: STMT_END_F
BINLOG '
YQWnZxMBAAAAMgAAAGEDAAAAAFkAAAAAAAEABHRlc3QAAXQABAMDAwMADgEBAINf7h8=
YQWnZx4BAAAANAAAAJUDAAAAAFkAAAAAAAEAAgAE/wABAAAAAAAAAAAAAAAAAAAAVFQUgw==
'/*!*/;
可以看到，对于 DDL 语句，尽管使用 row 格式，但是还是记录的语句原文，但是对于 DML 语句，则是一串二进制码，这就是 row 格式的 binlog
上述内容我们无法解析，如果希望看到二进制对应的内容，可以使用命令：mysqlbinlog -vv binlog.xxx，结果如下：
# at 362
#250208 15:31:47 server id 1 end_log_pos 414 CRC32 0x54b2f8f6 Write_rows: table id 89 flags: STMT_END_F
BINLOG '
YwinZxMBAAAAMgAAAGoBAAAAAFkAAAAAAAEABHRlc3QAAXQABAMDAwMADgEBAGSH8bM=
YwinZx4BAAAANAAAAJ4BAAAAAFkAAAAAAAEAAgAE/wAKAAAACgAAAAoAAAAKAAAA9viyVA==
'/*!*/;
### INSERT INTO `test`.`t`
### SET
### @1=10 /* INT meta=0 nullable=0 is_null=0 */
### @2=10 /* INT meta=0 nullable=1 is_null=0 */
### @3=10 /* INT meta=0 nullable=1 is_null=0 */
### @4=10 /* INT meta=0 nullable=1 is_null=0 */
# at 414
#250208 15:31:47 server id 1 end_log_pos 445 CRC32 0xfa5d7061 Xid = 44
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
这一坨就是二进制的内容
2. binlog 和主库性能
MySQL 主备交互流程
影响主库性能的 binlog 参数：
- binlog_cache_size
- binlog_checksum
- binlog_group_commit_sync_delay
- binlog_group_commit_sync_no_delay_count
- binlog_order_commits
- binlog_row_image
- semi-sync……
2.1 binlog_cache_size
binlog 中每个事务在 binlog 文件中都是连续存储的，不会出现交错的情况，这就是通过 binlog_cache 来实现的，只有当事务提交时，才会将整个事务的 binlog 尝试写到磁盘，如下所示：
上图中的 binlog_cache 是一个线程一个 cache，MySQL 线程开启一个事务，只有等到该事务提交后，才能开启新的事务，不能嵌套，因此一个线程一个 cache 即可，并且该 cache 是在连接后执行语句时才分配，并且直接全额分配，且事务提交时 cache 不回收，就算使用 reset connection 该 cache 也不会回收，只有在线程被退出时才会回收，因此建议研发用完就断开连接
如果 binlog_cache 满了怎么办？能不能先将部分内容写入 binlog 文件？答案是不能，因为需要保证 binlog 文件中事务的连续性，只能整体写入，因此如果 cache 满了，那么就找个临时文件写入，等事务完成后从临时文件+cache 一起写入 binlog 文件
由于磁盘操作会影响性能，因此建议将该 cache 设置大些，建议 10MB 以上，防止磁盘操作影响性能，由于 binlog_cache 是直接全部分配，因此也不能设置太大，不然易 OOM
2.2 binlog checksum
binlog 中每个 event 都会计算 CRC32 结果，这就是 checksum，影响很小，如下所示：
从库 io_thread 接收日志时不校验，sql_thread 应用 relay_log 时，会计算语句的 CRC32 值，跟日志中进行比对，一致才应用，不一致就会直接报错，提醒是否有网络或者其他原因，导致文件内容错误
2.3 sync_binlog
sync_binlog=0，每次提交事务只 write，不 sync（磁盘脏页太多以及切 binlog 文件时会 fsync）
sync_binlog=1，每次提交事务都执行 fsync
sync_binlog=N（N>1），每次提交事务都 write，累计 N 个事务后才 fsync
2.4 group commit
MySQL 中跟慢速设备交互基本都会用到 group 思想，比如 redo log、semi-sync、并行查询等
通过调整 binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count 这两个参数，可以控制组提交的行为。
binlog_group_commit_sync_delay：指定在进行 Sync 操作之前等待的微秒数，以便有更多的事务加入到组中。例如，设置为 10000 表示等待 10 毫秒。binlog_group_commit_sync_no_delay_count：指定在达到多少个事务时，无论binlog_group_commit_sync_delay的设置如何，都立即进行 Sync 操作。
sync_binlog 也有相似的作用，那么相同条件下使用哪个呢？ 比如下面两个配置如何选择？
binlog_group_commit_sync_delay=10ms
binlog_group_commit_sync_no_delay_count=10
sync_binlog=10
选择上面的配置，因为上面的配置在不满足时事务是不会提交的，此时掉电或者 crash 等都不会丢事务，但是下面的配置是会将事务提交，只是自己在凑提交事务，此时如果断电或者 crash 会导致 binlog 丢失
2.5 binlog_order_commits
简单回顾两阶段提交
binlog write 跟事务提交可能是错开的，比如事务 1 和事务 2 binlog 都 write 了，且顺序为先 1 后 2，那么在事务提交时，是否需要按照 binlog write 的顺序提交？
如果 binlog_order_commits = ON，表示需要按照 binlog write 的顺序提交事务，默认是 ON，理论上有影响，但是不大
2.6 binlog_row_image
binlog_row_image 是 MySQL 中的一个系统变量，它主要用于控制在 ROW 格式的二进制日志（binlog）中记录数据行的方式。该变量有三种取值，如下：
binlog_row_image 值 | 含义 | 适用场景 | 示例及影响说明 |
|---|---|---|---|
FULL | 在二进制日志中记录每一行数据的所有列，无论这些列的值是否发生变化。也就是说，在 INSERT、UPDATE 和 DELETE 操作的二进制日志记录里，会包含整行数据的完整信息。 | - 当需要进行精确的数据恢复，例如误删除数据后要完整恢复到删除前的状态。 - 主从复制场景下，确保从库能完全精确地复制主库的数据变更，避免因部分列未记录而导致数据不一致。 | INSERT 操作：假设有表 users(id, name, age)，执行 INSERT INTO users VALUES (1, 'Alice', 25);，binlog 会记录完整的 (1, 'Alice', 25)。UPDATE 操作：若执行 UPDATE users SET age = 26 WHERE id = 1;，binlog 会记录更新前的完整行 (1, 'Alice', 25) 和更新后的完整行 (1, 'Alice', 26)。DELETE 操作：执行 DELETE FROM users WHERE id = 1;，binlog 会记录被删除行的完整信息 (1, 'Alice', 26)。 |
MINIMAL | 仅记录数据行中发生变化的列，对于未发生变化的列则不记录。这种方式可以显著减少二进制日志的大小，降低磁盘 I/O 和网络传输的开销。 | - 当磁盘空间有限，或者二进制日志需要通过网络传输（如主从复制场景下网络带宽有限）时，可采用此模式来节省资源。 - 对于只关心数据变更部分，不要求完整记录每行数据的场景。 | INSERT 操作：执行 INSERT INTO users VALUES (1, 'Alice', 25);，binlog 会记录 (1, 'Alice', 25)，与 FULL 模式相同。UPDATE 操作：若执行 UPDATE users SET age = 26 WHERE id = 1;，binlog 只记录更新列 age 的旧值 25 和新值 26。DELETE 操作：执行 DELETE FROM users WHERE id = 1;，binlog 只记录用于定位被删除行的关键列信息，如 id = 1。 |
NOBLOB | 记录除 BLOB（二进制大对象）和 TEXT（文本大对象）类型列之外的所有列的完整信息。对于 BLOB 和 TEXT 类型的列，只有当它们的值发生变化时，才会记录其变化部分。 | - 当表中包含大的 BLOB 或 TEXT 类型字段，且这些字段的内容通常不会频繁变更时，使用该模式可减少二进制日志的大小，同时仍能记录其他列的完整信息。 | INSERT 操作：假设有表 documents(id, title, content)，其中 content 为 TEXT 类型，执行 INSERT INTO documents VALUES (1, 'Report', 'This is a long report...');，binlog 会记录 id 和 title 列的完整信息，对于 content 列，可能只记录一个引用或占位符。UPDATE 操作：若执行 UPDATE documents SET title = 'New Report' WHERE id = 1;，binlog 会记录 title 列的更新信息，而 content 列若未改变则不记录。若 content 列有更新，则记录其变化部分。DELETE 操作：执行 DELETE FROM documents WHERE id = 1;，binlog 会记录除 content 外的其他列信息来定位被删除的行。 |
2.7 semi-sync
- 事务提交过程：在主库上，当一个事务执行完成并准备提交时，主库不会立即向客户端返回提交成功的响应。
- 日志传输与确认：主库会先将该事务的二进制日志（Binlog）发送给至少一个从库。从库接收到二进制日志并将其写入自己的中继日志（Relay Log）后，会向主库发送一个确认消息。
- 完成提交：主库在收到至少一个从库的确认消息后，才会真正提交该事务，并向客户端返回提交成功的响应。如果在一定时间内（由参数
rpl_semi_sync_master_timeout控制）主库没有收到从库的确认消息，主库会自动切换到异步复制模式，以保证事务能够继续正常提交。
可以主库设置 statement，minimal，从库设置 row，full，性能会比较好，但是需要注意可靠性问题，至少两个从可以考虑该方案
反过来主库设置 row，从库设置 statement 是不行的，因为从库基本不能还原原语句，最多全字段凑语句，但是效果不好
3. binlog 和主备同步性能
主备延迟常见原因：
- 机器负载
- 备库读压力
- 大事务
- 并发度
典型的大事务会导致主备同步监控呈现 45°，如下所示：
主备还有个场景会导致同步延迟，备库处理事务速度赶不上主库生成事务的速度，在下面这张图中可以看到，备库只用一个 sql_thread 线程消费 relay_log，单线程消费就会存在消费<生产的情况
可以采用并行复制策略加快备库消费速度，主要逻辑如下：
其主要思路有：
5.5 按表/按行
5.6 按库
5.7 LOGICAL_CLOCK
5.7.22 WRITESET
3.1 按表
将事务按表分发给 worker（按照表名 hash 决定分配给哪个 worder），如果存在一个事物操作多张表的情况，比如事务 1 操作表 A 和 B，事务 2 操作表 B，事务 3 操作表 B 和 C，那么当分发2 时，发现事务 1 所在 worker 有操作 B，分给事务 1 所在worker，分发事务 3 时发现事务 3 操作的表 B 跟事务 2 有交集，同样分发给事务 1 所在的 worker，这样能保证按表有序
- 如果待分发的事务跟所有 worker 处理的事务的表没有交集，按照 hash 结果分配给空闲 woker
- 如果只跟其中一个有交集，分发给该线程
- 如果跟多个线程处理的事务的表有交集，等待直到只有一个有交集为止
按表并行复制有个问题，如果存在热点表，会基本退化为单线程，解决办法是按行并行复制
3.2 按行
将事务按行分给不同 worker，分配规则为计算表名和行所有唯一键一起的 hash 决定分给哪个 worder 处理，等待逻辑跟行一致
- 如果带分发事务操作的行跟所有 worder 处理的事务关联的行没有交集，按照 hash 结果分配给空闲 woker
- 如果只跟其中一个有交集，分发给该线程
- 如果跟多个有交集，等待直到只有一个有交集为止
3.3 按库（官方）
官方在 5.6 版本出了第一版并行复制策略，按库并行复制，但是并不实用，因为我们一般是将所有业务关联的表都放在同一个库，这样按库跟以前的单线程没有任何区别
3.4 LOGICAL_CLOCK（官方）
主库可以并行提交的事务，在从库也可以并行执行，给主库并行提交的事务打上 LOGICAL_LOCK，从库将相同 LOGICAL_CLOCK 的事务分发给不同 worker 即可
3.5 WRITE_SET（官方）
在事务写 binlog 之前，把表名、所有唯一键的值计算一个 hash 值，写到 binlog 中，到从库根据 hash 的冲突判断能否并行，跟上面 3.2 的逻辑按行基本一致，优点为：
计算放在主库，主库计算很快，并且只需要主库做一次，所有从库都能收益，3.2 按行的计算在从库，从库首先需要解析行数据，取出来再计算，代价比较大，并且每个从库都需要计算一次
4. binlog 和数据恢复速度
开了 binlog 之后，可以做哪些准备，应对误删数据？
4.1 秒级恢复
MVCC 闪回，binlog 闪回、回收站、延迟备份
4.1.1 MVCC 闪回（不支持 DDL）
MVCC 闪回：起一个事务，利用 MVCC 能力，在这个事务期间被误删的数据，该事务中还能看到，能挽救，不过不靠谱，因为事务不提交，undo 会很多，查询会变慢
4.2 binlog 闪回（不支持 DDL）
要求 binlog 使用 row+full 配置，误删后，利用 binlog 从后往前重做数据恢复，比如要恢复到事务 M，当前值是事务 X，那么从 X 开始往前一直恢复到 M，并且每个事务内的操作也是从后往前恢复，如下所示：
for transaction N .. 1:
reverse events in trans
for events in trans
insert -> delete
update A to B-> update B to A
delete -> insert
end for
end for
4.3 回收站
将删除表操作改为 rename 操作，这样误删后可以通过回收站来恢复，回收站可以指定保留时间
drop/truncate table -> rename
4.4 延迟备份
MySQL 支持从库配置 relay log 应用时间，比如延迟 2 小时，2 小时内的 relay log 不应用，之外的才应用，这样能达到备库数据延迟主库 2 小时，一旦主库出现误删操作，可以在从库上将对应操作的 binlog 删掉，会这将 gtid 设置为已应用，跳过误删语句，能达到恢复数据的目的
4.2 小时级恢复方案
全量+binlog 回放（过滤+并行）：这种要求数据库有定期全量备份，比如逻辑备份 mysqldump 或者物理备份 Percona XtraBackup，同时 binlog 文件也是随时备份，比如一旦切 binlog，就将 binlog 传输到云上保存
4.2.1 恢复流程
在全量备份的和 binlog 备份的前提下，如果需要恢复数据，流程为：
-
恢复全量备份 # 示例：通过 mysqldump 备份恢复 mysql -u root -p < full_backup.sql
-
确定 binlog 起始点
- 关键信息：全量备份文件中记录的 binlog 位置（例如
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=154;）。 - 如何记录：使用
mysqldump备份时需添加--master-data=2参数，自动记录 binlog 位置，如果使用 gtid 恢复数据，可以添加--gtid-set参数来记录当前的 GTID 集合。
- 关键信息：全量备份文件中记录的 binlog 位置（例如
-
回放 binlog
- 从全量备份记录的 binlog 位置开始，回放到崩溃前的最后一个 binlog 或指定时间点：
# 示例：回放 binlog 文件 mysqlbinlog mysql-bin.000001 mysql-bin.000002 --start-position=154 | mysql -u root -p # 指定时间点恢复（恢复至 2023-10-01 12:00:00） mysqlbinlog --start-position=154 --stop-datetime="2023-10-01 12:00:00" mysql-bin.* | mysql -u root -p
4.2.2 如何加速恢复流程
- sync_binlog 和 innodb_flush_log_at_trx_commit 关掉双1(甚至关掉 binlog)
- 只回放目标表
- 并行回放 (模拟 relaylog)
5. binlog 和主备切换速度
主备切换主要涉及三个步骤：
- 探测
- 切换
- 恢复服务
云厂商提供的主备切换的 SLA 时间一般为 30 秒，说明上述三个步骤还是需要花费一定时间的，主要花费时间在探测上
5.1 探测
| 探测方法 | 方法介绍 | Badcase（可能出现的问题） |
|---|---|---|
| ping | 通过向目标主机发送 ICMP（Internet 控制消息协议）回显请求数据包（ping 包），并等待目标主机返回 ICMP 回显应答数据包。若在规定时间内收到应答，则认为目标主机可达；若未收到应答，则认为目标主机不可达或存在网络问题。常用于检测网络连通性，在主备切换场景中，可初步判断主节点的网络可达性。 |
|
| connect | 尝试与目标主机上的特定服务端口建立 TCP 连接。如果连接成功，说明目标主机上的该服务端口正在监听且网络连接正常；如果连接失败，则表示服务不可用或网络存在问题。在主备切换场景中，可用于检测数据库、应用服务等是否正常监听端口。 |
|
| connect + select 1 | 先尝试与目标数据库建立连接，连接成功后执行 SELECT 1 语句。SELECT 1 是一个简单的 SQL 查询，用于验证数据库是否能够正常响应查询请求。如果连接成功且查询正常返回结果（通常返回一个值为 1 的结果集），则认为数据库服务正常；否则，认为数据库服务存在问题。 |
|
| connect + select from …… limit 1 | 先与目标数据库建立连接，然后执行一个从特定表中查询一条记录的 SQL 语句（SELECT * FROM …… LIMIT 1）。该方法比 SELECT 1 更能反映数据库的实际业务情况，因为它涉及到对表的查询操作。如果连接成功且查询正常返回结果，则认为数据库服务正常；否则，认为数据库服务存在问题。 |
|
| connect + update …… set last_monitor_time = now() | 先与目标数据库建立连接，然后执行一个更新语句，将表中的 last_monitor_time 字段更新为当前时间。该方法不仅可以检测数据库的连接和查询能力，还能检测数据库的写入能力。如果连接成功且更新操作正常执行，则认为数据库服务正常；否则，认为数据库服务存在问题。 |
|
上述探测方法都不完美，主要是探测频率、异常处理和重试等逻辑不好完美兼容，重试存在以下问题：
- 多久重试一次
- 重试几次
5.2 切换
- 等延迟 < 1
- 双 RO（Readonly）
- 等 slave 完全追上
- 切业务流量
- 数据一致性校验
- 新主关掉 RO
- 修改主备关系
6. 答疑
-
binlog.999999之后再
flush binary log，会怎样？会变为 binlog.1000000，这里 binlog 的编号上限是整形大小，即 2^32-1，如果达到这个大小后再 flush binary logs，此时 MySQL 会直接 crash
该序号正常情况下可以用来表示 binlog 文件生成时间
-
服务一直开着，binlog 会不会把磁盘打爆？
MySQL 有两个配置项：
- binlog_expire_logs_auto_purge | ON（是否自动删除 binlog）
- binlog_expire_logs_seconds |259200|（自动删除多久之前的 binlog，默认 30 天）
这两个配置可以保证只会保留 30 天内的 binlog，如果磁盘能容纳 30 天内 binlog，那么不会把磁盘打爆
MySQL 会在每次切 binlog 的时候，顺手判断是否有 binlog 时间是在 30 天之前的，如果有，就会将该文件清理掉
如果使用 extrabackup 工具会有一个 bug 存在，会导致过期 binlog 无法被删除，原因是 extrabackup 认为它自己主动切 log 文件，不应该导致 MySQL 系统文件被删除，会执行
LOCK INSTANCE FOR BACKUP，不会走上面两个参数控制的逻辑，因此如果每天只有一个 binlog，并且在 binlog 主动切之前 extraback 工具执行切换，那么主动清理过期 binlog 文件的逻辑永远不会走到
