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 實現主從同步。