binlog从基础到精通，24张图吃透，MySQL调优必会

Bin log 保证了数据的可靠性和一致性，MySQL 数据库的数据备份、主备、主主、主从等的实现，都依赖于 Bin log。

毫不夸张地说，Bin log 是 MySQL 极为重要的内核之一，同时，Bin log 也是 MySQL 的高频面试题，大厂校招、社招面试90%会问到。

大家好，我是爱分享的程序员宝妹儿，分享即学习。

本篇是 Bin log 的终结篇，包含了 Bin log 系列的全部内容，想要了解 Bin log 的成体系知识，看这一篇就足够了，宝子们，看了记得关注+收藏哦。

本文内容包括：

• Bin log 基础概述：概念、优缺点、使用场景、配置、参数等
• 基于 Bin log 实现 MySQL 主从复制，5个必看关键步骤
• Bin log 恢复 MySQL 数据，7步快速搞定
• Bin log 三大模式：Statement、Row、Mixed
• Bin log 增加过快，影响 MySQL 性能怎么办
• Bin log、redo log和Undo log的区别，2分钟吃透

PS.

宝妹儿已将Bin log 系列，整理到2023版《MySQL 大厂高频面试题大全》PDF了，方便系统学习、面试通关。

《MySQL 大厂高频面试题大全》PDF，已收录100+道真题，一共78页，近30000字，需要的小伙伴自取。

吃透它，足以应付MySQL面试。

01

Bin log 的概念

在 MySQL 中，Bin log 是一种日志文件，以二进制形式记录事务的开始、提交和回滚，以及数据的增、删、改操作。

通过 Bin log，我们可以追踪和还原数据库中的操作历史。

02

Bin log 的作用

Bin log 的两个主要作用：

2.1 数据恢复

Bin log 记录了每个事务的详细操作，包括数据的修改和事务的状态变更。

使用 Bin log 恢复 MySQL 数据，简单地说，就是让 MySQL 将保存在 Bin log 日志中指定段落区间的 sql 语句，逐个重新执行一次而已。

Bin log 恢复数据主要有如下 7 个步骤：

1）查看当前 Bin log 的位置

2）tb_person 表中插入两条记录

3）记录当前 Bin log 位置

4）查询数据

......

查看详解篇：

2.2 主从复制

Bin log 是实现主从复制的关键，主数据库将修改操作记录到 Bin log 中，从数据库通过解析 Bin log 实现数据的同步。

MySQL 主从复制中的主要线程

• master（Bin log dump thread）
• slave（I/O thread 、SQL thread）
• Master 的一条线程
• Slave 中的两条线程

查看详解篇：

03

Bin log 日志结构

Bin log 日志文件包含了索引文件和具体日志文件。

• 索引文件：用于跟踪日志文件，每行一个日志文件。默认情况下，索引文件名为{Host名}-bin.index。
• 日志文件：是由一系列事件(Binary Log Events)组成。默认情况下，文件名为{Host名}-bin.NNNNNN。后缀六个数字，是编号，用于区分不同的日志文件。

每个 Bin log 事件由四个部分组成：

• 通用 Header：存放事件的基本信息：事件类型和事件数据大小。
• Post Header：存放特定事件类型的相关信息
• 事件实体：存储事件的数据，如执行过的语句和变更的实际数据
• Checksum：MySQL5.6 新特性，用于检查数据是否损坏。

04

Bin log 的相关参数

• log_bin ：启用 Bin log 功能，并指定路径名称
• log_bin_index ：指定二进制索引文件的路径与名称
• Bin log_do_db ：只记录指定数据库的二进制日志
• Bin log_ignore_db ：不记录指定的数据库的二进制日志
• max_Bin log_cache_size ：Bin log 使用的内存最大的尺寸
• Bin log_cache_size ：Bin log 使用的内存大小
• Bin log_cache_use ：使用二进制日志缓存的事务数量
• Bin log_cache_disk_use : 使用二进制日志缓存
• max_Bin log_size ：Bin log 最大值，最大和默认值是1GB
• sync_Bin log ：这个参数直接影响MySQL的性能和完整性
• sync_Bin log=0 ：当事务提交后，MySQL 仅仅是将 Bin log_cache 中的数据写入 Bin log 文件，但不执行 fsync 之类的磁盘 同步指令通知文件系统将缓存刷新到磁盘，而让Filesystem自行决定什么时候来做同步，这个是性能最好的。
• sync_Bin log=n ：在进行n次事务提交以后，MySQL 将执行一次fsync之类的磁盘同步指令，同志文件系统将 Bin log 文件缓存刷新到磁盘。

在 MySQL 中，默认设置是 sync_Bin log=0 ，即不作任何强制性的磁盘刷新指令。

