delete from xx_table（删除全表数据）对应的binlog是怎么记录的？

一、确定问题

像这种听起来就比较难的问题，我们首先要核实面试官想问的和我们理解的问题是同一个吗？ 避免回答错误。

这道题可以从两个方面去聊：

• 1> binlog的日志存储结构，即：binlog日志是如何存储到binlog文件的？
• 2> binlog相关的一些命令，表述出自己有对binlog做过深入研究，实际看过binlog的文件内容。

因为博主在项目经验中提到了很多binlog+canal的操作，深入binlog问也算是对博主的尊重。不过很可惜，博主当时并没答出来，借机在此总结一下。

二、答题思路

1、binlog的日志存储结构

1）binlog是什么？

MySQL 的二进制日志 binlog 可以说是 MySQL 最重要的日志；

• 它是MySQL server层的日志，以事件的形式记录了所有的DDL和DML语句（处了数据查询查询语句select、show）；
• 主要用于主从复制、和宕机重启的数据恢复（使用mysqlbinlog工具来使恢复数据）。

2）binlog的事件格式类型？

记录在binlog中的事件格式取决于binlog的记录格式。binlog支持三种类型的格式：

1. STATEMENT：基于SQL语句的复制（statement-based replication, SBR）；

1> 每一条会修改数据的sql都会记录在binlog中；

2> 优点：

• 不需要记录每一行的变化，减少了binlog日志量，节约了IO, 提高了性能；

3> 缺点：

• 由于记录的只是执行的SQL语句，为了这些SQL语句能在slave上正确运行，还必须记录每条语句在执行时的一些相关信息。
• 另外mysql的复制，像一些特定函数的功能，slave与master要保持一致会有很多相关问题。
• 所以有了​​Row​​这种格式；

1. ROW：基于行的复制（row-based replication, RBR）；

1> 5.1.5版本的MySQL才开始支持 ​​row level​​ 的复制，它不记录sql语句上下文的相关信息，仅保存哪条记录被修改；

2> 优点：

• row的日志内容会非常清楚的记录下每一行数据修改的细节；
• 不会出现某些情况下：存储过程、function、以及trigger的调用和触发无法被正确复制到slave的问题。

3> 缺点：

• 所有执行的SQL语句当记录到binlog中时，都将以每行记录的修改来记录，这样可能会产生大量的日志内容。

1. MIXED：混合模式复制（mixed-based replication, MBR）

1> 实际就是Statement和Row的集结合。

2> 在Mixed模式下，一般的语句修改使用statment格式保存到binlog；比如一些函数、存储过程，​​statement​​格式无法完成MySQL的主从复制操作，则采用row格式保存binlog。

3> 也就是说MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式。

在 ​​MySQL 5.7.7 之前​​​，​​默认​​​的格式是 ​​STATEMENT​​​，在 ​​MySQL 5.7.7 及更高版本中​​​，​​默认格式是 ROW​​​。
此外，日志的格式可以通过在配置文件中使用 ​​​binlog-format​​ 属性指定，如binlog-format=MIXED。

3）binlog事件的类型？

常见的几个如下：

事件类型	说明
QUERY	执行更新语句时生成此事件，包括：create，insert，update，delete
WRITE_ROWS	用在binlog_format为ROW模式下，对应 insert 操作
UPDATE_ROWS	用在binlog_format为ROW模式下，对应 update 操作
DELETE_ROWS	用在binlog_format为ROW模式下，对应 delete 操作
STOP	当mysqld停止时生成此事件

4）binlog的存储方式？

1. binlog文件以一个值为0Xfe62696e的魔数开头，这个魔数对应0xfe ‘b’‘i’‘n’；
2. binlog由一系列的binlog event构成。每个binlog event包含header和data两部分；

• header部分提供的是event的公共的类型信息，包括event的类型、event的创建时间、event的长度、下一个event的位置等等；
• data部分提供的是针对该event的具体信息，如具体数据的修改；

