MySQL 日誌之 binlog 格式 → 關於 MySQL 默認隔離級別的探討

開心一刻

產品還沒測試直接投入生產時,這尼瑪…

背景問題

  在講 binlog 之前,我們先來回顧下主流關係型資料庫的默認隔離級別,是默認隔離級別,不是事務有哪幾種隔離級別,別會錯題意了

  1、Oracle、SQL Server 的默認隔離級別是什麼,MySQL 的呢 ?

  2、為什麼 MySQL 的默認隔離級別是 RR ?

    這個問題其實不太嚴謹,我們知道 MySQL 5.5 才將 InnoDB 代替 MyISAM 成為 MySQL 默認的存儲引擎,而事務才有隔離級別一說,MyISAM 本就不支援事務,那麼這個問題在 MySQL 5.5 之前根本就不成立。

    嚴謹點來說,應該是:為什麼 MySQL 5.5 及之後版本的事務默認隔離級別是 RR,或者是:為什麼 InnoDB 的事務默認隔離級別是 RR

  對於問題1,我相信大家都能回答的上來,Oracle,SqlServer 的默認隔離級別是 讀已提交(Read Commited,簡稱 RC) ,而 MySQL 的默認隔離級別是 可重複讀(Repeatable Read,簡稱 RR)  

  但是對於問題2,相信有很多小夥伴就會支支吾吾了:呃…,這個…,昂昂昂昂昂,太久了我記憶都不太好了…

  調皮的小夥伴可能就開始岔開話題了:你講 binlog 就講 binlog 啦,扯什麼默認隔離級別,難道 MySQL 的默認隔離級別還與 binlog 有關 ?

  想知道呀? 那得加錢

  具體它倆是不是有關,樓主也不知道,我們一起往下看

binlog 格式

  binlog 全稱:binary log,即二進位日誌,有時候也稱歸檔日誌,記錄了對 MySQL 資料庫執行了更改的所有操作,包括表結構變更(CREATE、ALTER、DROP TABLE…)、表數據修改(INSERT、UPDATE、DELETE…),但不包括 SELECT 和 SHOW 這類操作,因為這類操作對數據本身並沒有修改;若更改操作並未導致資料庫變化,那麼該操作也會寫入 binlog,例如

create table tbl_t1(name varchar(32));
insert into tbl_t1 values('zhangsan');
update tbl_t1 set name = 'lisi' where name = '123';
show master status\G;
show binlog events in 'mysql-bin.000002'\G;

  此時的:update tbl_t1 set name = ‘lisi’ where name = ‘123’; 並未引起資料庫的變化,但還是被記錄到了 binlog 中

  binlog 的格式有三種:STATEMENT、ROW、MIXED,一開始只有 STATEMENT,後面慢慢衍生出了 ROW、MIXED

  MySQL 5.1.5 之前 binlog 的格式只有 STATEMENT,5.1.5 開始支援 ROW 格式的 binlog,從 5.1.8 版本開始,MySQL 開始支援 MIXED 格式的 binlog

  MySQL 5.7.7 之前,binlog 的默認格式都是 STATEMENT,在 5.7.7 及更高版本中,binlog_format 的默認值才是 ROW

  三種格式的 binlog 各長什麼樣,它們有什麼區別,各有什麼優劣,我們往下看

  STATEMENT

    從 MySQL 第一個版本,到目前最新的 8.0.x,STATEMENT 一直堅挺在 binlog 的格式中,只是從 5.7.7 開始,它退居幕後,頭把交椅給了 ROW

    binglog 與我們開發中的程式碼日誌是不一樣的,它包含兩類文件

      索引文件:文件名.index,記錄了哪些日誌文件正在被使用,內容如下

        

      日誌文件:文件名.00000*

        

        記錄了對 MySQL 資料庫執行了更改的所有操作

    因為 binlog 的日誌文件是二進位文件,不能用文本編輯器直接打開,需要用特定的工具來打開,MySQL 提供了 mysqlbinlog 來幫助我們查看日誌文件內容

    mysqlbinlog 可選參數很多, mysqlbinlog.exe help 

