1 what's binary log

• 搭建複製架構的時候，需要binary log 來記錄資料庫的修改event；
• 資料庫宕機恢復使用；
• 異常操作，緊急恢複數據使用；

2 Binary Logging Options and Variables

2.1 基礎參數

• 文件大小
  • max_binlog_size
    • 範圍4k-1G，預設為1G；這裡註意下，並非設置了 max_binlog_size=1G，binlog文件最大就為1G，當事務短且小的情況下，binlog接近1G的時候，就會flush log，生成新的binlog文件，但是，但是，但是，但是同個事務是不能夠跨多個binlog文件存儲，一個事務只能存儲在一個binlog文件。如果這個時候，有個大事務，假設單個SQL UPDATE了100w行數據，SQL產生的binlog日誌記錄有5G，那麼當前的binlog文件則會出現大於5G的情況，該事務結束後，才會切換binlog文件。
• 緩存大小
  • binlog_cache_size
    • binlog寫緩衝區設置大小，由於是記憶體，寫速度非常快，可以有效提高binlog的寫效率，如果資料庫中經常出現大事務，可以酌情提高該參數。
    • 那麼，如果觀察自家DB實例的binlog_cache_size設置是否合理呢？可以通過show status like 'Binlog_cache%';查看Binlog_cache_use and Binlog_cache_disk_use的使用情況，Binlog_cache_use表示用到binlog緩衝區的次數，Binlog_cache_disk_use ，使用臨時文件來存放binlog cache的次數，如果Binlog_cache_disk_use的次數過多，可以酌情提高該參數。詳見下圖。
      • 
  • binlog_stmt_cache_size
    • 保留一個事務內，非事務語句文本的緩存大小。預設32k。
    • 與binlog_cache_size一樣，也可以通過show status like 'binlog_stmt_cache%'來查看是否設置合理。查看參數為：Binlog_stmt_cache_use （使用緩存區的次數），Binlog_stmt_cache_disk_use（使用臨時文件的次數）
      • 
  • max_binlog_cache_size
    • 預設為4G，如果發生大事務占用binlog cache超過設置值，則會報錯 ： multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage。
    • 這時候，就有個疑問了，為啥存在了 binlog_cache_size的設置，還需要 max_binlog_cache_size呢？
      • 其實是這樣，當一個線程連接進來並開始執行事務的時候，資料庫會按照binlog_cache_size的大小分配給它一個緩衝區域，如果使用到的空間要大於binlog_cache_size，則會使用臨時文件來存儲，線程結束後再刪除臨時文件。
      • 而max_binlog_cache_size則是嚴格限制了一個多SQL事務總的使用binlog cache的大小，保留分配緩衝區域跟臨時文件，總大小不能超過max_binlog_cache_size的限制值，一旦超過，則會報錯multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage。
  • max_binlog_stmt_cache_size
    • 預設4G。超過則報錯。註意事項跟 max_binlog_cache_size 類似。
• binlog文件相關
  • log_bin_basename
    • binlog文件的命名方式
  • log_bin_index
    • binlog索引文件的絕對路徑
  • expire_logs_days
    • binlog保留的有效天數，過期的會自動刪除
    • 這裡有個小tips，假設當前binlog文件過多且大占用磁碟空間，可以修改小改參數，改參數只有在切換新的binlog文件時，才會刪除過期文件，也就是可以等資料庫把當前binlog寫滿後切換到新文件的時候刪除，也可以手動執行flush logs，手動切換binlog，同時會觸發對過期binlog文件的刪除。

• binlog開關
  • log_bin
    • 需要在資料庫配置文件中添加或者指定--log-bin=[base-name]啟動DB服務，重啟後修改才生效
      • 