1> event具体的的数据结构如下：

+=====================================+
| event  | timestamp         0 : 4    |
| header +----------------------------+
|        | type_code         4 : 1    |
|        +----------------------------+
|        | server_id         5 : 4    |
|        +----------------------------+
|        | event_length      9 : 4    |
|        +----------------------------+
|        | next_position    13 : 4    |
|        +----------------------------+
|        | flags            17 : 2    |
|        +----------------------------+
|        | extra_headers    19 : x-19 |
+=====================================+
| event  | fixed part        x : y    |
| data   +----------------------------+
|        | variable part              |
+=====================================+

2、binlog相关的一些命令？

1）开启binlong、查看binlog信息

1> 查询是否启用了binlog日志：

show variables like 'log_bin';

2> 查看binlog的目录信息，其中log_bin_basename表示binlog文件的命名规则，log_bin_index表示binlog的索引：

show global variables like '%log_bin%';

3> 查看当前服务器使用的binlog文件及其大小；

show binary logs;

4> 查看binlog的事件内容；

show binlog events;

5> 查看bin-log的二进制文件；
进入到MYSQL的安装目录的bin/目录下，执行下列命令，以文本行的形式查看binlog二进制文件

./mysqlbinlog -v --base64-output=decode-rows /www/server/data/mysql-bin.000001

注：这里使用的binlog的事件格式为：MIXED，从内容来看MIXED格式中进一步选择了​​​STATEMENT​​格式。

2）binlog日志清理

1> 设置binlog保存事件的过期删除时间，单位为：天

set global expire_log_days=3;

2> 删除主服务器的binlog文件

reset master;

执行完这个命令我们再去看binlog日志内容，发现没有了上面的插入、删除操作等等的操作记录：

3> 删除slave服务器中的中继日志

reset slave;

4> 删除指定日期前 日志索引中的binlog日志文件

purge master logs before '2022-04-09 01:00:00';

3、具体的delete全表数据SQL对应的binlog内容？

1> 如果binlog的事件格式类型是​​MIXED​​ 或 ​​STATEMENT​​，则记录的是一条SQL语句：

DELETE from

binlog日志内容如下：

此时的事件类型为Query；

2> 如果binlog的事件格式类型是​​ROW​​，则记录的是每一行数据修改的细节，比如：delete from person where id=1 and name=‘a’ and age=18; id=2… id=3…等等等

### DELETE FROM `test`.`person`
### WHERE
###   @1=1
###   @2='a'
###   @3=18
### DELETE FROM `test`.`person`
### WHERE
###   @1=2
###   @2='b'
###   @3=19
### DELETE FROM `test`.`person`
### WHERE
###   @1=3
###   @2='c'
###   @3=20
### DELETE FROM `test`.`person`
### WHERE
###   @1=4
###   @2='d'
###   @3=21
### DELETE FROM `test`.`person`
### WHERE
###   @1=5
###   @2='e'
###   @3=22

binlog日志内容如下：

此时的事件类型为：delete_rows

4、附：变更binlog事件格式类型的方式（从MIXED到ROW）

第一步：先删除​​mysql-bin​​​相关的日志文件和日志索引文件；（mysql-bin为在配置文件中配置的​​log-bin​​属性的值）

第二步：修改MYSQL配置文件中的​​binlog-format​​​属性为​​ROW​​

第三步：重启MYSQL进程 再执行​​DELETE from person​​语句；然后再查看binlog日志文件内容；

1> 执行完​​DELETE from person​​语句后，binlog日志文件重新生成；

2> 查看 ​​binlog​​日志文件内容

• 进入到MYSQL的安装目录的bin/目录下，执行下列命令，以文本行的形式查看binlog二进制文件

./mysqlbinlog -v --base64-output=decode-rows /www/server/data/mysql-bin.000001

内容如下：