開心一刻
產品還沒測驗直接投入生產時，這尼瑪...
背景問題
在講 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 18432View Code
這些引數不做細講，有興趣的可自行去查閱，我們重點來關注日志檔案的內容，執行 mysqlbinlog.exe ../data/mysql-bin.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 檔案： mysql-bin.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=24View 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=18View Code
表沒有明確的指定主鍵，滿足更新條件的記錄也只有一條，大家可以去試試：明確指定主鍵且滿足更新條件的記錄有多條的情況，看看 binlog 日志是怎么記錄的
DELETE 與 UPDATE 一樣，雖說條件列只有一個，但日志中記錄的確實全部列
delete from tbl_row where name = 'ww'; 對應 ### DELETE FROM `my_project`.`tbl_row` ### WHERE ### @1='ww' ### @2=24View 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的限制
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/185273.html
標籤：其他