mysqlbinlog.exe Ver 3.3 for Win64 at x86
Copyright (c) 2001, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Dumps a MySQL binary log in a format usable for viewing or for piping to
the mysql command line client.

Usage: mysqlbinlog.exe [options] log-files
  -?, --help          Display this help and exit.
  --base64-output[=name]
                      Determine when the output statements should be
                      base64-encoded BINLOG statements: 'never' disables it and
                      works only for binlogs without row-based events;
                      'decode-rows' decodes row events into commented SQL
                      statements if the --verbose option is also given; 'auto'
                      prints base64 only when necessary (i.e., for row-based
                      events and format description events); 'always' prints
                      base64 whenever possible. 'always' is deprecated, will be
                      removed in a future version, and should not be used in a
                      production system.  --base64-output with no 'name'
                      argument is equivalent to --base64-output=always and is
                      also deprecated.  If no --base64-output[=name] option is
                      given at all, the default is 'auto'.
  --character-sets-dir=name
                      Directory for character set files.
  -d, --database=name List entries for just this database (local log only).
  --debug-check       Check memory and open file usage at exit .
  --debug-info        Print some debug info at exit.
  -D, --disable-log-bin
                      Disable binary log. This is useful, if you enabled
                      --to-last-log and are sending the output to the same
                      MySQL server. This way you could avoid an endless loop.
                      You would also like to use it when restoring after a
                      crash to avoid duplication of the statements you already
                      have. NOTE: you will need a SUPER privilege to use this
                      option.
  -F, --force-if-open Force if binlog was not closed properly.
                      (Defaults to on; use --skip-force-if-open to disable.)
  -f, --force-read    Force reading unknown binlog events.
  -H, --hexdump       Augment output with hexadecimal and ASCII event dump.
  -h, --host=name     Get the binlog from server.
  -l, --local-load=name
                      Prepare local temporary files for LOAD DATA INFILE in the
                      specified directory.
  -o, --offset=#      Skip the first N entries.
  -p, --password[=name]
                      Password to connect to remote server.
  -P, --port=#        Port number to use for connection or 0 for default to, in
                      order of preference, my.cnf, $MYSQL_TCP_PORT,
                      /etc/services, built-in default (3306).
  --protocol=name     The protocol to use for connection (tcp, socket, pipe,
                      memory).
  -R, --read-from-remote-server
                      Read binary logs from a MySQL server.
  -r, --result-file=name
                      Direct output to a given file.
  --server-id=#       Extract only binlog entries created by the server having
                      the given id.
  --set-charset=name  Add 'SET NAMES character_set' to the output.
  --shared-memory-base-name=name
                      Base name of shared memory.
  -s, --short-form    Just show regular queries: no extra info and no row-based
                      events. This is for testing only, and should not be used
                      in production systems. If you want to suppress
                      base64-output, consider using --base64-output=never
                      instead.
  -S, --socket=name   The socket file to use for connection.
  --start-datetime=name
                      Start reading the binlog at first event having a datetime
                      equal or posterior to the argument; the argument must be
                      a date and time in the local time zone, in any format
                      accepted by the MySQL server for DATETIME and TIMESTAMP
                      types, for example: 2004-12-25 11:25:56 (you should
                      probably use quotes for your shell to set it properly).
  -j, --start-position=#
                      Start reading the binlog at position N. Applies to the
                      first binlog passed on the command line.
  --stop-datetime=name
                      Stop reading the binlog at first event having a datetime
                      equal or posterior to the argument; the argument must be
                      a date and time in the local time zone, in any format
                      accepted by the MySQL server for DATETIME and TIMESTAMP
                      types, for example: 2004-12-25 11:25:56 (you should
                      probably use quotes for your shell to set it properly).
  --stop-position=#   Stop reading the binlog at position N. Applies to the
                      last binlog passed on the command line.
  -t, --to-last-log   Requires -R. Will not stop at the end of the requested
                      binlog but rather continue printing until the end of the
                      last binlog of the MySQL server. If you send the output
                      to the same MySQL server, that may lead to an endless
                      loop.
  -u, --user=name     Connect to the remote server as username.
  -v, --verbose       Reconstruct SQL statements out of row events. -v -v adds
                      comments on column data types.
  -V, --version       Print version and exit.
  --open-files-limit=#
                      Used to reserve file descriptors for use by this program.

