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

叁歲伎倆 2023-09-29 10:30 11阅读 0赞

### 一、确定问题 ###

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

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

*  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`这种格式；
2.  **ROW**：基于行的复制（row-based replication, RBR）；
    
    > **1> 5.1.5版本的MySQL才开始支持 `row level` 的复制，它不记录sql语句上下文的相关信息，仅保存哪条记录被修改；**
    > 
    > **2> 优点：**
    > 
    >  *  row的日志内容会非常清楚的记录下每一行数据修改的细节；
    >  *  不会出现某些情况下：存储过程、function、以及trigger的调用和触发无法被正确复制到slave的问题。
    > 
    > **3> 缺点：**
    > 
    >  *  所有执行的SQL语句当记录到binlog中时，都将以每行记录的修改来记录，这样可能会产生大量的日志内容。
3.  **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。

![请添加图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA56eD5aS054ix5YGl6Lqr_size_20_color_FFFFFF_t_70_g_se_x_16]

##### 3）binlog事件的类型？ #####

**常见的几个如下：**

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

##### 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具体的的数据结构如下：**

#### 2、binlog相关的一些命令？ ####

##### 1）开启binlong、查看binlog信息 #####

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

show variables like 'log_bin';

![在这里插入图片描述][d592b5d4ae4341a0b6ab598c90ea1cc3.png]

**2> 查看binlog的目录信息，其中log\_bin\_basename表示binlog文件的命名规则，log\_bin\_index表示binlog的索引：**

show global variables like '%log_bin%';

![在这里插入图片描述][d6fb82372e554caaaba6a364dfbc6e3f.png]

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

show binary logs;

![在这里插入图片描述][e1c8cfdacf5f431684274bf0604bd551.png]

**4> 查看binlog的事件内容；**

show binlog events;

![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA56eD5aS054ix5YGl6Lqr_size_20_color_FFFFFF_t_70_g_se_x_16 1]

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

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

![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA56eD5aS054ix5YGl6Lqr_size_20_color_FFFFFF_t_70_g_se_x_16 2]  
**注：这里使用的binlog的事件格式为：MIXED，从内容来看MIXED格式中进一步选择了`STATEMENT`格式。**

##### 2）binlog日志清理 #####

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

set global expire_log_days=3;

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

reset master;

执行完这个命令我们再去看binlog日志内容，发现没有了上面的插入、删除操作等等的操作记录：  
![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA56eD5aS054ix5YGl6Lqr_size_20_color_FFFFFF_t_70_g_se_x_16 3]

**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 person

binlog日志内容如下：  
![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA56eD5aS054ix5YGl6Lqr_size_20_color_FFFFFF_t_70_g_se_x_16 4]  
**此时的事件类型为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日志内容如下：

![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA56eD5aS054ix5YGl6Lqr_size_20_color_FFFFFF_t_70_g_se_x_16 5]  
**此时的事件类型为：delete\_rows**  
![在这里插入图片描述][dd50ecd445684df89dacb477ad5c0560.png]

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

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

![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA56eD5aS054ix5YGl6Lqr_size_20_color_FFFFFF_t_70_g_se_x_16 6]

第二步：修改MYSQL配置文件中的`binlog-format`属性为`ROW`  
![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA56eD5aS054ix5YGl6Lqr_size_20_color_FFFFFF_t_70_g_se_x_16 7]

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

1> 执行完`DELETE from person`语句后，binlog日志文件重新生成；  
![在这里插入图片描述][196a86ccd284499ca3dae7ac81f506f7.png]  
2> 查看 `binlog`日志文件内容

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

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

内容如下：  
![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA56eD5aS054ix5YGl6Lqr_size_20_color_FFFFFF_t_70_g_se_x_16 5]

[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA56eD5aS054ix5YGl6Lqr_size_20_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/849cde3896fd409991b18a00d2cd4874.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA56eD5aS054ix5YGl6Lqr,size_20,color_FFFFFF,t_70,g_se,x_16
[d592b5d4ae4341a0b6ab598c90ea1cc3.png]: https://img-blog.csdnimg.cn/d592b5d4ae4341a0b6ab598c90ea1cc3.png
[d6fb82372e554caaaba6a364dfbc6e3f.png]: https://img-blog.csdnimg.cn/d6fb82372e554caaaba6a364dfbc6e3f.png
[e1c8cfdacf5f431684274bf0604bd551.png]: https://img-blog.csdnimg.cn/e1c8cfdacf5f431684274bf0604bd551.png
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA56eD5aS054ix5YGl6Lqr_size_20_color_FFFFFF_t_70_g_se_x_16 1]: https://img-blog.csdnimg.cn/f2c24f5b843a44f7bed7264a290990bd.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA56eD5aS054ix5YGl6Lqr,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA56eD5aS054ix5YGl6Lqr_size_20_color_FFFFFF_t_70_g_se_x_16 2]: https://img-blog.csdnimg.cn/c151e7252c1f4cdebee2901e81c9aa12.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA56eD5aS054ix5YGl6Lqr,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA56eD5aS054ix5YGl6Lqr_size_20_color_FFFFFF_t_70_g_se_x_16 3]: https://img-blog.csdnimg.cn/7e6805e68c9a4025a584bdb465ab4eb1.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA56eD5aS054ix5YGl6Lqr,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA56eD5aS054ix5YGl6Lqr_size_20_color_FFFFFF_t_70_g_se_x_16 4]: https://img-blog.csdnimg.cn/d6d3d9928b4c4db8819cf4e8f6e2d703.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA56eD5aS054ix5YGl6Lqr,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA56eD5aS054ix5YGl6Lqr_size_20_color_FFFFFF_t_70_g_se_x_16 5]: https://img-blog.csdnimg.cn/ef37e12eeff840ff86871e1d3ef4b88d.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA56eD5aS054ix5YGl6Lqr,size_20,color_FFFFFF,t_70,g_se,x_16
[dd50ecd445684df89dacb477ad5c0560.png]: https://img-blog.csdnimg.cn/dd50ecd445684df89dacb477ad5c0560.png
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA56eD5aS054ix5YGl6Lqr_size_20_color_FFFFFF_t_70_g_se_x_16 6]: https://img-blog.csdnimg.cn/58e6806ce6ac4ebeb660f9f60556eba3.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA56eD5aS054ix5YGl6Lqr,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA56eD5aS054ix5YGl6Lqr_size_20_color_FFFFFF_t_70_g_se_x_16 7]: https://img-blog.csdnimg.cn/7f7d85b661014bc7af2e2ccefadd2619.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA56eD5aS054ix5YGl6Lqr,size_20,color_FFFFFF,t_70,g_se,x_16
[196a86ccd284499ca3dae7ac81f506f7.png]: https://img-blog.csdnimg.cn/196a86ccd284499ca3dae7ac81f506f7.png