sync_Bin log=0 的性能最好、但风险也最大，一旦系统绷 Crash ，在文件系统缓存中的所有 Bin log 信息都会丢失。

05

Bin log 的三种模式

针对不同的应用场景，Bin log 推出了三种模式 Statement、Row、Mixed，以满足对数据库的需求。

Bin log 模式的优缺点：

• statement： 基于 SQL 语句的模式，某些语句和函数如 UUID, LOAD DATA INFILE 等在复制过程可能导致数据不一致甚至出错。
• row： 基于行的模式，记录的是行的变化，很安全。但是 Bin log 会比其他两种模式大很多，在一些大表中清除大量数据时在 Bin log 中会生成很多条语句，可能导致从库延迟变大。
• mixed： 混合模式，根据语句来选用是 statement 还是 row 模式。

Bin log 模式选型思路：

• 使用 MySQL 特殊功能较少，例如存储过程、触发器、函数等，用 Statement 模式。
• 使用 MySQL 特殊功能较多，用 Mixed 模式。
• 使用 MySQL 特殊功能较多，同时希望数据最大化一致，用 Row 模式。

关于 Bin log 三种模式 Statement、Row、Mixed，及其特点、优缺点、应用场景、配置、切换等。

具体查看详解篇：

06

Bin log 的开启、关闭、删除

Bin log 的开启

在 MySQL 配置文件 my.cnf 中增加log-bin参数，即可开启 Bin log。

[MySQLd]
log-bin=MySQL-bin           

通过命令行的方式开启 Bin log：

SET SQL_LOG_BIN=1           

Bin log 的关闭

通过命令行的方式关闭 Bin log：

SET SQL_LOG_BIN=0           

Bin log 的删除

过期删除

MySQL> show variables like 'expire_log_days';
MySQL> set global expire_log_days=7;           

reset master 会删除所有 Bin log，reset slave 会删除所有 relay log。

07

Bin log 空间查询

在数据库管理中，定期查询和监控 Bin log 的空间占用情况，评估和监控系统的运行状态，预防磁盘空间不足和性能问题的发生，确保数据库的正常运行。

此时，我们可以通过 MySQL 的内置命令和查询，来评估和监控 Bin log 的大小和增长趋势。

一些查询 Bin log 空间的常用方法和命令：

• SHOW VARIABLES LIKE 'max_Bin log_size'; ：用于查询 Bin log 的最大大小限制，这对于评估是否需要调整 Bin log 的大小很有用。
• SHOW BINARY LOGS; ：用于查询当前已生成的 Bin log 文件列表，了解已使用的 Bin log 空间，以及每个 Bin log 文件的大小和生成时间等信息。

可以将这些命令嵌入到脚本或管理工具中，实现自动化的 Bin log 空间查询和监控。

08

Bin log 增长过快怎么办？

当 Bin log 增长过快时，磁盘空间占用过多，就会导致磁盘空间不足或性能下降等问题，影响数据库性能和稳定性。

Bin log 增长过快的主要原因：

• 大事务
• 频繁的 DDL 操作
• 长时间的读事务
• 错误的配置参数

Bin log（归档日志）增长过快的解决方案：

• 拆分大事务
• 优化 DDL 操作

......

详解篇：

09

Bin log、redolog 和 undolog 的区别

Bin log、Redo log 和 Undo log 的区别，是 MySQL 的高频面试题，大厂校招面试90%会问。

在 MySQL 数据库中，Bin log、Redo log 和 Undo log 都是极为重要的日志文件。

MySQL InnoDB 引擎:

使用 redo log （重做日志）保证事务的持久性。

使用 Undo log （回滚日志）来保证事务的原子性。

使用 Bin log（归档日志）同步数据，保证数据一致性。

MySQL 实现事务、崩溃恢复、集群的主从复制等，底层都离不开日志，可以说日志是 MySQL 的内核所在。

只有了解 MySQL 日志，才算是彻底搞懂 MySQL。

详解篇：

10

总结

到这里，宝妹儿的 Bin log 系列创作暂时就告一段落了，未来还会继续补充完善，更多体系化的内容分享持续送达。

最后，谢谢“记**记”、“E***T”、....等小伙伴们的支持，向宝妹儿提供了很多非常棒的建议.....

谢谢您的关注、点赞、建议，这是鼓励宝妹儿坚持前行的重要力量。

PS.

Bin log 系列已收录于宝妹儿精编的 2023版《MySQL 大厂高频面试题大全》PDF。

《MySQL 大厂高频面试题大全》一共78页，34000字，图文并茂，长期持续更新。

吃透它，足以应对 MySQL 面试，如图自取所需。