MySQL/MariaDB的binlog二進制日志模式及刪除方法

binlog二進制如果要刪除的話我們需要一點小技巧了，　同時MySQL/MariaDB的binlog二進制日志模式有好幾種了，今天我們一起來看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'格式