閱讀目錄(Content)

• 1 what's binary log

• 2 Binary Logging Options and Variables

• 2.1 基礎(chǔ)參數(shù)

• 3 Binary Logging Formats

• 3.1 binlog_format=statement

• 3.2 binlog_format=row

• 3.2.1 binlog_row_image默認(rèn)full，binlog_rows_query_log_events默認(rèn)false

• 3.2.2 binlog_rows_query_log_events 設(shè)置

• 3.3 binlog_format=mixed

• 4 binlog server的搭建

• 5 Tips

• 5.1 修改會話級別的binlog格式

• 5.2 查看binlog中每個事務(wù)的開始跟結(jié)束

• 5.3 根據(jù)binlog格式恢復(fù)數(shù)據(jù)（row格式）

• 5.4 binlog文件清理

• 5.5 與binlog相關(guān)的指令

本文介紹binlog的作用以及幾個重要參數(shù)的使用方法，同時通過實驗來描述binlog內(nèi)部記錄內(nèi)容：row 、statement跟mixed的設(shè)置下，記錄了哪些東西，最后會簡單介紹下binlog server的搭建以及一些關(guān)于binlog使用的小Tips。

理解跟熟悉binlog相關(guān)內(nèi)容，對復(fù)制原理及故障處理會有很大幫助的。

如果轉(zhuǎn)載，請注明博文來源： www.cnblogs.com/xinysu/   ，版權(quán)歸 博客園內(nèi) 蘇家小蘿卜 所有。望各位支持！

回到頂部(go to top)

1 what's binary log

Binary log 用來記錄數(shù)據(jù)庫中發(fā)生的修改情況，比如數(shù)據(jù)的修改、表格的創(chuàng)建及修改等，它既可以記錄涉及修改的SQL，也可以記錄數(shù)據(jù)修改的行變化記錄，同時也記錄了執(zhí)行時間。比如，執(zhí)行sql：update tabname set cola='a' where id between 1 and 5，修改了5行記錄。當(dāng)開啟binlog記錄的時候，根據(jù)設(shè)置的binlog格式，可能記錄的是這一條SQL語句，也可能記錄的是5行數(shù)據(jù)記錄的修改情況，也可能兩者都有，這部分詳情可以看本博文的第3部分：binlog formats。

這里注意跟general log區(qū)分下，binnary log是記錄數(shù)據(jù)庫內(nèi)部的修改情況，而general log是記錄所有數(shù)據(jù)庫的SQL操作情況，比如像select或者show這種語句，不會發(fā)生數(shù)據(jù)修改，則不會記錄到binnary log，但是屬于數(shù)據(jù)庫操作記錄，會記錄到general log。

那么，開啟它，有什么好處，有什么確定呢 ？

首先，好處有3個：

• 搭建復(fù)制架構(gòu)的時候，需要binary log 來記錄數(shù)據(jù)庫的修改event；

• 數(shù)據(jù)庫宕機恢復(fù)使用；

• 異常操作，緊急恢復(fù)數(shù)據(jù)使用；

那么，當(dāng)開啟binlog記錄日志的時候，也就以為著有一定的IO量被占用，相對而言，數(shù)據(jù)庫會比不開啟的時候稍微慢些。但是由于帶來的好處比較多且重要，這點性能影響在大多數(shù)情況下可以忽略。

回到頂部(go to top)

2 Binary Logging Options and Variables

2.1 基礎(chǔ)參數(shù)

• 文件大小

• 范圍4k-1G，默認(rèn)為1G；這里注意下，并非設(shè)置了 max_binlog_size=1G，binlog文件最大就為1G，當(dāng)事務(wù)短且小的情況下，binlog解決1G的時候，就會flush log，生成新的binlog文件，但是，但是，但是，但是同個事務(wù)是不能夠跨多個binlog文件存儲，一個事務(wù)只能存儲在一個binlog文件。如果這個時候，有個大事務(wù)，假設(shè)單個SQL UPDATE了100w行數(shù)據(jù)，SQL產(chǎn)生的binlog日志記錄有5G，那么當(dāng)前的binlog文件則會出現(xiàn)大于5G的情況，該事務(wù)結(jié)束后，才會切換binlog文件。

