一、確定問(wèn)題

像這種聽(tīng)起來(lái)就比較難的問(wèn)題，我們首先要核實(shí)面試官想問(wèn)的和我們理解的問(wèn)題是同一個(gè)嗎？ 避免回答錯(cuò)誤。

這道題可以從兩個(gè)方面去聊：

• 1> binlog的日志存儲(chǔ)結(jié)構(gòu)，即：binlog日志是如何存儲(chǔ)到binlog文件的？
• 2> binlog相關(guān)的一些命令，表述出自己有對(duì)binlog做過(guò)深入研究，實(shí)際看過(guò)binlog的文件內(nèi)容。

因?yàn)椴┲髟陧?xiàng)目經(jīng)驗(yàn)中提到了很多binlog+canal的操作，深入binlog問(wèn)也算是對(duì)博主的尊重。不過(guò)很可惜，博主當(dāng)時(shí)并沒(méi)答出來(lái)，借機(jī)在此總結(jié)一下。

二、答題思路

1、binlog的日志存儲(chǔ)結(jié)構(gòu)

1）binlog是什么？

MySQL 的二進(jìn)制日志 binlog 可以說(shuō)是 MySQL 最重要的日志；

• 它是MySQL server層的日志，以事件的形式記錄了所有的DDL和DML語(yǔ)句（處了數(shù)據(jù)查詢查詢語(yǔ)句select、show）；
• 主要用于主從復(fù)制、和宕機(jī)重啟的數(shù)據(jù)恢復(fù)（使用mysqlbinlog工具來(lái)使恢復(fù)數(shù)據(jù)）。

2）binlog的事件格式類型？

記錄在binlog中的事件格式取決于binlog的記錄格式。binlog支持三種類型的格式：

1. STATEMENT：基于SQL語(yǔ)句的復(fù)制（statement-based replication, SBR）；

   1> 每一條會(huì)修改數(shù)據(jù)的sql都會(huì)記錄在binlog中；

   2> 優(yōu)點(diǎn)：

   • 不需要記錄每一行的變化，減少了binlog日志量，節(jié)約了IO, 提高了性能；

   3> 缺點(diǎn)：

   • 由于記錄的只是執(zhí)行的SQL語(yǔ)句，為了這些SQL語(yǔ)句能在slave上正確運(yùn)行，還必須記錄每條語(yǔ)句在執(zhí)行時(shí)的一些相關(guān)信息。
   • 另外mysql的復(fù)制，像一些特定函數(shù)的功能，slave與master要保持一致會(huì)有很多相關(guān)問(wèn)題。
   • 所以有了Row這種格式；

2. ROW：基于行的復(fù)制（row-based replication, RBR）；

   1> 5.1.5版本的MySQL才開(kāi)始支持 row level 的復(fù)制，它不記錄sql語(yǔ)句上下文的相關(guān)信息，僅保存哪條記錄被修改；

   2> 優(yōu)點(diǎn)：

   • row的日志內(nèi)容會(huì)非常清楚的記錄下每一行數(shù)據(jù)修改的細(xì)節(jié)；
   • 不會(huì)出現(xiàn)某些情況下：存儲(chǔ)過(guò)程、function、以及trigger的調(diào)用和觸發(fā)無(wú)法被正確復(fù)制到slave的問(wèn)題。

   3> 缺點(diǎn)：

   • 所有執(zhí)行的SQL語(yǔ)句當(dāng)記錄到binlog中時(shí)，都將以每行記錄的修改來(lái)記錄，這樣可能會(huì)產(chǎn)生大量的日志內(nèi)容。

3. MIXED：混合模式復(fù)制（mixed-based replication, MBR）

   1> 實(shí)際就是Statement和Row的集結(jié)合。

   2> 在Mixed模式下，一般的語(yǔ)句修改使用statment格式保存到binlog；比如一些函數(shù)、存儲(chǔ)過(guò)程，statement格式無(wú)法完成MySQL的主從復(fù)制操作，則采用row格式保存binlog。

   3> 也就是說(shuō)MySQL會(huì)根據(jù)執(zhí)行的每一條具體的sql語(yǔ)句來(lái)區(qū)分對(duì)待記錄的日志形式。