• 日誌記錄內容相關
  • binlog_format
    • 多麼重要的參數，以至於本文開了一節來細講，詳見 第三部分
    • 設置binlog的記錄格式
    • 5.7.6前預設statement，5.7.7後預設row，可選row，mixed，statement
  • binlog_row_image
    • 主要針對當binlog_format=row格式 下的設置，
    • 預設full，可選full，minimal，noblob
  • binlog_rows_query_log_events
    • 主要針對當binlog_format=row格式 下的設置，如果基於row記錄binlog日誌，預設是只記錄變化的行數據，不記錄涉及執行的SQL語句，如果開啟此參數，則會一同記錄執行的SQL語句
    • 預設false
  • binlog_gtid_simple_recovery
    • GTID複製會使用到，該參數控制 配置了的GTID複製到實例，在重啟時或者清理binlog文件時，資料庫只需要打開最老跟最新兩個binlog文件取出gtid_purged and gtid_executed，不需要打開所有文件
    • 預設為false，這個參數是社區反饋給官方添加，調整這個選項設置為True，對性能會有所提高，但是在某些環境下，由於只打開兩個文件來計算，所以計算gtids值可能會出錯。而保持這個選項值為false，能確保計算總是正確。
    • 組提交（提高binary log併發提交的數據量）
  • binlog_group_commit_sync_delay
    • 預設為0
    • 結合binlog_group_commit_sync_no_delay_count來理解，見下文
  • binlog_group_commit_sync_no_delay_count
    • 預設為0
    • MySQL等待binlog_group_commit_sync_delay毫秒的時間直到 binlog_group_commit_sync_no_delay_count個數時進行一次組提交，如果binlog_group_commit_sync_delay毫秒內也還沒有到達指定的個數，也會提交。
    • flush disk相關
  • sync_binlog
    • 5.7.7前預設為0，之後預設為1，範圍0-4294967295

• • • sync_binlog =0，則是依賴操作系統刷新文件的機制，MySQL不會主動同步binlog內容到磁碟文件中去，而是依賴操作系統來刷新binary log。

• • • sync_binlog =N (N>0) ，則是MySQL 在每寫 N次 二進位日誌binary log時，會使用fdatasync()函數將它的寫二進位日誌binary log同步到磁碟中去。

• • • 註: 如果啟用了autocommit，那麼每一個語句statement就會有一次寫操作；否則每個事務對應一個寫操作。

• • • 如果設置sync_binlog =0 ，發生crash事件（伺服器），資料庫最高丟失binlog內容為1s內寫在file system buffer的內容；

• • • 如果設置sync_binlog =N ，發生crash事件（伺服器），資料庫最高丟失binlog內容為寫在file system buffer內 N個binlog events；
      • 

         

• • • 這個參數經常跟innodb_flush_log_at_trx_commit結合調整，提高性能或者提高安全性（詳細可查看上周博文：http://www.cnblogs.com/xinysu/p/6555082.html 中 “redo參數” 一節），這裡提2個推薦的配置：

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

• • • • innodb_flush_log_at_trx_commit=2 ，sync_binlog=N (N為500 或1000) ，但是但是但是，伺服器一定要待用蓄電池後備電源來緩存cache，在伺服器crash後，還能支持把file system buffer中的內容寫入到binlog file中，防止系統斷電異常。這種適合當磁碟IO無法滿足業務需求時，比如節假日或者周年活動產生的資料庫IO壓力，則推薦這麼設置。

3 Binary Logging Formats

• binlog_format
  • 多麼重要的參數，以至於本文開了一節來細講，詳見 第三部分
  • 設置binlog的記錄格式
  • 5.7.6前預設statement，5.7.7後預設row，可選row，mixed，statement
• binlog_row_image
  • 主要針對當binlog_format=row格式 下的設置，
  • 預設full，可選full，minimal，noblob
• binlog_rows_query_log_events
  • 主要針對當binlog_format=row格式 下的設置，如果基於row記錄binlog日誌，預設是只記錄變化的行數據，不記錄涉及執行的SQL語句，如果開啟此參數，則會一同記錄執行的SQL語句
  • 預設false

1. 設置binlog format格式；設置隔離級別；
2. 創建表格
3. INSERT操作（重點查看UUID（）函數使用情況）
4. UPDATE操作（檢查自動更新時間列）
5. DELETE操作

3.1 binlog_format=statement

1 #測試前環境準備及清理：
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;