帶你了解 MySQL Binlog 不為人知的秘密
MySQL 的 Binlog 日誌是一種二進位格式的日誌，Binlog 記錄所有的 DDL 和 DML 語句(除了數據查詢語句SELECT、SHOW等)，以 Event 的形式記錄，同時記錄語句執行時間。
Binlog 的主要作用有兩個：
-
數據恢復
因為 Binlog 詳細記錄了所有修改數據的 SQL，當某一時刻的數據誤操作而導致出問題，或者資料庫宕機數據丟失，那麼可以根據 Binlog 來回放歷史數據。
-
主從複製
想要做多機備份的業務，可以去監聽當前寫庫的 Binlog 日誌，同步寫庫的所有更改。
Binlog 包括兩類文件：
- 二進位日誌索引文件(.index)：記錄所有的二進位文件。
- 二進位日誌文件(.00000*)：記錄所有 DDL 和 DML 語句事件。
Binlog 日誌功能默認是開啟的，線上情況下 Binlog 日誌的增長速度是很快的，在 MySQL 的配置文件 my.cnf 中提供一些參數來對 Binlog 進行設置。
設置此參數表示啟用binlog功能，並制定二進位日誌的存儲目錄
log-bin=/home/mysql/binlog/
#mysql-bin.*日誌文件最大位元組（單位：位元組）
#設置最大100MB
max_binlog_size=104857600
#設置了只保留7天BINLOG（單位：天）
expire_logs_days = 7
#binlog日誌只記錄指定庫的更新
#binlog-do-db=db_name
#binlog日誌不記錄指定庫的更新
#binlog-ignore-db=db_name
#寫緩衝多少次，刷一次磁碟，默認0
sync_binlog=0
需要注意的是：
max_binlog_size ：Binlog 最大和默認值是 1G，該設置並不能嚴格控制 Binlog 的大小，尤其是 Binlog 比較靠近最大值而又遇到一個比較大事務時，為了保證事務的完整性不可能做切換日誌的動作，只能將該事務的所有 SQL 都記錄進當前日誌直到事務結束。所以真實文件有時候會大於 max_binlog_size 設定值。
expire_logs_days ：Binlog 過期刪除不是服務定時執行，是需要藉助事件觸發才執行，事件包括：
- 伺服器重啟
- 伺服器被更新
- 日誌達到了最大日誌長度
max_binlog_size - 日誌被刷新
二進位日誌由配置文件的 log-bin 選項負責啟用，MySQL 伺服器將在數據根目錄創建兩個新文件mysql-bin.000001 和 mysql-bin.index，若配置選項沒有給出文件名，MySQL 將使用主機名稱命名這兩個文件，其中 .index 文件包含一份全體日誌文件的清單。
sync_binlog：這個參數決定了 Binlog 日誌的更新頻率。默認 0 ，表示該操作由作業系統根據自身負載自行決定多久寫一次磁碟。
sync_binlog = 1 表示每一條事務提交都會立刻寫盤。sync_binlog=n 表示 n 個事務提交才會寫盤。
根據 MySQL 文檔，寫 Binlog 的時機是：SQL transaction 執行完，但任何相關的 Locks 還未釋放或事務還未最終 commit 前。這樣保證了 Binlog 記錄的操作時序與資料庫實際的數據變更順序一致。
檢查 Binlog 文件是否已開啟：
mysql> show variables like '%log_bin%';
+---------------------------------+------------------------------------+
| Variable_name | Value |
+---------------------------------+------------------------------------+
| log_bin | ON |
| log_bin_basename | /usr/local/mysql/data/binlog |
| log_bin_index | /usr/local/mysql/data/binlog.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+------------------------------------+
6 rows in set (0.00 sec)
MySQL 會把用戶對所有資料庫的內容和結構的修改情況記入 mysql-bin.n 文件，而不會記錄 SELECT 和沒有實際更新的 UPDATE 語句。
如果你不知道現在有哪些 Binlog 文件，可以使用如下命令：
show binary logs; #查看binlog列表
show master status; #查看最新的binlog
mysql> show binary logs;
+------------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000001 | 179 | No |
| mysql-bin.000002 | 156 | No |
+------------------+-----------+-----------+
2 rows in set (0.00 sec)
Binlog 文件是二進位文件，強行打開看到的必然是亂碼，MySQL 提供了命令行的方式來展示 Binlog 日誌：
mysqlbinlog mysql-bin.000002 | more
mysqlbinlog 命令即可查看。
看起來凌亂其實也有跡可循。Binlog 通過事件的方式來管理日誌資訊，可以通過 show binlog events in 的語法來查看當前 Binlog 文件對應的詳細事件資訊。
mysql> show binlog events in 'mysql-bin.000001';
+------------------+-----+----------------+-----------+-------------+-----------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+-----------------------------------+
| mysql-bin.000001 | 4 | Format_desc | 1 | 125 | Server ver: 8.0.21, Binlog ver: 4 |
| mysql-bin.000001 | 125 | Previous_gtids | 1 | 156 | |
| mysql-bin.000001 | 156 | Stop | 1 | 179 | |
+------------------+-----+----------------+-----------+-------------+-----------------------------------+
3 rows in set (0.01 sec)
這是一份沒有任何寫入數據的 Binlog 日誌文件。
Binlog 的版本是V4，可以看到日誌的結束時間為 Stop。出現 Stop event 有兩種情況：
- 是 master shut down 的時候會在 Binlog 文件結尾出現
- 是備機在關閉的時候會寫入 relay log 結尾，或者執行 RESET SLAVE 命令執行
本文出現的原因是我有手動停止過 MySQL 服務。
一般來說一份正常的 Binlog 日誌文件會以 Rotate event 結束。當 Binlog 文件超過指定大小，Rotate event 會寫在文件最後，指向下一個 Binlog 文件。
我們來看看有過數據操作的 Binlog 日誌文件是什麼樣子的。
mysql> show binlog events in 'mysql-bin.000002';
+------------------+-----+----------------+-----------+-------------+-----------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+-----------------------------------+
| mysql-bin.000002 | 4 | Format_desc | 1 | 125 | Server ver: 8.0.21, Binlog ver: 4 |
| mysql-bin.000002 | 125 | Previous_gtids | 1 | 156 | |
+------------------+-----+----------------+-----------+-------------+-----------------------------------+
2 rows in set (0.00 sec)
上面是沒有任何數據操作且沒有被截斷的 Binlog。接下來我們插入一條數據，再看看 Binlog 事件。
mysql> show binlog events in 'mysql-bin.000002';
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------------+
| mysql-bin.000002 | 4 | Format_desc | 1 | 125 | Server ver: 8.0.21, Binlog ver: 4 |
| mysql-bin.000002 | 125 | Previous_gtids | 1 | 156 | |
| mysql-bin.000002 | 156 | Anonymous_Gtid | 1 | 235 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000002 | 235 | Query | 1 | 323 | BEGIN |
| mysql-bin.000002 | 323 | Intvar | 1 | 355 | INSERT_ID=13 |
| mysql-bin.000002 | 355 | Query | 1 | 494 | use `test_db`; INSERT INTO `test_db`.`test_db`(`name`) VALUES ('xdfdf') |
| mysql-bin.000002 | 494 | Xid | 1 | 525 | COMMIT /* xid=192 */ |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------------+
7 rows in set (0.00 sec)
這是加入一條數據之後的 Binlog 事件。
我們對 event 查詢的數據行關鍵欄位來解釋一下：
-
Pos：當前事件的開始位置，每個事件都佔用固定的位元組大小，結束位置(End_log_position)減去Pos，就是這個事件佔用的位元組數。
上面的日誌中我們能看到，第一個事件位置並不是從 0 開始，而是從 4。MySQL 通過文件中的前 4 個位元組，來判斷這是不是一個 Binlog 文件。這種方式很常見，很多格式的文件，如 pdf、doc、jpg等，都會通常前幾個特定字元判斷是否是合法文件。
-
Event_type：表示事件的類型
-
Server_id：表示產生這個事件的 MySQL server_id，通過設置 my.cnf 中的 server-id 選項進行配置
-
End_log_position：下一個事件的開始位置
-
Info：包含事件的具體資訊
Binlog 日誌格式
針對不同的使用場景，Binlog 也提供了可訂製化的服務，提供了三種模式來提供不同詳細程度的日誌內容。
- Statement 模式：基於 SQL 語句的複製(statement-based replication-SBR)
- Row 模式：基於行的複製(row-based replication-RBR)
- Mixed 模式：混合模式複製(mixed-based replication-MBR)
Statement 模式
保存每一條修改數據的SQL。
該模式只保存一條普通的SQL語句，不涉及到執行的上下文資訊。
因為每台 MySQL 資料庫的本地環境可能不一樣，那麼對於依賴到本地環境的函數或者上下文處理的邏輯 SQL 去處理的時候可能同樣的語句在不同的機器上執行出來的效果不一致。
比如像 sleep()函數，last_insert_id()函數，等等，這些都跟特定時間的本地環境有關。
Row 模式
MySQL V5.1.5 版本開始支援Row模式的 Binlog，它與 Statement 模式的區別在於它不保存具體的 SQL 語句，而是記錄具體被修改的資訊。
比如一條 update 語句更新10條數據，如果是 Statement 模式那就保存一條 SQL 就夠，但是 Row 模式會保存每一行分別更新了什麼，有10條數據。
Row 模式的優缺點就很明顯了。保存每一個更改的詳細資訊必然會帶來存儲空間的快速膨脹，換來的是事件操作的詳細記錄。所以要求越高代價越高。
Mixed 模式
Mixed 模式即以上兩種模式的綜合體。既然上面兩種模式分別走了極簡和一絲不苟的極端，那是否可以區分使用場景的情況下將這兩種模式綜合起來呢？
在 Mixed 模式中，一般的更新語句使用 Statement 模式來保存 Binlog，但是遇到一些函數操作，可能會影響數據準確性的操作則使用 Row 模式來保存。這種方式需要根據每一條具體的 SQL 語句來區分選擇哪種模式。
MySQL 從 V5.1.8 開始提供 Mixed 模式，V5.7.7 之前的版本默認是Statement 模式，之後默認使用Row模式， 但是在 8.0 以上版本已經默認使用 Mixed 模式了。
查詢當前 Binlog 日誌使用格式：
mysql> show global variables like '%binlog_format%';
+---------------------------------+---------+
| Variable_name | Value |
+---------------------------------+---------+
| binlog_format | MIXED |
| default_week_format | 0 |
| information_schema_stats_expiry | 86400 |
| innodb_default_row_format | dynamic |
| require_row_format | OFF |
+---------------------------------+---------+
5 rows in set (0.01 sec)
如何通過 mysqlbinlog 命令手動恢複數據
上面說過每一條 event 都有位點資訊，如果我們當前的 MySQL 庫被無操作或者誤刪除了，那麼該如何通過 Binlog 來恢復到刪除之前的數據狀態呢？
首先發現誤操作之後，先停止 MySQL 服務，防止繼續更新。
接著通過 mysqlbinlog命令對二進位文件進行分析，查看誤操作之前的位點資訊在哪裡。
接下來肯定就是恢複數據，當前資料庫的數據已經是錯的，那麼就從開始位置到誤操作之前位點的數據肯定的都是正確的；如果誤操作之後也有正常的數據進來，這一段時間的位點數據也要備份。
比如說：
誤操作的位點開始值為 501，誤操作結束的位置為705，之後到800的位點都是正確數據。
那麼從 0 – 500 ，706 – 800 都是有效數據，接著我們就可以進行數據恢復了。
先將資料庫備份並清空。
接著使用 mysqlbinlog 來恢複數據：
0 – 500 的數據：
mysqlbinlog --start-position=0 --stop-position=500 bin-log.000003 > /root/back.sql;
上面命令的作用就是將 0 -500 位點的數據恢復到自定義的 SQL 文件中。同理 706 – 800 的數據也是一樣操作。之後我們執行這兩個 SQL 文件就行了。
Binlog 事件類型
上面我們說到了 Binlog 日誌中的事件，不同的操作會對應著不同的事件類型，且不同的 Binlog 日誌模式同一個操作的事件類型也不同，下面我們一起看看常見的事件類型。
首先我們看看源碼中的事件類型定義：
源碼位置：/libbinlogevents/include/binlog_event.h
enum Log_event_type
{
/**
Every time you update this enum (when you add a type), you have to
fix Format_description_event::Format_description_event().
*/
UNKNOWN_EVENT= 0,
START_EVENT_V3= 1,
QUERY_EVENT= 2,
STOP_EVENT= 3,
ROTATE_EVENT= 4,
INTVAR_EVENT= 5,
LOAD_EVENT= 6,
SLAVE_EVENT= 7,
CREATE_FILE_EVENT= 8,
APPEND_BLOCK_EVENT= 9,
EXEC_LOAD_EVENT= 10,
DELETE_FILE_EVENT= 11,
/**
NEW_LOAD_EVENT is like LOAD_EVENT except that it has a longer
sql_ex, allowing multibyte TERMINATED BY etc; both types share the
same class (Load_event)
*/
NEW_LOAD_EVENT= 12,
RAND_EVENT= 13,
USER_VAR_EVENT= 14,
FORMAT_DESCRIPTION_EVENT= 15,
XID_EVENT= 16,
BEGIN_LOAD_QUERY_EVENT= 17,
EXECUTE_LOAD_QUERY_EVENT= 18,
TABLE_MAP_EVENT = 19,
/**
The PRE_GA event numbers were used for 5.1.0 to 5.1.15 and are
therefore obsolete.
*/
PRE_GA_WRITE_ROWS_EVENT = 20,
PRE_GA_UPDATE_ROWS_EVENT = 21,
PRE_GA_DELETE_ROWS_EVENT = 22,
/**
The V1 event numbers are used from 5.1.16 until mysql-trunk-xx
*/
WRITE_ROWS_EVENT_V1 = 23,
UPDATE_ROWS_EVENT_V1 = 24,
DELETE_ROWS_EVENT_V1 = 25,
/**
Something out of the ordinary happened on the master
*/
INCIDENT_EVENT= 26,
/**
Heartbeat event to be send by master at its idle time
to ensure master's online status to slave
*/
HEARTBEAT_LOG_EVENT= 27,
/**
In some situations, it is necessary to send over ignorable
data to the slave: data that a slave can handle in case there
is code for handling it, but which can be ignored if it is not
recognized.
*/
IGNORABLE_LOG_EVENT= 28,
ROWS_QUERY_LOG_EVENT= 29,
/** Version 2 of the Row events */
WRITE_ROWS_EVENT = 30,
UPDATE_ROWS_EVENT = 31,
DELETE_ROWS_EVENT = 32,
GTID_LOG_EVENT= 33,
ANONYMOUS_GTID_LOG_EVENT= 34,
PREVIOUS_GTIDS_LOG_EVENT= 35,
TRANSACTION_CONTEXT_EVENT= 36,
VIEW_CHANGE_EVENT= 37,
/* Prepared XA transaction terminal event similar to Xid */
XA_PREPARE_LOG_EVENT= 38,
/**
Add new events here - right above this comment!
Existing events (except ENUM_END_EVENT) should never change their numbers
*/
ENUM_END_EVENT /* end marker */
};
這麼多的事件類型我們就不一一介紹，挑出來一些常用的來看看。
FORMAT_DESCRIPTION_EVENT
FORMAT_DESCRIPTION_EVENT 是 Binlog V4 中為了取代之前版本中的 START_EVENT_V3 事件而引入的。它是 Binlog 文件中的第一個事件，而且，該事件只會在 Binlog 中出現一次。MySQL 根據 FORMAT_DESCRIPTION_EVENT 的定義來解析其它事件。
它通常指定了 MySQL 的版本，Binlog 的版本，該 Binlog 文件的創建時間。
QUERY_EVENT
QUERY_EVENT 類型的事件通常在以下幾種情況下使用：
- 事務開始時，執行的 BEGIN 操作
- STATEMENT 格式中的 DML 操作
- ROW 格式中的 DDL 操作
比如上文我們插入一條數據之後的 Binlog 日誌：
mysql> show binlog events in 'mysql-bin.000002';
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------------+
| mysql-bin.000002 | 4 | Format_desc | 1 | 125 | Server ver: 8.0.21, Binlog ver: 4 |
| mysql-bin.000002 | 125 | Previous_gtids | 1 | 156 | |
| mysql-bin.000002 | 156 | Anonymous_Gtid | 1 | 235 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000002 | 235 | Query | 1 | 323 | BEGIN |
| mysql-bin.000002 | 323 | Intvar | 1 | 355 | INSERT_ID=13 |
| mysql-bin.000002 | 355 | Query | 1 | 494 | use `test_db`; INSERT INTO `test_db`.`test_db`(`name`) VALUES ('xdfdf') |
| mysql-bin.000002 | 494 | Xid | 1 | 525 | COMMIT /* xid=192 */ |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------------+
7 rows in set (0.00 sec)
XID_EVENT
在事務提交時，不管是 STATEMENT 還 是ROW 格式的 Binlog，都會在末尾添加一個 XID_EVENT 事件代表事務的結束。該事件記錄了該事務的 ID，在 MySQL 進行崩潰恢復時，根據事務在 Binlog 中的提交情況來決定是否提交存儲引擎中狀態為 prepared 的事務。
ROWS_EVENT
對於 ROW 格式的 Binlog，所有的 DML 語句都是記錄在 ROWS_EVENT 中。
ROWS_EVENT分為三種：
-
WRITE_ROWS_EVENT
-
UPDATE_ROWS_EVENT
-
DELETE_ROWS_EVENT
分別對應 insert，update 和 delete 操作。
對於 insert 操作，WRITE_ROWS_EVENT 包含了要插入的數據。
對於 update 操作，UPDATE_ROWS_EVENT 不僅包含了修改後的數據，還包含了修改前的值。
對於 delete 操作，僅僅需要指定刪除的主鍵（在沒有主鍵的情況下，會給定所有列）。
對比 QUERY_EVENT 事件，是以文本形式記錄 DML 操作的。而對於 ROWS_EVENT 事件，並不是文本形式，所以在通過 mysqlbinlog 查看基於 ROW 格式的 Binlog 時，需要指定 -vv --base64-output=decode-rows。
我們來測試一下，首先將日誌格式改為 Rows：
mysql> set binlog_format=row;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)
然後刷新一下日誌文件，重新開始一個 Binlog 日誌。我們插入一條數據之後看一下日誌：
mysql> show binlog events in 'binlog.000008';
+---------------+-----+----------------+-----------+-------------+--------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------+-----+----------------+-----------+-------------+--------------------------------------+
| binlog.000008 | 4 | Format_desc | 1 | 125 | Server ver: 8.0.21, Binlog ver: 4 |
| binlog.000008 | 125 | Previous_gtids | 1 | 156 | |
| binlog.000008 | 156 | Anonymous_Gtid | 1 | 235 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000008 | 235 | Query | 1 | 313 | BEGIN |
| binlog.000008 | 313 | Table_map | 1 | 377 | table_id: 85 (test_db.test_db) |
| binlog.000008 | 377 | Write_rows | 1 | 423 | table_id: 85 flags: STMT_END_F |
| binlog.000008 | 423 | Xid | 1 | 454 | COMMIT /* xid=44 */ |
+---------------+-----+----------------+-----------+-------------+--------------------------------------+
7 rows in set (0.01 sec)
總結
這一篇我們詳解了解 Binlog 日誌是什麼，裡面都有什麼內容，Binlog 事件，如何通過 Binlog 來恢複數據。Binlog 目前最重要的應用就是用於主從同步，那麼下一篇我們講來講講如何通過 Binlog 實現主從同步。
