MySQL二進位日誌相關問題詳解

 

本文來源： 
（保留出處並非什麼原創作品權利，本人拙作還遠遠達不到，僅是為了鏈接到原文，因為後續對可能存在的一些錯誤進行修正或補充，無他）

 

今天無意中發現了一個雲棲社區舉行的MySQL“第一季：挑戰玄慚之 慢SQL性能優化賽”，在測試伺服器上執行其測試腳本寫入數據的時候報錯提示如下，
Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage,increase this mysqld variable and try agagin

 提示max_binlog_cache_size空間不足，因為開啟了二進位日誌，之前是預設設定沒有大批量的事務性操作，沒有遇到該問題，這次一開始就遇到一個較大的事務性操作就失敗了。
之後修改binlog_cache_size的大小之後，問題解決。

 

由於使用的是預設的innodb引擎，開啟了二進位日誌，
對於事務性的操作，是要事物完成的時候寫入二進位日誌，因此在事物提交之前，執行的寫入性操作會被快取起來，直到整個事物完成，mysqld程序會將整個事物寫入二進位日誌。
當事物開始的時候，會依照binlog_cache_size系統變數指定的值分配內容空間，如果指定的binlog_cache_size快取空間不夠，執行的事務性操作回溯並提示失敗。

 

 

順便總結二進位日誌（binary log）以及其相關參數資訊

#什麼是二進位日誌？
　　用來記錄操作MySQL資料庫中的寫入性操作（增刪改，但不包含查詢），相當於sqlserver中的完整復原模式下的交易記錄檔。
二進位日誌的作用？
　　1，用於複製，配置了主從複製的時候，主伺服器會將其產生的二進位日誌發送到slave端，slave端會利用這個二進位日誌的資訊在本地重做，實現主從同步
　　2，使用者恢復，MySQL可以在全備和差異備份的基礎上，利用二進位日誌進行基於時間點或事物Id的恢復操作。原理雷同於主從複製的日誌重做。
 

二進位日誌（binary log）的相關參數資訊

1，開啟二進位日誌
　開啟二進位日誌，需要指定一個log-bin參數的路徑，例如：log_bin=/var/lib/mysql/mysql-bin
　開始二進位日誌之後會自動產生一個管理二進位日誌的log_bin_index檔。 log_bin選項也顯示為on，也即開啟了二進位日誌。

　

 

2，二進位日誌檔案的格式

　 二進位日誌的格式由參數binlog_format控制，二進位日誌有三種模式，基於語句(statement)的，基於行的（row），以及結合前兩者的混合模式（mixed）
　基於語句的二進位功能有一些硬傷（個人認為），例如同樣的update語句中的利用當前時間的now更新操作，在主伺服器和從伺服器上同樣都是取得目前時間，主從複製得到的結果是不一樣的。
　基於行的二進位日誌模式解決了基於語句的一些缺陷，但是某些情況下回產生大量的日誌，例如一個update操作更新了100W行數據，如果是基於行的二進位日誌，結果就是產生了100W條日誌
　基於混合模式的，結合上述兩種方式的優點。
　可以在設定檔中設定：binlog_format = MIXED

　

 

3，二進位日誌的記錄時機

　二進位日誌記錄可以讓同步的，也即事物提交之後就寫入二進位日誌，也可以是異步的，由作業系統的磁碟快取覺得什麼時候寫入磁碟。
　由參數sync_binlog= n來控制，設定sync_binlog = 1的話，表示最高安全等級的寫入（但也不能保證不遺失任何事物日誌），相當於是一種安全寫入模式，不過對效能有一定的影響。
　個人覺得，如果是事務性的引擎，本身就是為了確保事物安全的，沒理由不把sync_binlog 設定為1。

　據說設定為sync_binlog設定為1也會潛在遺失一個交易日誌的可能性，但還沒想明白為什麼會遺失，因為既然是事務性引擎，還有一層undo或redo日誌在做後盾啊？

　後面想想，因為有redo和undo日誌的存在，事物的一致性可以在主伺服器上得到保證，應該是主從複製的時候，可能丟失的一條事物可能無法傳遞到從伺服器。

　

 

4，二進位日誌的單一檔案大小

　二進位日誌的大下就是單一日誌檔案的最大限制，正常情況下都不會超過設定的最大檔案的大小限制，超過設定的最大限制之後，會發生日誌滾動，也即重新產生一個二進位日誌檔案。
　max_binlog_size  = 100M
　這裡顯示的104857600單位是字節，也即104857600/1024/1024 = 100M