在 MySQL 5.7.7 之前，默認(rèn)的格式是 STATEMENT，在 MySQL 5.7.7 及更高版本中，默認(rèn)格式是 ROW。
此外，日志的格式可以通過(guò)在配置文件中使用 binlog-format 屬性指定，如binlog-format=MIXED。

3）binlog事件的類型？

常見(jiàn)的幾個(gè)如下：

4）binlog的存儲(chǔ)方式？

1. binlog文件以一個(gè)值為0Xfe62696e的魔數(shù)開(kāi)頭，這個(gè)魔數(shù)對(duì)應(yīng)0xfe ‘b’‘i’‘n’；
2. binlog由一系列的binlog event構(gòu)成。每個(gè)binlog event包含header和data兩部分；
   • header部分提供的是event的公共的類型信息，包括event的類型、event的創(chuàng)建時(shí)間、event的長(zhǎng)度、下一個(gè)event的位置等等；
   • data部分提供的是針對(duì)該event的具體信息，如具體數(shù)據(jù)的修改；

1> event具體的的數(shù)據(jù)結(jié)構(gòu)如下：

+=====================================+
| 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相關(guān)的一些命令？

1）開(kāi)啟binlong、查看binlog信息

1> 查詢是否啟用了binlog日志：

show variables like 'log_bin';

2> 查看binlog的目錄信息，其中l(wèi)og_bin_basename表示binlog文件的命名規(guī)則，log_bin_index表示binlog的索引：

show global variables like '%log_bin%';

3> 查看當(dāng)前服務(wù)器使用的binlog文件及其大小；

show binary logs;

4> 查看binlog的事件內(nèi)容；

show binlog events;

5> 查看bin-log的二進(jìn)制文件；
進(jìn)入到MYSQL的安裝目錄的bin/目錄下，執(zhí)行下列命令，以文本行的形式查看binlog二進(jìn)制文件

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

注：這里使用的binlog的事件格式為：MIXED，從內(nèi)容來(lái)看MIXED格式中進(jìn)一步選擇了STATEMENT格式。

2）binlog日志清理

1> 設(shè)置binlog保存事件的過(guò)期刪除時(shí)間，單位為：天

set global expire_log_days=3;

2> 刪除主服務(wù)器的binlog文件

reset master;

執(zhí)行完這個(gè)命令我們?cè)偃タ碽inlog日志內(nèi)容，發(fā)現(xiàn)沒(méi)有了上面的插入、刪除操作等等的操作記錄：

3> 刪除slave服務(wù)器中的中繼日志

reset slave;

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

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

3、具體的delete全表數(shù)據(jù)SQL對(duì)應(yīng)的binlog內(nèi)容？

1> 如果binlog的事件格式類型是MIXED 或 STATEMENT，則記錄的是一條SQL語(yǔ)句：

DELETE from person

binlog日志內(nèi)容如下：

此時(shí)的事件類型為Query；

2> 如果binlog的事件格式類型是ROW，則記錄的是每一行數(shù)據(jù)修改的細(xì)節(jié)，比如：delete from person where id=1 and name=‘a(chǎn)’ 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日志內(nèi)容如下：

此時(shí)的事件類型為：delete_rows

4、附：變更binlog事件格式類型的方式（從MIXED到ROW）

第一步：先刪除mysql-bin相關(guān)的日志文件和日志索引文件；（mysql-bin為在配置文件中配置的log-bin屬性的值）

第二步：修改MYSQL配置文件中的binlog-format屬性為ROW

第三步：重啟MYSQL進(jìn)程 再執(zhí)行DELETE from person語(yǔ)句；然后再查看binlog日志文件內(nèi)容；

1> 執(zhí)行完DELETE from person語(yǔ)句后，binlog日志文件重新生成；

2> 查看 binlog日志文件內(nèi)容

• 進(jìn)入到MYSQL的安裝目錄的bin/目錄下，執(zhí)行下列命令，以文本行的形式查看binlog二進(jìn)制文件

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

內(nèi)容如下：