一、確定問題

像這種聽起來就比較難的問題，我們首先要核實面試官想問的和我們理解的問題是同一個嗎？ 避免回答錯誤。

這道題可以從兩個方面去聊：

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

因為博主在項目經(jīng)驗中提到了很多binlog+canal的操作，深入binlog問也算是對博主的尊重。不過很可惜，博主當時并沒答出來，借機在此總結(jié)一下。

二、答題思路

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

1）binlog是什么？

MySQL 的二進制日志 binlog 可以說是 MySQL 最重要的日志；

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

2）binlog的事件格式類型？

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

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

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

   2> 優(yōu)點：

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

   3> 缺點：

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

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

   1> 5.1.5版本的MySQL才開始支持 row level 的復制，它不記錄sql語句上下文的相關信息，僅保存哪條記錄被修改；

   2> 優(yōu)點：

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

   3> 缺點：

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

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

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

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

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

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

3）binlog事件的類型？

常見的幾個如下：

4）binlog的存儲方式？

1. binlog文件以一個值為0Xfe62696e的魔數(shù)開頭，這個魔數(shù)對應0xfe ‘b’‘i’‘n’；
2. binlog由一系列的binlog event構(gòu)成。每個binlog event包含header和data兩部分；
   • header部分提供的是event的公共的類型信息，包括event的類型、event的創(chuàng)建時間、event的長度、下一個event的位置等等；
   • data部分提供的是針對該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相關的一些命令？

1）開啟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> 查看當前服務器使用的binlog文件及其大小；

show binary logs;

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

show binlog events;

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

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

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

2）binlog日志清理

1> 設置binlog保存事件的過期刪除時間，單位為：天

set global expire_log_days=3;

2> 刪除主服務器的binlog文件

reset master;

執(zhí)行完這個命令我們再去看binlog日志內(nèi)容，發(fā)現(xiàn)沒有了上面的插入、刪除操作等等的操作記錄：

3> 刪除slave服務器中的中繼日志

reset slave;

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

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

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

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

DELETE from person

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

此時的事件類型為Query；

2> 如果binlog的事件格式類型是ROW，則記錄的是每一行數(shù)據(jù)修改的細節(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)容如下：

此時的事件類型為：delete_rows

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

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

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

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

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

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

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

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

內(nèi)容如下：