吃透3大Binlog模式，MySQL資料零丢失

用錯 Binlog 模式，可能導緻 MySQL 的資料不一緻性、性能下降、存儲空間浪費和資料恢複困難等問題。

在 MySQL 中，不管使用什麼存儲引擎，隻要表資料更新，就會産生 Binlog（歸檔日志）。

而 MySQL 資料庫的資料備份、主備、主主、主從等實作，也離不開 Binlog（歸檔日志）。

針對不同的應用場景，Binlog 推出了三種工作模式 Statement、Row、Mixed，以滿足對資料庫的需求。

大家好，我是寶妹兒。

今天我們就來深入 Binlog 的三種模式，包括它的原理、優缺點、适用場景、配置管理、切換方式等。

這也是 MySQL 的重要知識點及高頻面試點，寶妹兒順便将這個題目以及答案，整理到2023版《MySQL 大廠高頻面試題大全》PDF了，友善系統學習、面試通關。

寶妹兒精編的2023版《MySQL 大廠高頻面試題大全》，已收錄100+道真題，近30000字，長期疊代、持續更新。吃透它，應付MySQL面試沒問題。

公衆号Java面試題寶，自取。

1. Binlog 概述

在 MySQL 中，Binlog 是一種二進制形式記錄的日志檔案，用于記錄資料庫的修改操作，包括插入、更新和删除等。

Binlog 的兩個主要作用：

• 資料恢複：如果 MySQL 意外停止，可以通過該日志進行恢複、備份；
• 資料複制：master 把它的二進制日志傳遞給 slaves ，進而實作 master-slove 資料的一緻性。

2. Binlog 的三種工作模式

Binlog 的三種工作模式分别是：Statement、Row、Mixed。

2.1 Statement 模式

Statement 模式的概念

Statement 是基于語句的複制模式。

Statement 模式将資料庫中執行的修改操作記錄為 SQL 語句，再從資料庫上執行相同的 SQL 語句來實作資料同步。

Statement 模式的優缺點

Statement 模式的優點是簡單明了，易于了解和實作。

但是，Statement 模式在執行涉及非确定性函數、觸發器和存儲過程等操作時，可能會導緻不一緻的結果。

Statement 模式的缺點：

1）不支援 RU、RC 隔離級别；

2）binglog 日志檔案中，上一個事物的結束點是下一個事物的開始點；

3）DML、DDL 語句都會明文顯示；

4）對一些系統函數不能準确複制或者不能複制；

5）主庫執行 delete from t1 where c1=xxx limit 1，statement 模式下，從庫也會這麼執行，可能導緻删除的不是同一行資料；

6）主庫有 id=1 和 id=10 兩行資料，從庫有 id=1,2,3,10 這四行資料，主庫執行 delete from t1 where id<10 指令，從庫删除過多資料。

Statement 模式的應用場景

Statement 模式适用于大多數情況下的資料庫複制需求。

例如：

1）一次更新大量資料，如二十萬資料。反之，在複制時，從庫可能會追得太慢，然後導緻延時；

2）使用 pt-table-checksum 工具時。

示例一：

update這個事物的開始是insert這個事物結束的點at1581；
update結束的點是commit之後的點at1842;
[root@Darren2 logs]# MySQLBinlog --start-position=1581 --stop-position=1842 MySQL-bin.000022;
......
BEGIN
/*!*/;
# at 1729
#170408 14:40:49 server id 330622  end_log_pos 1841 CRC32 0xb443cf1e    Query   thread_id=55    exec_time=0     error_code=0
use `testdb`/*!*/;
SET TIMESTAMP=1491633649/*!*/;
update t10 set c2='bbb' where c1=1
/*!*/;
# at 1841
#170408 14:40:49 server id 330622  end_log_pos 1872 CRC32 0xd06c40f5    Xid = 1756
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by MySQLBinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;           

示例二：