Variables (--variable-name=value)
and boolean options {FALSE|TRUE}  Value (after reading options)
--------------------------------- ----------------------------------------
base64-output                     (No default value)
character-sets-dir                (No default value)
database                          (No default value)
debug-check                       FALSE
debug-info                        FALSE
disable-log-bin                   FALSE
force-if-open                     TRUE
force-read                        FALSE
hexdump                           FALSE
host                              (No default value)
local-load                        (No default value)
offset                            0
port                              3307
read-from-remote-server           FALSE
server-id                         0
set-charset                       (No default value)
shared-memory-base-name           (No default value)
short-form                        FALSE
socket                            E:/soft/mysql5.5.8/tmp/mysql.sock
start-datetime                    (No default value)
start-position                    4
stop-datetime                     (No default value)
stop-position                     18446744073709551615
to-last-log                       FALSE
user                              (No default value)
open-files-limit                  18432

View Code

    這些參數不做細講,有興趣的可自行去查閱,我們重點來關注日誌文件的內容,執行 mysqlbinlog.exe ../data/mysqlbin.000004 

    可以看到,對資料庫執行了更改的操作

insert tbl_t1 values ('aaa'),('bbb');
update tbl_t1 set name = 'a1' where name = 'aaa';
delete from tbl_t1 where name = 'bbb';

    都是以明文形式的 SQL 記錄在日誌文件中,至於優缺點,我們看完另外兩種格式之後再來比較

  ROW

    MySQL 5.7.7 及之後版本,binlog 的默認格式是 ROW,我們基於 5.7.30 版本,來看下 ROW 格式 binlog 內容是怎樣的

    先產生資料庫更改操作

    更改操作有

create table tbl_row(
    name varchar(32),
    age int
);
insert into tbl_row values('qq',23),('ww',24);
update tbl_row set age = 18 where name = 'aa';
update tbl_row set age = 18 where name = 'qq';
delete from tbl_row where name = 'aa';
delete from tbl_row where name = 'ww';

    master 當前正在寫入的 binlog 文件: mysqlbin.000002 , position  從  2885  到  3929 

    接下來我們看下日誌文件中是怎麼記錄的,執行 mysqlbinlog.exe start-position=2885 –stop-position=3929 ../data/mysql-bin.000002  

    可以看到,表結構變更操作以明文形式的 SQL 記錄在日誌文件中(與 STATEMENT 一樣),但表數據變更的操作卻是以一坨一坨的密文形式記錄在日誌文件中,不便於我們閱讀

    慶幸的是,mysqlbinlog 提供參數 v 或 vv 來解密查看,執行 mysqlbinlog.exe base64-output=decode-rows -v –start-position=2885 –stop-position=3929 ../data/mysql-bin.000002  

    INSERT 沒什麼好注意的,每一列都插入對應的值

insert into tbl_row values('qq',23),('ww',24);

對應

### INSERT INTO `my_project`.`tbl_row`
### SET
###   @1='qq'
###   @2=23
### INSERT INTO `my_project`.`tbl_row`
### SET
###   @1='ww'
###   @2=24

View Code

    UPDATE 就有需要注意的了,雖然我們修改列只有一列,條件列也只有一列,但是日誌中記錄的卻是:修改列是全部列,條件列也是全部列,並且列值是具體的值,而沒有 NOW()、UUID() 這樣的函數

update tbl_row set age = 18 where name = 'qq';

對應

### UPDATE `my_project`.`tbl_row`
### WHERE
###   @1='qq'
###   @2=23
### SET
###   @1='qq'
###   @2=18

