MySQL的binlog主要用于資料恢複及主從複制,binlog 通過binlog events記錄了對資料庫的修改。
binlog有3種記錄格式: statement , row 和mixed,通過參數binlog_format配置。
binlog_format=STATEMENT,直接記錄原始語句,存在nondeterministic的問題(如AUTO_INCREMENT,UUID等),是以容易造成主從資料不一緻。
binlog_format=ROW ,推薦的配置方式,将對資料修改的SQL語句轉換成對應的行改變
binlog_format=MIXED ,預設情況下為STATEMENT,遇到特殊的SQL語句時轉換為ROW格式
推薦的安全配置為ROW格式,ROW格式下原始的SQL語句不會直接記錄到binlog中,而是通過等價的轉化記錄最終對行的修改。這樣的好處一方面可以保證主從資料的一緻性,另外當遇到執行很慢的複雜SQL但最終執行結果隻改變了很少的行資料時可以更高效。比如執行了一個很長時間的DML,最終沒有修改資料或者隻修改了很少的資料,那binlog中隻記錄對改變行的修改,在從庫端回放日志會更高效。
另外,ROW格式下有些類型的語句也是記錄為STATEMENT的,例如DDL語句。
通過mysqlbinlog分析ROW格式的binlog時, 通常會用到--base64-output=DECODE-ROWS 以及 --verbose 參數。
mysql的--base64-output選項有3個可選值:AUTO,NEVER,DECODE-ROWS。
AUTO: 當不顯示加--base64-output選項時,預設為AUTO方式,原始的記錄binlog events的方式。如果要通過binlog恢複資料(mysqlbinlog log_file | mysql -h server_name),必須使用AUTO方式
NEVER: 不顯示binlog statements,遇到ROW格式的binlog直接報錯
DECODE-ROWS: 壓縮顯示row格式events
--verbose: Reconstruct row events and display them as commented SQL statements, with table partition
information where applicable. If this option is given twice (by passing in either "-vv" or "--verbose --
verbose"), the output includes comments to indicate column data types and some metadata, and
informational log events such as row query log events if the binlog_rows_query_log_events
system variable is set to TRUE.
也就是通過僞代碼的方式重構出行資料改變的等價的SQL語句
下面通過實驗驗證一下這兩個參數的用法:
MySQL Server version: 5.7.31-log MySQL Community Server (GPL)
mysql> create table test(id int,name varchar(20));
Query OK, 0 rows affected (0.02 sec)
mysql>
mysql> flush binary logs;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into test values(88,'Hunter');
Query OK, 1 row affected (0.01 sec)
在insert一行資料之前,flush binlog,這樣最後的binlog隻有這一個insert操作。
1.不加任何參數:
mysqlbinlog /usr/local/my3306/binlog.000031
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLicmbw5SYwImZ0AzNmZjYlRGZ2UGOiVTM0UGM4ETNxETN2IDN38CX5d2bs92Yl1iclB3bsVmdlR2LcNWaw9CXt92Yu4GZjlGbh5yYjV3Lc9CX6MHc0RHaiojIsJye.png)
2.加參數 --base64-output=DECODE-ROWS ,可以看到binlog event直接被壓縮看不到了
mysqlbinlog /usr/local/my3306/binlog.000031 --base64-output=DECODE-ROWS
--3.隻加--verbose參數
mysqlbinlog /usr/local/my3306/binlog.000031 --verbose 或
mysqlbinlog /usr/local/my3306/binlog.000031 -v
可以看到既顯示了原來的binlog events,也生成了轉換之後的僞SQL語句
4.同時加 --base64-output=DECODE-ROWS 和 --verbose,實際上通過以上可以推測出,這種情況下不會顯示原始的binlog events, 隻會顯示生成的sql語句:
mysqlbinlog /usr/local/my3306/binlog.000031 --base64-output=DECODE-ROWS --verbose
或
mysqlbinlog /usr/local/my3306/binlog.000031 --base64-output=DECODE-ROWS -v
5.加 --base64-output=DECODE-ROWS 和兩個 --verbose
mysqlbinlog /usr/local/my3306/binlog.000031 --base64-output=DECODE-ROWS --verbose --verbose 或
mysqlbinlog /usr/local/my3306/binlog.000031 --base64-output=DECODE-ROWS -vv
會顯示insert語句對應表的中繼資料資訊(字段的資料類型等)
以上也就是為什麼我們經常會通過一下的語句來分析ROW格式的binlog的原因:
另外,在ROW格式下,因為記錄的是最終的行資料改變,而非原始的SQL statement, 是以一個SQL語句如果操作了N行資料,binlog中會轉換為N個binlog events,如下:
mysql> select * from test;
+------+--------+
| id | name |
| 88 | Hunter |
| 99 | Hunter |
| 77 | Hunter |
3 rows in set (0.00 sec)
mysql> delete from test;
Query OK, 3 rows affected (0.01 sec)
一個delete語句删除了3行資料,那在binlog中會記錄3個binlog events, 翻譯之後會有3個delete語句:
這也就是在主從複制環境下,master上一個語句操作大量資料,在slave上replay binlog時會有大量的events, 如果操作的表上沒有索引的情況下,從庫端每個行的操作都會變成全表掃描,容易造成主從複制延時,這種情況會在之後讨論。