當檢視commit之前的position點時，會看到rollback狀态，說明這個截取的事物不完整：
[root@Darren2 logs]# MySQLBinlog --start-position=1581 --stop-position=1841 MySQL-bin.000022;
BEGIN
/*!*/;
# at 1729
#170408 14:40:49 server id 330622  end_log_pos 1841 CRC32 0xb443cf1e    Query   thread_id=55    exec_time=0     error_code=0
use `testdb`/*!*/;
SET TIMESTAMP=1491633649/*!*/;
update t10 set c2='bbb' where c1=1
/*!*/;
ROLLBACK /* added by MySQLBinlog */ /*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by MySQLBinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;           

2.2 Row 模式

Row 模式的概念

MySQL 5.7 預設的日志模式為 Row。

Row 模式是基于行的複制模式，它将資料庫中實際修改的行記錄寫入 Binlog ，從資料庫通過解析 Binlog 來逐行執行相應的修改操作。

相對 statement ，Row 模式更加精确、安全，能夠確定資料的一緻性。

Row 模式的優缺點

Row 模式的優點是能夠準确複制修改的行記錄，避免了語句複制模式下的不确定性問題。

Row 模式的缺點：

如果 Binlog 檔案較大，傳輸成本就會很高，在某些情況下，可能會導緻性能下降。

1）在表有主鍵的情況下複制更加快；

2）系統的特殊函數也能複制；

3）更少的鎖，隻有行鎖；

4）Binlog 檔案比較大，假設單語句更新 20 萬行資料，可能要半小時，也有可能把主庫跑挂；

5）MySQL 5.6 之前的版本，無法從 binog 看見使用者執行的 SQL 語句；

6）DDL 語句明文顯示，DML 語句加密顯示；

7）DML 經過 base64 加密，需要使用參數 --base64-output=decode-rows --verbose；

8）update 修改的語句可以看到曆史舊資料。

示例：

開啟Binlog_rows_query_log_events參數,會顯示執行的SQL語句，這個參數預設關閉，不顯示執行的SQL
root@localhost [testdb]>set Binlog_rows_query_log_events=on;

[root@Darren2 logs]# MySQLBinlog -vv --base64-output=decode-rows MySQL-bin.000024
......
create table t10(c1 int,c2 varchar(50))

# insert into t10 values(1,now())
### INSERT INTO `testdb`.`t10`
### SET
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
###   @2='2017-04-08 15:26:09' /* VARSTRING(150) meta=150 nullable=1 is_null=0 */
# insert into t10 values(2,now())

### INSERT INTO `testdb`.`t10`
### SET
###   @1=2 /* INT meta=0 nullable=1 is_null=0 */
###   @2='2017-04-08 15:26:09' /* VARSTRING(150) meta=150 nullable=1 is_null=0 */
# at 1033

# insert into t10 values(3,sysdate())

### INSERT INTO `testdb`.`t10`
### SET
###   @1=3 /* INT meta=0 nullable=1 is_null=0 */
###   @2='2017-04-08 15:26:09' /* VARSTRING(150) meta=150 nullable=1 is_null=0 */
# insert into t10 values(4,uuid())
### INSERT INTO `testdb`.`t10`
### SET
###   @1=4 /* INT meta=0 nullable=1 is_null=0 */
###   @2='a2b570b8-1c2c-11e7-bc58-000c29c1b8a9' /* VARSTRING(150) meta=150 nullable=1 is_null=0 */
# update t10 set c2='bbb' where c1=1
### UPDATE `testdb`.`t10`
### WHERE
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
###   @2='2017-04-08 15:26:09' /* VARSTRING(150) meta=150 nullable=1 is_null=0 */
### SET
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
###   @2='bbb' /* VARSTRING(150) meta=150 nullable=1 is_null=0 */           

Row 模式的應用場景

Row 模式适用于對資料一緻性要求較高的場景，特别是涉及一些複雜的資料庫操作和業務邏輯。

例如，涉及觸發器、存儲過程和函數等的資料庫操作。

使用Row 模式時需注意，Row 模式可能導緻 Binlog 檔案較大，需要合理設定 Binlog 檔案大小和保留時間。

3.3 Mixed 模式

Mixed 模式的概念

Mixed 模式（混合模式）是将語句複制模式和行複制模式結合起來使用。

大多數的修改操作，通常使用 Statement 模式記錄對應的 SQL 語句。

一些特殊的操作，涉及非确定性函數和存儲過程等，則使用 Row 模式記錄修改的行記錄。

Mixed 模式的優缺點

Mixed 模式綜合了語句複制模式和行複制模式的優點，能夠在大多數情況下高效地記錄修改操作，并在需要時使用行複制模式確定資料的準确性。

但是 Mixed 模式對一些特殊操作的處理可能會很複雜，特别需要注意下配置和管理。

總結：

1）innodb 引擎，如果隔離級别是 RU、RC，則 Mixed 模式會轉成 Row 模式存儲。

示例：