• max_binlog_size

• 緩存大小

• 默認(rèn)4G。超過則報錯。注意事項跟 max_binlog_cache_size 類似。

• 默認(rèn)為4G，如果發(fā)生大事務(wù)占用binlog cache超過設(shè)置值，則會報錯 ： multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage。

• 這時候，就有個疑問了，為啥存在了 binlog_cache_size的設(shè)置，還需要 max_binlog_cache_size呢？

• 其實是這樣，當(dāng)一個線程連接進(jìn)來并開始執(zhí)行事務(wù)的時候，數(shù)據(jù)庫會按照binlog_cache_size的大小分配給它一個緩沖區(qū)域，如果使用到的空間要大于binlog_cache_size，則會使用臨時文件來存儲，線程結(jié)束后再刪除臨時文件。

• 而max_binlog_cache_size則是嚴(yán)格限制了一個多SQL事務(wù)總的使用binlog cache的大小，保留分配緩沖區(qū)域跟臨時文件，總大小不能超過max_binlog_cache_size的限制值，一旦超過，則會報錯multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage。

• 保留一個事務(wù)內(nèi)，非事務(wù)語句文本的緩存大小。默認(rèn)32k。

• 與binlog_cache_size一樣，也可以通過show status like 'binlog_stmt_cache%'來查看是否設(shè)置合理。查看參數(shù)為：Binlog_stmt_cache_use （使用緩存區(qū)的次數(shù)），Binlog_stmt_cache_disk_use（使用臨時文件的次數(shù)）

• 
  

• binlog寫緩沖區(qū)設(shè)置大小，由于是內(nèi)存，寫速度非常快，可以有效提高binlog的寫效率，如果數(shù)據(jù)庫中經(jīng)常出現(xiàn)大事務(wù)，可以酌情提高該參數(shù)。

• 那么，如果觀察自家DB實例的binlog_cache_size設(shè)置是否合理呢？可以通過show status like 'Binlog_cache%';查看Binlog_cache_use and Binlog_cache_disk_use的使用情況，Binlog_cache_use表示用到binlog緩沖區(qū)的次數(shù)，Binlog_cache_disk_use ，使用臨時文件來存放binlog cache的次數(shù)，如果Binlog_cache_disk_use的次數(shù)過多，可以酌情提高該參數(shù)。詳見下圖。

• 
  

• binlog_cache_size

• binlog_stmt_cache_size

• max_binlog_cache_size

• max_binlog_stmt_cache_size

• binlog文件相關(guān)

• binlog保留的有效天數(shù)，過期的會自動刪除

• 這里有個小tips，假設(shè)當(dāng)前binlog文件過多且大占用磁盤空間，可以修改小改參數(shù)，改參數(shù)只有在切換新的binlog文件時，才會刪除過期文件，也就是可以等數(shù)據(jù)庫把當(dāng)前binlog寫滿后切換到新文件的時候刪除，也可以手動執(zhí)行flush logs，手動切換binlog，同時會觸發(fā)對過期binlog文件的刪除。

• binlog索引文件的絕對路徑

• binlog文件的命名方式

• log_bin_basename

• log_bin_index

• expire_logs_days

2.2 重要參數(shù)

• binlog開關(guān)

• 需要在數(shù)據(jù)庫配置文件中添加或者指定--log-bin=[base-name]啟動DB服務(wù)，重啟后修改才生效

• 
  

• log_bin

• 日志記錄內(nèi)容相關(guān)

• 5.7.7前默認(rèn)為0，之后默認(rèn)為1，范圍0-4294967295

• 默認(rèn)為0

• MySQL等待binlog_group_commit_sync_delay毫秒的時間直到 binlog_group_commit_sync_no_delay_count個數(shù)時進(jìn)行一次組提交，如果binlog_group_commit_sync_delay毫秒內(nèi)也還沒有到達(dá)指定的個數(shù)，也會提交。

• flush disk相關(guān)

• 默認(rèn)為0

• 結(jié)合binlog_group_commit_sync_no_delay_count來理解，見下文

• GTID復(fù)制會使用到，該參數(shù)控制 配置了的GTID復(fù)制到實例，在重啟時或者清理binlog文件時，數(shù)據(jù)庫只需要打開最老跟最新兩個binlog文件取出gtid_purged and gtid_executed，不需要打開所有文件

