mysql binlog 使用指南

標籤：mysql   二進位   日誌   binlog   

MySQL binlog 詳解

1.前言
     日誌是把資料庫的每一個變化都記載到一個專用的檔案裡，這種檔案就叫做記錄檔。Mysql預設只開啟出錯日誌，因為過多的日誌將會影響系統的處理效能。
  在5.0前支援文字格式設定和二進位格式，5.0後只支援二進位格式，因為二進位日誌在效能、資訊處理方面有更多的優點。

2.基礎知識

  2.1、二進位日誌的啟用
    二進位日誌由設定檔的log-bin選項負責啟用，Mysql伺服器將在資料根目錄建立兩個新檔案XXX-bin.001和XXX-bin.index，若配置選項沒有給出檔案名稱，Mysql將使用主機名稱命名這兩個檔案，其中.index檔案包含一份全體記錄檔的清單。
    Mysql會把使用者對所有資料庫的內容和結構的修改情況記入XXX-bin.n檔案，而不會記錄SELECT和沒有實際
 2.2、更新的UPDATE語句。
  記錄檔的擴充
    當停止或重啟時，伺服器會把記錄檔記入下一個記錄檔，Mysql會在重啟時產生一個新的記錄檔，檔案序號遞增，此外，如果記錄檔超過max_binlog_size系統變數配置的上限時，也會產生新的記錄檔。
  2.3、記錄檔的查看
    Mysql提供了mysqlbinlog命令來查看記錄檔，如mysqlbinlog xxx-bin.001 | more。在記錄每條變更日誌的時候，記錄檔都會把目前時間給記錄下來，以便進行資料庫恢複。    
  2.4、記錄檔的停用
    可以使用SET SQL_LOG_BIN=0命令停止使用記錄檔，然後可以通過SET SQL_LOG_BIN=1命令來啟用。
  2.5、使用日誌進行資料庫恢複
    如果遇到災難事件，應該用最近一次製作的完整備份恢複資料庫，然後使用備份之後的日誌
  檔案把資料庫恢複到最接近現在的可用狀態。
    使用日誌進行恢複時需要依次進行，即最早產生的記錄檔要最先恢複：
      mysqlbinlog xxx-bin.00001 | mysql -u root -p
      mysqlbinlog xxx-bin.00002 | mysql -u root -p

3.日誌跟換策略
  使用索引來迴圈檔案，在以下條件將迴圈至下一個索引

 a. 伺服器重啟
  b.伺服器被更新
  c.日誌達到了最大日誌長度max_binlog_size ，一般設定檔中指定
  d.日誌被重新整理mysql> flush logs;    手動重新整理

4.日誌格式
  從官網文檔中看到，之前的MySQL一直都只有基於statement的複製模式，直到5.1.5版本的MySQL才開始支援row level的複製。從5.0開始，MySQL的複製已經解決了大量老版本中出現的無法正確複製的問題。但是由於預存程序的出現，給MySQL Replication複製又帶來了更大的新挑戰。另外，看到官方文檔說，從5.1.8版本開始，MySQL提供了除Statement Level和Row Level之外的第三種複製模式：Mixed，實際上就前兩種模式的結合。在Mixed模式下，MySQL會根據執行的每一條具體的sql語句來區分對待記錄的日誌形式，也就是在Statement和Row之間選擇一種。新版本中的Statement Level還是和以前一樣，僅僅記錄執行的語句。而新版本的MySQL中對row level模式也被做了最佳化，並不是所有的修改都會以row level來記錄，像遇到表結構變更的時候就會以statement模式來記錄，如果sql語句確實就是update或者delete等修改資料的語句，那麼還是會記錄所有行的變更。

  --基於SQL語句的複製(statement-based replication,SBR)，
  --基於行的複製(row-based replication,RBR)，
  --混合模式複製(mixed-based replication,MBR)。

  三種格式的優缺點請參考：

 http://douya.blog.51cto.com/6173221/1605114 

 靜態設定binlog格式：--永久生效

  log-bin = mysql-bin  #binlog_format = "STATEMENT"  #binlog_format = "ROW"  binlog_format = "MIXED"

  動態修改binlog格式： --重啟失效

  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‘;

5.binary log相關變數和參數

  5.1、命令列參數

  --log-bin [=file_name]

  設定此參數表示啟用binlog功能，並制定路徑名稱，名稱不寫的話預設是主機名稱

  --log-bin-index[=file]

  設定此參數是指定二進位索引檔案的路徑與名稱，預設在datadir ，可以不再設定檔中配置

  --max_binlog_size  推薦500M 

 Binlog最大值，最大和預設值是1GB，當binlog日誌達到這個最大值時候，將會被自動更新出一個新的日誌。

該設定並不能嚴格控制Binlog的大小，尤其是Binlog比較靠近最大值而又遇到一個比較大事務時，

