MySQL的binlog归档日志（一文彻底吃透，总结超详细）

提到 MySQL 的日志文件，就一定绕不开 Bin log。

Bin log 的作用非常重要，MySQL 数据库的数据备份、主备、主主、主从都离不开 Bin log。

Bin log 保证了数据的可靠性和一致性。

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

本文是 Bin log 系列的总结篇，内容包括：Bin log 的概念、优缺点、使用场景、配置、主从复制、数据恢复、三大模式、空间占用解决方案、以及Bin log、redo log和Undo log的区别等。

binlog从基础到精通，24张图全面总结，一文彻底吃透！

PS.

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

宝妹儿精编的2023版《MySQL 大厂高频面试题大全》，已收录100+道真题，近30000字，长期迭代、持续更新。吃透它，应付MySQL面试没问题。

请拉到文末自取。

1. Bin log 的概念

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

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

2. Bin log 的作用

Bin log 的两个主要作用：

2.1 数据恢复

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

当数据库发生故障或意外操作时，可以通过回放 Bin log 来还原数据。

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

1）查看当前 Bin log 的位置

2）tb_person 表中插入两条记录

3）记录当前 Bin log 位置

4）查询数据

5）删除一条: delete from tb_person where name =”person_2″;

6）Bin log 恢复（指定 pos 点恢复/部分恢复）

7）数据恢复完成

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

查看详解篇：Bin log数据恢复，7个步骤搞定，再不怕MySQL宕机了

2.2 主从复制

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

MySQL 主从复制中的主要线程

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

binlog从基础到精通，24张图全面总结，一文彻底吃透！

查看详解篇：基于Bin log实现MySQL复制，5个关键步骤务必掌握！

3. Bin log 日志结构

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

binlog从基础到精通，24张图全面总结，一文彻底吃透！

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

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

binlog从基础到精通，24张图全面总结，一文彻底吃透！

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

4. 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 信息都会丢失。

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，及其特点、优缺点、应用场景、配置、切换等，查看详解篇：吃透3大Bin log模式，MySQL数据零丢失

5. 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。

6. 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 空间查询和监控。