• 默認(rèn)為false，這個參數(shù)是社區(qū)反饋給官方添加，調(diào)整這個選項設(shè)置為True，對性能會有所提高，但是在某些環(huán)境下，由于只打開兩個文件來計算，所以計算gtids值可能會出錯。而保持這個選項值為false，能確保計算總是正確。

• 組提交（提高binary log并發(fā)提交的數(shù)據(jù)量）

• 主要針對當(dāng)binlog_format=row格式 下的設(shè)置，如果基于row記錄binlog日志，默認(rèn)是只記錄變化的行數(shù)據(jù)，不記錄涉及執(zhí)行的SQL語句，如果開啟此參數(shù)，則會一同記錄執(zhí)行的SQL語句

• 默認(rèn)false

• 主要針對當(dāng)binlog_format=row格式 下的設(shè)置，

• 默認(rèn)full，可選full，minimal，noblob

• 多么重要的參數(shù)，以至于本文開了一節(jié)來細(xì)講，詳見 第三部分

• 設(shè)置binlog的記錄格式

• 5.7.6前默認(rèn)statement，5.7.7后默認(rèn)row，可選row，mixed，statement

• binlog_format

• binlog_row_image

• binlog_rows_query_log_events

• binlog_gtid_simple_recovery

• binlog_group_commit_sync_delay

• binlog_group_commit_sync_no_delay_count

• sync_binlog

• 
  

• sync_binlog =0，則是依賴操作系統(tǒng)刷新文件的機制，MySQL不會主動同步binlog內(nèi)容到磁盤文件中去，而是依賴操作系統(tǒng)來刷新binary log。

• 
  

• 
  

• sync_binlog =N (N>0) ，則是MySQL 在每寫 N次 二進(jìn)制日志binary log時，會使用fdatasync()函數(shù)將它的寫二進(jìn)制日志binary log同步到磁盤中去。

• 
  

• 
  

• 注: 如果啟用了autocommit，那么每一個語句statement就會有一次寫操作；否則每個事務(wù)對應(yīng)一個寫操作。

• 
  

• 
  

• 如果設(shè)置sync_binlog =0 ，發(fā)生crash事件（無論是數(shù)據(jù)庫服務(wù)還是服務(wù)器），數(shù)據(jù)庫最高丟失binlog內(nèi)容為1s內(nèi)寫在file system buffer的內(nèi)容；

• 
  

• 
  

• 如果設(shè)置sync_binlog =N ，發(fā)生crash事件（無論是數(shù)據(jù)庫服務(wù)還是服務(wù)器），數(shù)據(jù)庫最高丟失binlog內(nèi)容為寫在file system buffer內(nèi) N個binlog events；

• 

   

• 
  

• 
  

• 這個參數(shù)經(jīng)常跟innodb_flush_log_at_trx_commit結(jié)合調(diào)整，提高性能或者提高安全性（詳細(xì)可查看上周博文：http://www.cnblogs.com/xinysu/p/6555082.html 中 “redo參數(shù)” 一節(jié)），這里提2個推薦的配置：

• 
  

• 
  

• 
  

•  innodb_flush_log_at_trx_commit和sync_binlog  都為 1（俗稱雙一模式），在mysqld 服務(wù)崩潰或者服務(wù)器主機crash的情況下，binary log 只有可能丟失最多一個語句或者一個事務(wù)。但是有得必有舍，這個設(shè)置是最安全但也是最慢的。適合數(shù)據(jù)一致性要求較高，核心業(yè)務(wù)使用。

• 
  

• 
  

• 
  

• innodb_flush_log_at_trx_commit=2 ，sync_binlog=N (N為500 或1000) ，但是但是但是，服務(wù)器一定要待用蓄電池后備電源來緩存cache，在服務(wù)器crash后，還能支持把file system buffer中的內(nèi)容寫入到binlog file中，防止系統(tǒng)斷電異常。這種適合當(dāng)磁盤IO無法滿足業(yè)務(wù)需求時，比如節(jié)假日或者周年活動產(chǎn)生的數(shù)據(jù)庫IO壓力，則推薦這么設(shè)置。