為了保證事務的完整性，不可能做切換日誌的動作，只能將該事務的所有SQL都記錄進當前日誌，直到事務結束。

  --binlog-do-db=db_name

  此參數表示只記錄指定資料庫的二進位日誌

  --binlog-ignore-db=db_name

  此參數表示不記錄指定的資料庫的二進位日誌

      -- expire_logs_days  =N             推薦一般保留7天

        此參數表示保留N天的binlog，超過這個時間將被自動刪除  推薦使用此參數清除到期的日誌 ，而不是手動

  5.2、系統變數

  log_bin

  binlog_cache_size

  此參數表示binlog使用的記憶體大小，可以通過狀態變數binlog_cache_use和binlog_cache_disk_use來協助測試。

  max_binlog_cache_size

  此參數表示binlog使用的記憶體最大的尺寸

  binlog_cache_use

  使用二進位日誌緩衝的事務數量

  binlog_cache_disk_use

  使用二進位日誌緩衝但超過binlog_cache_size值並使用臨時檔案來儲存事務中的語句的事務數量。

  sync_binlog

  這個參數直接影響mysql的效能和完整性。

  sync_binlog=0：   推薦使用預設的參數 0 

  當事務提交後，Mysql僅僅是將binlog_cache中的資料寫入binlog檔案，但不執行fsync之類的磁碟，同步指令通知檔案系統將緩衝重新整理到磁碟，而讓Filesystem自行決定什麼時候來做同步，這個是效能最好的。

  sync_binlog=n，在進行n次事務提交以後，Mysql將執行一次fsync之類的磁碟同步指令，通知檔案系統將Binlog檔案快取重新整理到磁碟。

  Mysql中預設的設定是sync_binlog=0，即不做任何強制性的磁碟排清指令，這時效能是最好的，但風險也是最大的。一旦系統Crash，在檔案系統快取中的所有Binlog資訊都會丟失。

  

6.常見問題

   6.1執行個體：

> PURGE MASTER LOGS TO ‘MySQL-bin.010‘;  //清除MySQL-bin.010日誌> PURGE MASTER LOGS BEFORE ‘2008-06-22 13:00:00‘;   //清除2008-06-22 13:00:00前binlog日誌> PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 3 DAY);  //清除3天前binlog日誌BEFORE，變數的date自變數可以為‘YYYY-MM-DD hh:mm:ss‘格式。

 
      6.2.清除binlog時，對從mysql的影響
       如果您有一個活性的從屬伺服器，該伺服器當前正在讀取您正在試圖刪除的日誌之一，則本語句不會起作用，而是會失敗，並伴隨一個錯誤。不過，如果從屬伺服器是休止的，並且您碰巧清理了其想要讀取的日誌之一，則從屬伺服器啟動後不能複製。當從屬伺服器正在複製時，本語句可以安全運行。您不需要停止它們。

  --或使用命令：
  RESET MASTER   謹慎操作

  刪除之前所有的binlog，並重建新的binlog，尾碼從000001開始。

  註：如果您有一個活性的從屬伺服器，該伺服器當前正在讀取您正在試圖刪除的日誌之一，則本語句不會起作用，而是失敗，並伴隨一個錯誤。

  不過，如果從屬伺服器是休止的，並且您碰巧清理了其想要讀取的日誌之一，則從屬伺服器啟動後不能複製。

  當從屬伺服器正在複製時，本語句可以安全運行。您不需要停止它們。

 

  6.3、二進位日誌不準確的處理

   預設情況下，並不是每次寫入時都將二進位日誌與硬碟同步。因此如果作業系統或機器(不僅僅是MySQL伺服器)崩潰，有可能二進位日誌中最後的語句丟失。 要想防止這種情況，你可以使用sync_binlog全域變數(1是最安全的值，但也是最慢的)，使二進位日誌在每N次二進位日誌寫入後與硬碟同步。 即使sync_binlog設定為1,出現崩潰時，也有可能表內容和二進位日誌內容之間存在不一致性。

   如果崩潰恢複時MySQL伺服器發現二進位日誌變短了(即至少缺少一個成功提交的InnoDB事務)， 如果sync_binlog =1並且硬碟/檔案系統的確能根據需要進行同步(有些不需要)則不會發生，則輸出錯誤訊息 (“二進位日誌<名>比期望的要小”)。 在這種情況下，二進位日誌不準確，複製應從主伺服器的資料快照開始。   

 

一般線上環境中，MySQL binlog可以使用以下設定檔

#binloglog-bin = $dir/mysql-binbinlog_format = "STATEMENT"max_binlog_size = 500Mbinlog_cache_size = 64Mexpire_logs_days  = 7 sync_binlog=0：

本文出自 “crazy_sir” 部落格，請務必保留此出處http://douya.blog.51cto.com/6173221/1788730

mysql binlog 使用指南