MySQL二進制日志相關問題詳細說明

今天無意中發(fā)現(xiàn)了一個云棲社區(qū)舉行的MySQL“第一季：挑戰(zhàn)玄慚之 慢SQL性能優(yōu)化賽”，在測試服務器上執(zhí)行其測試腳本寫入數(shù)據(jù)的時候報錯提示如下，
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引擎，開啟了二進制日志，
對于事務性的操作，是要事物完成的時候?qū)懭攵�進制日志，事物提交之前，執(zhí)行的寫入性操作會被緩存起來，直到整個事物完成，mysqld進程會將整個事物寫入二進制日志。
當事物開始的時候，會按照binlog_cache_size系統(tǒng)變量指定的值分配內(nèi)容空間，如果指定的binlog_cache_size緩存空間不夠，執(zhí)行的事務性操作回滾并提示失敗。

順便總結一下二進制日志（binary log）以及其相關參數(shù)信息

什么是二進制日志？
　　用來記錄操作MySQL數(shù)據(jù)庫中的寫入性操作（增刪改，但不包括查詢），相當于sqlserver中的完整恢復模式下的事務日志文件。
二進制日志的作用？
　　1，用于復制，配置了主從復制的時候，主服務器會將其產(chǎn)生的二進制日志發(fā)送到slave端，slave端會利用這個二進制日志的信息在本地重做，實現(xiàn)主從同步
　　2，用戶恢復，MySQL可以在全備和差異備份的基礎上，利用二進制日志進行基于時間點或者事物Id的恢復操作。原理雷同于主從復制的日志重做。

二進制日志（binary log）的相關參數(shù)信息

1，開啟二進制日志
　開啟二進制日志，需要指定一個log-bin參數(shù)的路徑，比如：log_bin=/var/lib/mysql/mysql-bin
　開始二進制日志之后會自動生成一個管理二進制日志的log_bin_index文件。log_bin選項也顯示為on，也即開啟了二進制日志。

2，二進制日志文件的格式

　 二進制日志的格式由參數(shù)binlog_format控制，二進制日志有三種模式，基于語句(statement)的，基于行的（row），以及結合前兩者的混合模式（mixed）
　基于語句的二進制功能有一些硬傷（個人認為），比如同樣的update語句中的利用當前時間的now更新操作，在主服務器和從服務器上同樣都是獲取當前時間，主從復制得到的結果是不一樣的。
　基于行的二進制日志模式解決了基于語句的一些缺陷，但是某些情況下回產(chǎn)生大量的日志，比如一個update操作更新了100W行數(shù)據(jù)，如果是基于行的二進制日志，結果就是產(chǎn)生了100W條日志
　基于混合模式的，結合上述兩種方式的優(yōu)點。
　可以在配置文件中設置：binlog_format = MIXED

3，二進制日志的記錄時機

　二進制日志記錄可以使同步的，也即事物提交之后就寫入二進制日志，也可以是異步的，由操作系統(tǒng)的磁盤緩存覺得什么時候?qū)懭氪疟P。
　由參數(shù)sync_binlog= n來控制，設置sync_binlog = 1的話，表示最高安全級別的寫入（但也不能保證不丟失任何事物日志），相當于是一種安全寫入模式，不過對性能有一定的影響。
　個人覺得，如果是事務性的引擎，本身就是為了保證事物安全的，沒理由不把sync_binlog 設置為1。

　據(jù)說設置為sync_binlog設置為1也會潛在丟失一個事務日志的可能性，但是還沒想明白為什么會丟失，因為既然是事務性引擎，還有一層undo或者redo日志在做后盾啊？

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

4，二進制日志的單個文件大小

　二進制日志的大下就是單個日志文件的最大限制，正常情況下都不會超過設置的最大文件的大小限制，超過設置的最大限制之后，會發(fā)生日志滾動，也即重新生成一個二進制日志文件。
　max_binlog_size = 100M
　這里顯示的104857600單位是字節(jié)，也即104857600/1024/1024 = 100M

5，二進制日志的清理