set  tx_isolation='read-committed';
set  Binlog_format='mixed';
flush logs;
create table t10(c1 int,c2 varchar(50));
insert into t10 values(1,now());
insert into t10 values(2,now());
insert into t10 values(3,sysdate());
insert into t10 values(4,uuid());
update t10 set c2='bbb' where c1=1;
[root@Darren2 logs]# MySQLBinlog -vv --base64-output=decode-rows MySQL-bin.000028
......
### UPDATE `testdb`.`t10`
### WHERE
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
###   @2='2017-04-08 18:34:08' /* VARSTRING(150) meta=150 nullable=1 is_null=0 */
### SET
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
###   @2='bbb' /* VARSTRING(150) meta=150 nullable=1 is_null=0 */
......           

2）在以下幾種情況下，Mixed 模式會自動将 Binlog 的模式 SBR 轉化成 RBR 模式：

• 當更新一個 NDB 表時；
• 當函數包含 uuid() 函數時；
• 2個及以上包含 auto_increment 字段的表被更新時；
• 視圖中必須要求使用 RBR 時。

示例：

set  tx_isolation='repeatable-read';
set  Binlog_format='mixed';
flush logs;
create table t10(c1 int,c2 varchar(50));
insert into t10 values(1,now());
insert into t10 values(2,now());
insert into t10 values(3,sysdate());
insert into t10 values(4,uuid());
update t10 set c2='bbb' where c1=1;
[root@Darren2 logs]# MySQLBinlog MySQL-bin.000029
......
update t10 set c2='bbb' where c1=1
......           

Mixed 模式的應用場景

Mixed 模式适用于大多數情況下的資料庫複制需求，尤其适合需要兼顧效率和準确性的場景。

在使用 Mixed 模式時，需要注意對特殊操作進行測試和驗證，確定資料的一緻性和正确性。

4. 如何檢視、配置 Binlog 模式

檢視 Binlog 模式

MySQL> show global variables like "%Binlog_format%";  
+---------------+-----------+  
| Variable_name | Value    |  
+---------------+-----------+  
| Binlog_format | STATEMENT |  
+---------------+-----------+           

配置 Binlog 模式

vim my.cnf，在 MySQLd 子產品中配置。

log-bin = /data/3306/MySQL-bin  
Binlog_format="STATEMENT"  
#Binlog_format="ROW"  
#Binlog_format="MIXED"           

不重新開機，使配置在 MySQL 中生效。

SET global Binlog_format='STATEMENT';           

4. Binlog 模式的切換方法

4.1 修改配置檔案

可以通過修改 MySQL 的配置檔案來切換 Binlog 模式。

編輯 MySQL 的配置檔案（通常是 my.cnf 或 my.ini ），找到 Binlog_format 參數，并将其設定為所需的模式。

Binlog_format = ROW           

然後重新啟動 MySQL 服務，使新的 Binlog 模式生效。

4.2 使用 SQL 語句切換

另一種切換 Binlog 模式的方法，是通過執行 SQL 語句來修改 Binlog_format 參數。

SET GLOBAL Binlog_format = 'ROW';           

這會立即将 Binlog 模式切換為指定的模式。

這種修改在 MySQL 服務重新開機後會失效，是以需要在重新開機前進行相應的配置修改。

4.3 動态切換

MySQL 5.6 版本及以上提供了動态切換 Binlog 模式的功能，可以在不重新開機 MySQL 服務的情況下進行切換。

SET SESSION Binlog_format = 'ROW';           

這将在目前會話中将 Binlog 模式切換為指定的模式

動态切換隻對目前會話有效，不會影響其他會話或 MySQL 服務重新開機後的配置。

總結

通過本文，我們學習并掌握了 Binlog 三種模式 Statement、Row、Mixed，包括它們的特點、優缺點、應用場景、配置、切換等。

選型參考思路：

• 使用 MySQL 特殊功能較少，例如存儲過程、觸發器、函數等，用 Statement 模式。
• 使用 MySQL 特殊功能較多，用 Mixed 模式。
• 使用 MySQL 特殊功能較多，同時希望資料最大化一緻，用 Row 模式。

在實際應用場景中，還是要結合具體情況來合理選擇。

如果覺得有用，請順手【點贊】支援下哦，這将是對寶妹兒的最大鼓勵，謝謝~

最後

本文收錄于寶妹兒精編的 2023版《MySQL 大廠高頻面試題大全》PDF。

搞定這100道題，足以應對MySQL面試。