MySQL/MariaDB的binlog二進位記錄模式及刪除方法

有時候會發現binlog突然間變得的很大，導致磁碟分割都滿了，這時候就需首先需要對binlog做清除，清除binlog時，如果有一個活性的從屬伺服器，該伺服器當前正在讀取您正在試圖刪除的日誌之一，則本語句不會起作用，而是會失敗，並伴隨一個錯誤。不過，如果從屬伺服器是休止的，並且您碰巧清理了其想要讀取的日誌之一，則從屬伺服器啟動後不能複製。當從屬伺服器正在複製時，本語句可以安全運行。您不需要停止它們，當然清除binlog只是臨時的動作，我更應該需要查出是什麼東西導致了binlog的猛然增長。這個可以看binlog日誌的內容應該很容易發現的。
一、Row Level
日誌中會記錄成每一行資料被修改的形式，然後在slave端再對相同的資料進行修改。
優點：在row level模式下，bin-log中可以不記錄執行的sql語句的上下文相關的資訊，僅僅只需要記錄那一條記錄被修改了，修改成什麼樣了。所以row level的日誌內容會非常清楚的記錄下每一行資料修改的細節，非常容易理解。而且不會出現某些特定情況下的預存程序，或function，以及 trigger的調用和觸發無法被正確複製的問題。
對於一個寫入操作，row格式binlog分為四部分，仿次為: BEGIN -> Table_map -> Write_rows/Delete_rows/Update_rows -> COMMIT
優點：任何情況都可以被複製，這對複製來說是最安全可靠的；
和其他大多數資料庫系統的複製技能一樣；
多數情況下，從伺服器上的表如果有主鍵的話，複製就會快了很多；
複製以下幾種語句時的行鎖更少：
* INSERT … SELECT
* 包含 AUTO_INCREMENT 欄位的 INSERT
* 沒有附帶條件或者並沒有修改很多記錄的 UPDATE 或 DELETE 語句 執行 INSERT，UPDATE，DELETE 語句時鎖更少；
 從伺服器上採用多線程來執行複製成為可能；
缺點：row level下，所有的執行的語句當記錄到日誌中的時候，都將以每行記錄的修改來記錄，這樣可能會產生大量的日誌內容，比如有這樣一條update語句：update product set owner_member_id = ‘b’ where owner_member_id = ‘a’，執行之後，日誌中記錄的不是這條update語句所對應額事件(MySQL以事件的形式來記錄bin-log日誌)，而是這條語句所更新的每一條記錄的變化情況，這樣就記錄成很多條記錄被更新的很多個事件。自然，bin-log日誌的量就會很大。尤其是當執行alter table之類的語句的時候，產生的日誌量是驚人的。因為MySQL對於alter table之類的表結構變更語句的處理方式是整個表的每一條記錄都需要變動，實際上就是重建了整個表。那麼該表的每一條記錄都會被記錄到日誌中。
二、Statement Level
每一條會修改資料的sql都會記錄到 master的bin-log中。slave在複製的時候sql進程會解析成和原來master端執行過的相同的sql來再次執行。
優點：statement level下的優點首先就是解決了row level下的缺點，不需要記錄每一行資料的變化，減少bin-log日誌量，節約IO，提高效能。因為他只需要記錄在Master上所執行的語句的細節，以及執行語句時候的內容相關的資訊。
缺點：由於他是記錄的執行語句，所以，為了讓這些語句在slave端也能正確執行，那麼他還必須記錄每條語句在執行的時候的一些相關資訊，也就是上下文資訊，以保證所有語句在slave端杯執行的時候能夠得到和在master端執行時候相同的結果。另外就是，由於MySQL現在發展比較快，很多的新功能不斷的加入，使MySQL得複製遇到了不小的挑戰，自然複製的時候涉及到越複雜的內容，bug也就越容易出現。在statement level下，目前已經發現的就有不少情況會造成MySQL的複製出現問題，主要是修改資料的時候使用了某些特定的函數或者功能的時候會出現，比如：sleep()函數在有些版本中就不能真確複製，在預存程序中使用了last_insert_id()函數，可能會使slave和master上得到不一致的id等等。由於row level是基於每一行來記錄的變化，所以不會出現類似的問題。
三、Mixed Level
實際上就是前兩種模式的結合。在Mixed模式下，MySQL會根據執行的每一條具體的sql語句來區分對待記錄的日誌形式，也就是在Statement和Row之間選擇一種。新版本中的Statment level還是和以前一樣，僅僅記錄執行的語句。而新版本的MySQL中隊row level模式也被做了最佳化，並不是所有的修改都會以row level來記錄，像遇到表結構變更的時候就會以statement模式來記錄，如果sql語句確實就是update或者delete等修改資料的語句，那麼還是會記錄所有行的變更。
四、在設定檔中參數
log-bin=mysql-bin
#binlog_format=”STATEMENT”
#binlog_format=”ROW” binlog_format=”MIXED” 運行時線上修改：
mysql> SET SESSION binlog_format = ‘STATEMENT’;
mysql> SET SESSION binlog_format = ‘ROW’;
mysql> SET SESSION binlog_format = ‘MIXED’;
mysql> SET GLOBAL binlog_format = ‘STATEMENT’;
mysql> SET GLOBAL binlog_format = ‘ROW’;
mysql> SET GLOBAL binlog_format = ‘MIXED’;
五、刪除&關閉binlog日誌
1、關閉binlog
#修改設定檔，注釋下面的幾行就OK
[root@LookBack ~]# cat /etc/my.cnf | grep -E 'log[_-]bin|binlog_format|expire_logs_days' #這是查詢語句
log_bin = mysql-bin #是否開啟了二進位日誌
binlog_format = mixed #二進位日誌記錄的模式
expire_logs_days = 30 #二進位日誌自動刪除的天數。預設值為0,表示"沒有自動刪除"
2、正確刪除binlog
PURGE MASTER LOGS TO 'MySQL-bin.010'; #清除MySQL-bin.010日誌
PURGE MASTER LOGS BEFORE '2015-07-19 13:50:42'; #清除2015-07-19 13:50:42前binlog日誌
PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 3 DAY); #清除3天前binlog日誌BEFORE，變數的date自變數可以為'YYYY-MM-DD hh:mm:ss'格式