　二進制日志滾動之后會生成新的文件來存儲日志，日志文件逾期之后會自動刪除，否則會產(chǎn)生源源不斷的日志文件
　比如可以設置過期時間為2，可配值為：expire_logs_days = 2，超過兩天的二進制日志會被自動刪除。
　可以通過命令show master logs 查看當前的二進制日志文件個數(shù)

6，二進制日志文件的滾動

　1）正常情況下，記錄滿之后，自動滾動，后綴名+1
　2）重啟mysql服務之后，自動滾動，不管日志文件是否按照指定的最大容量寫滿
　3）手動滾動，執(zhí)行flush logs命令，如下執(zhí)行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_sub( now( ), interval 7 day),應該是一個效果（binary和master關鍵詞）？

7，二進制日志的綁定（或者排除）的數(shù)據(jù)庫

　可以設置某些數(shù)據(jù)庫開啟二進制日志，或者某些數(shù)據(jù)庫不開啟二進制日志
　# binlog_do_db:設置master-slave時使用;
　# binlog-ignore-db:設置哪個數(shù)據(jù)庫不記錄日志;
　MySQL5.7.18中設置了（my.cnf中配置了），但是查詢的時候好像沒用?

8，二進制日志的緩存以及緩存大小配置

　binlog_cache_size的大小，一開始提到的問題，當事物開始的時候，會按照binlog_cache_size系統(tǒng)變量指定的值分配內(nèi)容空間，如果指定的binlog_cache_size緩存空間不夠則會報錯并回滾事物
　這里顯示的記錄的單位同樣是字節(jié)，除以兩個1024之后就是以MB為單位的容量了，這里的20971520 /1024/1024就相當于20MB了。
　如果有較大的事務性操作，比如在測試的時候，必須要將此緩存設置的相對較大一些，否則語句無法成功執(zhí)行

　max_binlog_cache_size語binlog_cache_size的區(qū)別在于前者是實例級別的cache，后者是Session級別的cache，如果并發(fā)量很大，就需要考慮將max_binlog_cache_size設置的稍微大一些。
　max_binlog_cache_size默認是是4GB，最大值也是4GB，這里為了測試設置的是100MB（104857600/1024.0/1024.0）

max_binlog_cache_size設置的最大內(nèi)存大小為4GB，如果服務器內(nèi)容較大，比如128GB或者更大，max_binlog_cache_size默認為設置為最大也無傷大雅，因為要保證并發(fā)成功寫入。
　至于對于Session級別的binlog_cache_size大小，可以根據(jù)業(yè)務情況自行調(diào)整，個人覺得設置的稍微大一點也問題不大，畢竟，除了正常的OLTP操作，有一些定時作業(yè)之類的數(shù)據(jù)提取或者merge數(shù)據(jù)之類的操作可能會產(chǎn)生大量的日志。
　據(jù)說是可以通過查看binlog_cache_disk_use 與 binlog_cache_use來判斷binlog_cache_size是否需要調(diào)整。
　但是在MySQL5.7.18中并沒有發(fā)現(xiàn)這個參數(shù)

9，二進制日志其他參數(shù)

　max_binlog_stmt_cache_size針對非事務語句，非事務性的參數(shù)暫不關心它了
　記得某次看到過某大師說過，innodb引擎優(yōu)勢不僅僅在事務性的支持上，與非事物引起的myisam引擎相比，讀取性能上差距越來越小，MySQL因此將innodb設置為默認引擎。
　放棄myisam，投奔innodb是正道。
　binlog_checksum 用作復制的主從校檢。暫時沒有研究過這個參數(shù)，暫不論
　詳細參考想想大神的文章 。

總結：

　　MySQL二進制日志不僅僅作用于功能性（master-slave復制）的，還作用于安全性（二進制日志）以及開啟了二進制日志情況下的事務性操作，因此對于生產(chǎn)環(huán)境，可以認為是一個必不可少的配置。
　　同時，其各種參數(shù)又會影響到某些操作，因此二進制日志的參數(shù)要格外的重視，確保數(shù)據(jù)庫在使用時在功能性和可用性上得到保證。

參考：

　　　《涂抹MySQL》

　　　以及各種翻書，網(wǎng)上資料

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

以上就是MySQL二進制日志相關問題詳解的詳細內(nèi)容，更多請關注php中文網(wǎng)其它相關文章！