• 
  

回到頂部(go to top)

3 Binary Logging Formats

這一部分，將通過實驗來說明。我們會使用到mysqlbinlog指令，其具體用法詳見：https://dev.mysql.com/doc/refman/5.7/en/mysqlbinlog.html 。

還記得你剛剛看到“日志記錄內(nèi)容相關(guān) ” 小節(jié)里那三個紅燦燦喜洋洋的參數(shù)嗎？哈哈哈，見下文：

• binlog_format

• 多么重要的參數(shù)，以至于本文開了一節(jié)來細(xì)講，詳見 第三部分

• 設(shè)置binlog的記錄格式

• 5.7.6前默認(rèn)statement，5.7.7后默認(rèn)row，可選row，mixed，statement

• binlog_row_image

• 主要針對當(dāng)binlog_format=row格式 下的設(shè)置，

• 默認(rèn)full，可選full，minimal，noblob

• binlog_rows_query_log_events

• 主要針對當(dāng)binlog_format=row格式 下的設(shè)置，如果基于row記錄binlog日志，默認(rèn)是只記錄變化的行數(shù)據(jù)，不記錄涉及執(zhí)行的SQL語句，如果開啟此參數(shù)，則會一同記錄執(zhí)行的SQL語句

• 默認(rèn)false

實驗內(nèi)容：

1. 設(shè)置binlog format格式；設(shè)置隔離級別；

2. 創(chuàng)建表格

3. INSERT操作（重點查看UUID（）函數(shù)使用情況）

4. UPDATE操作（檢查自動更新時間列）

5. DELETE操作

3.1 binlog_format=statement

1 #測試前環(huán)境準(zhǔn)備及清理：2 mysql> set binlog_format='statement';3 mysql> SET session tx_isolation='REPEATABLE-READ';4 mysql> SELECT @@GLOBAL.tx_isolation, @@tx_isolation;5 mysql> show variables like 'binlog_format' ;6 mysql> flush logs;7 mysql> show master status;

測試前環(huán)境準(zhǔn)備及清理：

模擬DDL操作及DML操作：

DDL跟DML模擬結(jié)束后，得到當(dāng)前的binlog文件是 ，結(jié)束的position是，所以直接讀取整個文件從position=154到i2216之間的操作記錄，使用mysqlbinlog讀取。

[root@localhost ~]# /usr/local/mysql/bin/mysqlbinlog --start-position=154 --stop-position=2216

 statement 格式的binlog

逐個事務(wù)拆分開看如下圖：

小結(jié)：

• 當(dāng)binlog_format=statement的時候，DDL及DML都是明文按照SQL記錄存儲

• 對復(fù)制的影響

• 某系統(tǒng)參數(shù)由于在不同時間不同服務(wù)器，執(zhí)行結(jié)果不一致，這會給復(fù)制的主從帶來數(shù)據(jù)不一致的嚴(yán)重影響

• LOAD_FILE()， UUID()， USER()，F(xiàn)OUND_ROWS()，defaults now()及用戶自定義函數(shù)等

• 同步到從庫的binlog都是SQL語句，在slave端再跑一遍，假設(shè)一個update語句性能很差，但是最終只修改了一行數(shù)據(jù)，那么在從庫也會同樣執(zhí)行這個性能差的SQL

• 而對于 insert tb select * from tbname 這類型的SQL，則只需要同步一行SQL語句即可

3.2 binlog_format=row

3.2.1 binlog_row_image默認(rèn)full，binlog_rows_query_log_events默認(rèn)false

 1 set binlog_format='row'; 2 SET session tx_isolation='REPEATABLE-READ'; 3 SELECT @@GLOBAL.tx_isolation, @@tx_isolation; 4 show variables like 'binlog_format' ; 5 show master status; 6 flush logs; 7 show master status; 8   9 10 CREATE TABLE `tbrow` (11   `id` int(11) NOT NULL AUTO_INCREMENT,12   `rowformat` varchar(50) NOT NULL,13   `uuids` varchar(50) NOT NULL,14   `timepoint` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,15    CurrentVersion timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,16   PRIMARY KEY (`id`)17 ) ENGINE=InnoDB;18 insert into tbrow(rowformat,uuids) select 'row',uuid();19 insert into tbrow(rowformat,uuids) select 'row',uuid();20 update tbrow set rowformat='tbstatement1_update' where id=1;21 delete from tbrow where id=2;22 DROP TABLE tbrow;23 show master status;