>二進位日誌的清理

　二進位日誌滾動之後會產生新的檔案來儲存日誌，日誌檔案逾期之後會自動刪除，否則會產生來源來源不斷的日誌檔案

　例如可以設定過期時間為2，可配值為：expire_logs_days = 2，超過兩天的二進位日誌會自動刪除。

　可以透過指令show master logs 查看目前的二進位日誌檔個數

 　

 

6，二進位日誌檔案的捲動

#　1）正常情況下，記錄滿之後，自動滾動，後綴名+1

　2）重啟mysql服務之後，自動滾動，不管日誌檔案是否按照指定的最大容量寫滿

　3）手動滾動，執行flush logs指令，如下執行flush logs之後，重新產生了一個二進位日誌檔案

#　

　4）手動刪除二進位日誌

　可以透過指令

purge binary logs to fileName刪除指定fileName之前的檔案

　

　可以透過指令purge binary logs before '2017-03-10 10:10:00'刪除指定時間之前的檔案

　

　刪除指定日誌purge binary logs before date_sub( now( ), interval 7 day);

　瀟湘大神是purge master logs before date_p　瀟湘大神是purge master logs before date_sub( now( ), interval 7 天),應該是一個效果（binary和master關鍵字）？

 

7，二進位日誌的綁定（或排除）的資料庫

　可以設定某些資料庫開啟二進位日誌，或是某些資料庫不開啟二進位日誌

　# binlog_do_db:設定master-slave時使用;

　# binlog-ignore-db:設定哪個資料庫不記錄日誌;
　MySQL5.7.18中設定了（my.cnf中配置了），但是查詢的時候好像沒用?

　

 

8，二進位日誌的快取以及快取大小配置

　binlog_cache_size的大小，一開始提到的問題，當事物開始的時候，會按照binlog_cache_size系統變數指定的值分配內容空間，如果指定的binlog_cache_size快取空間不夠則會報錯並回滾事物

　這裡顯示的記錄的單位同樣是字節，除以兩個1024之後就是以MB為單位的容量了，這裡的20971520 /1024/1024就等於20MB了。

　如果有較大的事務性操作，例如在測試的時候，必須將此快取設定的相對較大一些，否則語句無法成功執行

　

max_binlog_cache_size語binlog_cache_size的差別在於前者是實例層級的cache，後者是Session層級的cache，如果並發量很大，就需要考慮將max_binlog_cache_size設定的稍微大一些。

　max_binlog_cache_size預設是4GB，最大值也是4GB，這裡為了測試設定的是100MB（104857600/1024.0/1024.0）

　

###

    max_binlog_cache_size設定的最大記憶體大小為4GB，若伺服器內容較大，例如128GB或更大，max_binlog_cache_size預設設定為最大且無傷大雅，因為要確保並發成功寫入。
　至於對於Session級別的binlog_cache_size大小，可以根據業務情況自行調整，個人覺得設定的稍微大一點也問題不大，畢竟，除了正常的OLTP操作，有一些定時作業之類的數據提取或者merge數據之類的操作可能會產生大量的日誌。
　據說是可以透過查看binlog_cache_disk_use 與 binlog_cache_use來判斷binlog_cache_size是否需要調整。
　但在MySQL5.7.18中並沒有發現這個參數

　

## 

9，二進位日誌其他參數

　max_binlog_stmt_cache_size針對非事務語句，非事務性的參數暫不關心它了

　記得某次看到過某大師說過，innodb引擎優勢不僅僅在事務性的支持上，與非事物引起的myisam引擎相比，讀取效能上差距越來越小，MySQL因此將innodb設定為預設引擎。
　放棄myisam，投奔innodb是正道。
　binlog_checksum 用作複製的主從校檢。暫時沒有研究過這個參數，暫不論
　詳細參考想想大神的文章 。

 

 

總結：

　　MySQL二進位日誌不僅作用於功能性（master-slave複製）的，也作用於安全性（二進位日誌）以及開啟了二進制日誌情況下的事務性操作，因此對於生產環境，可以認為是必不可少的配置。

　　同時，其各種參數又會影響到某些操作，因此二進位日誌的參數要格外的重視，確保資料庫在使用時在功能性和可用性上得到保證。

 

參考： 

　　　《塗抹MySQL》

　　　以及各種翻書，網路資料

 

################################################################################################################################################################################################################行動起來，可以改變思考模式以及畏懼心理。 ###### ###

以上是MySQL二進位日誌相關問題詳解的詳細內容。更多資訊請關注PHP中文網其他相關文章！