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

秃秃爱健身

已于 2022-05-12 22:34:26 修改

阅读量2.5k

点赞数 5

分类专栏： mysql 文章标签： mysql java

于 2022-04-16 20:54:40 首次发布

本文链接：https://blog.csdn.net/Saintmm/article/details/124218015

版权

mysql 专栏收录该内容

21 篇文章 7 订阅

订阅专栏

一、确定问题

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

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

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支持三种类型的格式：

STATEMENT：基于SQL语句的复制（statement-based replication, SBR）；
1> 每一条会修改数据的sql都会记录在binlog中；

2> 优点：
- 不需要记录每一行的变化，减少了binlog日志量，节约了IO, 提高了性能；
3> 缺点：
- 由于记录的只是执行的SQL语句，为了这些SQL语句能在slave上正确运行，还必须记录每条语句在执行时的一些相关信息。
- 另外mysql的复制，像一些特定函数的功能，slave与master要保持一致会有很多相关问题。
- 所以有了Row这种格式；
ROW：基于行的复制（row-based replication, RBR）；
1> 5.1.5版本的MySQL才开始支持 row level 的复制，它不记录sql语句上下文的相关信息，仅保存哪条记录被修改；

2> 优点：
- row的日志内容会非常清楚的记录下每一行数据修改的细节；
- 不会出现某些情况下：存储过程、function、以及trigger的调用和触发无法被正确复制到slave的问题。
3> 缺点：
- 所有执行的SQL语句当记录到binlog中时，都将以每行记录的修改来记录，这样可能会产生大量的日志内容。
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的存储方式？

binlog文件以一个值为0Xfe62696e的魔数开头，这个魔数对应0xfe ‘b’‘i’‘n’；
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 person

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

内容如下：
在这里插入图片描述

秃秃爱健身

关注

5
点赞
踩
10

收藏

觉得还不错? 一键收藏
打赏
0
评论
delete from xx_table（删除全表数据）对应的binlog是怎么记录的？

面试官：delete from xx_table（删除全表数据）对应的binlog是怎么记录的？binlog事件的类型有哪些？binlog事件记录的格式类型有哪些？
复制链接

扫一扫