MySQL 二進制日志格式

2024-07-24 13:00:03

字體：大中小

來源：轉載

供稿：網友

MySQL 二進制日志格式

日志分類

MySQL存儲引擎層日志 innodb重做日志回滾日志MySQL服務層日志 二進制日志慢查日志通用日志

二進制日志介紹

記錄了所有對MySQL數據庫的修改事件, 包括DDL和DML操作. 其中binlog僅記錄成功執行的日志, 對于回滾或者Syntax Error而未執行的事件并不記錄.

啟用二進制日志

log_bin為OFF說為未啟用二進制日志, 我們嘗試通過以下指令打開

MariaDB [(none)]> set global log_bin = 'mariadb-bin';ERROR 1238 (HY000): Variable 'log_bin' is a read only variable

好吧, 其實仍然是要修改my.cnf配置文件, 在my.cnf或自定義配置中添加[mysqld]組, 設置log_bin屬性的值為mariadb-bin以開啟binlog.

[mysqld]log_bin = /var/log/mysql/mariadb-bin

以后我們查看目錄/var/log/mysql/的時候就會發現有如mariadb-bin.000001的一系列日志.待重啟MySQL后執行

現在也可以正常顯示binary的信息

NOTE: mysql> flush logs; 用于產生新的二進制日志, 方便調試.

二進制日志的格式

基于段的日志格式 - statement

這是MySQL 5.7之前默認的日志格式, 記錄CUD的SQL語句.

mysqlbinlog工具可直接用于查看日志

mysqlbinlog mysql-bin.000001優點:

記錄每個事件所執行的SQL語句, 故不需要記錄每一行的具體變化, 所以日志記錄量相對較少, 節約磁盤IO與網絡IO(如果只對一條記錄修改或者插入, ROW格式的日志量有可能少于STATEMENT格式).

缺點:

為了確保這些SQL語句能在從庫中正確地執行, 所以要記錄上下文信息, 以保證重放時的行為一致. 但如果使用UUID()這類非確定性函數, 可能會造成主從的數據不一致.

基于行的日志格式 - row

在MySQL 5.7后的默認格式, 每當進行CUD操作修改行記錄時都會將數據寫入binlog.

如果我們有一個修改了10k條數據的情況下, 基于段的日志格式僅會記錄該條SQL, 而基于行的日志則會有10k條記錄

查看日志mysqlbinlog -vv mysql-bin.000001

優點

使主從復制更加安全, 由于ROW格式記錄了每一行的更改, 當日志被從庫重放時僅需應用該條更改即可, 使得不確定性函數也能安全地使用. 更大程度上減少由于主從數據不一致而造成復制鏈路中斷的情況.基于行的復制比基于段的復制要高效

缺點

記錄的日志量較大

混合日志格式 - mixed

混合使用STATEMENT和ROW, 根據SQL語句由系統決定使用基于段還是基于行的日志格式, 如非確定性函數則會以ROW格式記錄, 其他的以STATEMENT記錄.

修改binlog格式

MariaDB [(none)]> set binlog_format = 'statement';Query OK, 0 rows affected (0.00 sec)MariaDB [(none)]> show variables like 'binlog_format';+---------------+-----------+| Variable_name | Value |+---------------+-----------+| binlog_format | STATEMENT |+---------------+-----------+1 row in set (0.00 sec)

ROW日志格式的相關優化

由于ROW格式記錄的日志量巨大, 在MySQL 5.6以后, 官方增加了binlog_row_image參數改善其記錄方式.

FULL為默認值, 意思是記錄一行紀錄里面的所有內容, 無論該列是否被修改.

MINIMAL僅記錄被修改的列, 這樣就可以大大減少記錄量.

NOBLOB與FULL類型, 但是如果沒有修改BLOB或TEXT類型的列, 就不會記錄該大數據類型的列.

實驗

假如有表結構如下

CREATE TABLE `t_test` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `title` varchar(32) NOT NULL DEFAULT '', `content` text, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

表中有記錄

id->1, title->'prontera', content->'hey guy'

FULL (全記錄)

我們修改id為1的記錄, 然后查看/var/log/mysql中的binlog