View Code

      表沒有明確的指定主鍵,滿足更新條件的記錄也只有一條,大家可以去試試:明確指定主鍵且滿足更新條件的記錄有多條的情況,看看 binlog 日誌是怎麼記錄的

    DELETE 與 UPDATE 一樣,雖說條件列只有一個,但日誌中記錄的確實全部列

delete from tbl_row where name = 'ww';

對應

### DELETE FROM `my_project`.`tbl_row`
### WHERE
###   @1='ww'
###   @2=24

View Code

     相較 STATEMENT,顯得更複雜,內容會多很多, 具體 ROW 有什麼優點,我們往下看

  MIXED

    字面意思:混合,那它混合誰? 還能混合誰?只能混合 STATEMENT 和 ROW

    大多數情況下,是以 STATEMENT 格式記錄 binlog 日誌(因為 MySQL 默認隔離級別是 RR,而又很少有人去修改默認隔離級別),當隔離級別為 RC 模式的時候,則修改為 ROW 模式記錄

    有些特殊場景,也是以 ROW 格式來記錄的,就不區分 RR 和 RC 了(摘自:關於binary log那些事——認真碼了好長一篇

    

    當然還有一個  NOW() ,說白了就是,只有具體的值才最可靠,其他依賴於上下文、環境的函數、系統變數都不可靠,因為它們會因上下文、環境而變化

    這個就不去展示具體的日誌內容了,有興趣的小夥伴自行去跑結果

  優缺點總結

    三種格式都已介紹完畢,相比之下,相信大家對它們各自的特點、優缺點已經有一定的了解了

    基於 binlog 的用途之一:主從複製(三個用途:主從複製、數據恢復、審計), 樓主給大家總結下它們的優缺點

    

    MIXED 的願景是好的:結合 STATEMENT 和 ROW 兩者的優點,產生一個完美的格式,但事與願違,它還是會有一些問題

    相比於準確性而言,性能優先順序會低一些(隨著技術的發展,硬體性能已不再是不可接受的瓶頸),所以推薦使用 ROW 格式

MySQL 的 binlog 與其默認隔離級別 RR 的關係

  從上面 binlog 格式的內容來看,似乎與默認隔離級別 RR 沒有半毛錢關係,先莫急,慢慢往下看

  RC,STATEMENT 下,各版 MySQL 執行表數據修改操作

    表引擎是 InnoDB,隔離級別是 RC,binlog_format=STATEMENT的統一前提下,我們分別看下 MySQl5.0.96、MySQL5.1.30、MySQL5.5.8、MySQL5.7.30 執行表數據更改操作的情況

    MySQl5.0.96 可以正常執行

    MySQL5.1.30 執行報錯,提示

ERROR 1598 (HY000): Binary logging not possible. Message: Transaction level 'READ-COMMITTED' in InnoDB is not safe for binlog mode 'STATEMENT'

    MySQL5.5.8、MySQL5.7.30 執行報錯,都提示

ERROR 1665 (HY000): Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.

    也就是說,MySQL5.1.30及之後,RC 隔離級別的 InnoDB 對 binlog_format 是有限制的,不能是 STATEMENT,否則表數據無法進行修改

    MySQL 4.x 系列,由於官方不提供下載了,沒法做測試,有 4.x 版本(或者5.1.21之前的5.1.x版本)的可以私信下我哦,不勝感激!

  不同 session 的操作記錄在 binlog 中的記錄順序

    我們用兩個 session 來執行更新操作,看下不同 session 的操作記錄在 binlog 中的記錄順序有什麼決定 

    可以看到 update tbl_rr_test set age = 20 where id = 1; 先執行,後 commit, update tbl_rr_test set age = 21 where id = 2; 後執行,先 commit,日誌中記錄的是:先commit的記錄在前面,後commit的記錄在後面,與執行時間點無關;就單個 session 來說,好理解,執行順序就是記錄順序;多個 session 之間的話,先 commit 的先記錄

    主庫對資料庫的更改是按執行時間的先後順序進行的,而 binlog 卻是按 commit 的先後順序記錄的,理論上來說就會出現 MySQL Bug23051 中的示例問題

  默認隔離級別 RR 與 binlog 的關係 

    我們來看看 MySQL Bug23051,裡面有說到,MySQL 5.1 的早期版本,隔離級別是 RC、binlog 格式是STATEMENT時,InnoDB 的主從複製是有 bug 的(5.1.21 中修復),而 5.0.x 是沒問題的,我們在 5.0.96 上跑下 Bug23051 中的例子

    可以看到,5.0.96 下的 InnoDB,在 RC 級別,binlog_format=STATEMENT 時, UPDATE t1 SET a=11 where b=2; 的事務未提交,則 UPDATE t1 SET b=2 where b=1; 的事務會被阻塞,那麼從庫複製的時候,數據是沒問題的

    所以,綜合前面的來看,從 MySQL5.0 開始,InnoDB 在 RC 級別,binlog_format=STATEMENT 時 主從複製是沒有 bug 的(5.0沒問題,5.1.21之前的5.1.x有問題,但官方不提供下載了,5.1.21及之後的版本不支援 RC 隔離級別下設置 binlog 為 STATEMENT)

    那麼 binlog 與 默認級別 RR 的關係就清楚了,就是煙哥在【原創】互聯網項目中mysql應該選什麼事務隔離級別中說的這段話:

那Mysql在5.0這個版本以前,binlog只支援STATEMENT這種格式!而這種格式在讀已提交(Read Commited)這個隔離級別下主從複製是有bug的,因此Mysql將可重複讀(Repeatable Read)作為默認的隔離級別!

    也就是說,在 MySQL5.0之前,將 RR 作為默認隔離級別,是為了規避大部分主從複製的bug(具體什麼bug,可詳看 Bug23051 中的案例,或者【原創】互聯網項目中mysql應該選什麼事務隔離級別中的案例),然後一直被沿用了下來而已;為什麼不是規避全部的主從複製 bug,因為在 RR 隔離級別、binlog_format=STATEMENT 下,使用系統函數(NOW()、UUID()等)時,還是會導致主從數據不一致

總結

  1、binlog 三個格式

    目前主流的 MySQL,binlog 格式有 3 種:STATEMENT、ROW、MIXED,從數據準確性考慮,推薦使用 ROW 格式

  2、binlog 默認格式

    MySQL 5.1.5 之前只支援 STATEMENT 格式的 binlog,5.1.5 開始支援 binlog_format=ROW,MySQL 5.7.7 之前,binlog 的默認格式都是 STATEMENT,在 5.7.7 及更高版本中,binlog_format的默認值才是 ROW

  3、主從複製 bug(InnoDB 引擎)

    MySQL 5.1.30及之後,InnoDB 下,開啟 RC 隔離級別的話是不能啟用 binlog_format=STATEMENT的

    RC、RR 隔離級別,binlog_format=MIXED,主從複製仍會有數據不一致的問題(受系統函數影響)

    RR 隔離級別,binlog_format=STATEMENT,主從複製仍會有數據不一致的問題(受系統函數影響)

    binlog_format=ROW,不管是 RC 隔離級別,還是 RR 隔離級別,主從複製仍不會有數據不一致的問題

  4、MySQL 為什麼默認隔離級別是 RR

    為了規避 MySQL5.0 以前版本的主從複製問題,然後一直被沿用了下來而已

  5、引擎選擇問題

    MySQL 5.6 及之後,InnoDB 做了大量的優化,性能並不比MyISAM低,說沒特別的理由,基本可以放棄 MyISAM 了

參考

  【原創】互聯網項目中mysql應該選什麼事務隔離級別

  【原創】研發應該懂的binlog知識(上)

  關於binary log那些事——認真碼了好長一篇

  mysql查看binlog日誌

  MySQL · 特性分析 · InnoDB對binlog_format的限制