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格式：--永久生效

  動态修改binlog格式： --重新開機失效

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執行個體：

      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可以使用以下配置檔案

<code>#binlog</code>

<code>log-bin = $dir/mysql-bin</code>

<code>binlog_format = </code><code>"STATEMENT"</code>

<code>max_binlog_size = 500M</code>

<code>binlog_cache_size = 64M</code>

<code>expire_logs_days  = 7 </code>

<code>sync_binlog=0：</code>

######################基于mysqlbinlog的資料恢複

1,先使用全備進行恢複

<code>mysql -uroot -p &lt;back_up.sql</code>

2,使用mysqlbinlog增量恢複

<code>mysqlbinlog  </code><code>--start-datetime="2015-07-01 06:00:03" --stop-datetime="2015-07-01 14:10:27"</code>

<code>binmysql-bin_.000003 |mysql -uroot -p</code>

      本文轉自crazy_charles 51CTO部落格，原文連結：http://blog.51cto.com/douya/1788730，如需轉載請自行聯系原作者