MariaDB [employees]> update t_test set title = 'solar' where id = 1;Query OK, 1 row affected (0.01 sec)Rows matched: 1 Changed: 1 Warnings: 0

可以看出, 我們僅僅set title, 但是binlog_row_image為FULL的時候會記錄所有的列

BINLOG 'HPyWWBMBAAAANwAAAM8BAAAAACEAAAAAAAEACWVtcGxveWVlcwAGdF90ZXN0AAMDD/wDYAACBA==HPyWWBgBAAAASQAAABgCAAAAACEAAAAAAAEAA///+AEAAAAIcHJvbnRlcmEHAGhleSBndXn4AQAAAAVzb2xhcgcAaGV5IGd1eQ=='/*!*/;### UPDATE `employees`.`t_test`### WHERE### @1=1 /* INT meta=0 nullable=0 is_null=0 */### @2='prontera' /* VARSTRING(96) meta=96 nullable=0 is_null=0 */### @3='hey guy' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */### SET### @1=1 /* INT meta=0 nullable=0 is_null=0 */### @2='solar' /* VARSTRING(96) meta=96 nullable=0 is_null=0 */### @3='hey guy' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */

MINIMAL (按需記錄)

同樣地, 將id為1的記錄的title更新為原先的’prontera’, 對比binlog的確是有效地減少了日志量

BINLOG 'rv2WWBMBAAAANwAAAM8BAAAAACEAAAAAAAEACWVtcGxveWVlcwAGdF90ZXN0AAMDD/wDYAACBA==rv2WWBgBAAAALQAAAPwBAAAAACEAAAAAAAEAAwEC/gEAAAD+CHByb250ZXJh'/*!*/;### UPDATE `employees`.`t_test`### WHERE### @1=1 /* INT meta=0 nullable=0 is_null=0 */### SET### @2='prontera' /* VARSTRING(96) meta=96 nullable=0 is_null=0 */

NOBLOB (除去BLOB&TEXT的全紀錄) 更新時不包含text類型的列

MariaDB [employees]> update t_test set title = 'juno' where id = 1;Query OK, 1 row affected (0.01 sec)Rows matched: 1 Changed: 1 Warnings: 0

類似于FULL模式, 但是binlog不會記錄沒有被更新的大數據類型(BLOB或TEXT類型)

BINLOG 'hP+WWBMBAAAANwAAAM8BAAAAACEAAAAAAAEACWVtcGxveWVlcwAGdF90ZXN0AAMDD/wDYAACBA==hP+WWBgBAAAAMwAAAAICAAAAACEAAAAAAAEAAwMD/AEAAAAFcGF5b278AQAAAARqdW5v'/*!*/;### UPDATE `employees`.`t_test`### WHERE### @1=1 /* INT meta=0 nullable=0 is_null=0 */### @2='payon' /* VARSTRING(96) meta=96 nullable=0 is_null=0 */### SET### @1=1 /* INT meta=0 nullable=0 is_null=0 */### @2='juno' /* VARSTRING(96) meta=96 nullable=0 is_null=0 */

更新時包含text列

MariaDB [employees]> update t_test set title = 'prontera', content = 'google' where id = 1;Query OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0

當有大數據類型的列被更新時, 該列才會被記錄, 一定程度上減少了日志量.

BINLOG 'PQCXWBMBAAAANwAAAM8BAAAAACEAAAAAAAEACWVtcGxveWVlcwAGdF90ZXN0AAMDD/wDYAACBA==PQCXWBgBAAAAPgAAAA0CAAAAACEAAAAAAAEAAwMH/AEAAAAEanVub/gBAAAACHByb250ZXJhBgBnb29nbGU='/*!*/;### UPDATE `employees`.`t_test`### WHERE### @1=1 /* INT meta=0 nullable=0 is_null=0 */### @2='juno' /* VARSTRING(96) meta=96 nullable=0 is_null=0 */### SET### @1=1 /* INT meta=0 nullable=0 is_null=0 */### @2='prontera' /* VARSTRING(96) meta=96 nullable=0 is_null=0 */### @3='google' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */

通過實驗, 在基于ROW格式情況下, 即使處于同一IDC, 也建議使用minimal模式以大量減少日志量.