找到開始與結(jié)束的position點，查看這個區(qū)間的binlog日志內(nèi)容：/usr/local/mysql/bin/mysqlbinlog --start-position=154 --stop-position=2196  /data/mysql/mysql3306/logs/bin_log.000017

發(fā)現(xiàn)，在row格式下，DML是加密存儲，好在mysqlbinlog提供參數(shù)-v 反解析查看，指令如下：

[root@localhost ~]# /usr/local/mysql/bin/mysqlbinlog --base64-output=decode-rows -v -v --start-position=154 --stop-position=2196  /data/mysql/mysql3306/logs/bin_log.000017

小結(jié)：

• 當(dāng)binlog_format=row的時候，其他參數(shù)默認(rèn)，DDL明文存儲SQL腳本，DML都是加密存儲且存儲的是每一行的行記錄修改情況

• 對復(fù)制的影響

• 最安全的同步設(shè)置

• 同步到從庫的binlog都是按行記錄修改的SQL，所以假設(shè)一個update語句性能很差，但是最終只修改了一行數(shù)據(jù)，那么在從庫不需要執(zhí)行這個性能差的SQL，只需要直接執(zhí)行行記錄的修改結(jié)果即可（注意，使用基于row格式復(fù)制的實例，請給所有表格添加主鍵或者唯一索引，不然每一行記錄的修改都需要全表掃，會導(dǎo)致從庫性能非常差而且可能延時較長）

• 而對于 insert tb select * from tbname 這類型的SQL，statment格式的只需要同步一條sql，但是row的話，則需要同步所有行記錄。

3.2.2 binlog_rows_query_log_events 設(shè)置

從上小節(jié)可以看出，當(dāng)binlog_format=row的時候，只記錄行修改情況，不記錄執(zhí)行的SQL的。啟動這個參數(shù)，則可在row格式下查看到執(zhí)行的sql語句。

造數(shù)據(jù)中：

查看binlog日志如下：

3.2.3 binlog_row_image設(shè)置

默認(rèn) full，可選full，minimal，noblob

• full的結(jié)果可看上文的所有截圖，update的時候，set是全部列的新紀(jì)錄，where是全部的舊記錄；

• noblob

• 

• 

• minimal
  

• 

小結(jié)

• 設(shè)置為full，則update語句的set部分是新紀(jì)錄內(nèi)容，delete及update語句的where部分是全部舊記錄內(nèi)容

• 

• 設(shè)置為noblob，則update語句的set部分是新紀(jì)錄內(nèi)容，delete及update語句的where部分是全部舊記錄內(nèi)容，但是，如果如果修改的列不是blob或者text字段，則set部分不會出現(xiàn)blob及text字段；where條件中無論涉不涉及，都不會出現(xiàn)；

• 這個截圖表格有3列，( id int，name text, description varchar(50))，只update id為2，binlog記錄如下：

• 

• 設(shè)置為minimal ，則update語句的set部分只有修改的列內(nèi)容，delete及update語句的where部分是主鍵或者唯一索引，如果都沒有，才會使整行舊記錄。

• 這個截圖中，有5列，主鍵是id，第5列是根據(jù)行變動記錄時間，update第二列的值，第5列值自動更新，binlog記錄如下：

• 
  

3.3 binlog_format=mixed

理解完statement跟row模式后，mixed混合模式就好理解了。

mixed模式下，大多數(shù)情況下，是以statement格式記錄binlog日志，當(dāng)隔離級別為RC模式的時候，則修改為row模式記錄，以下幾個形式，也是以row模式記錄：

• When a function contains UUID().

• When one or more tables with AUTO_INCREMENT columns are updated and a trigger or stored function is invoked. Like all other unsafe statements, this generates a warning if binlog_format = STATEMENT

• 
  

延伸閱讀

• 關(guān)于binary log那些事——認(rèn)真碼了好長一篇 2017-06-29

學(xué)習(xí)是年輕人改變自己的最好方式