MySQL——binlog

一、binlog簡介：

1、什麼是binlog：

   binlog日志用于記錄所有更新了資料或者已經潛在更新了資料（例如，沒有比對任何行的一個DELETE）的所有語句。語句以“事件”的形式儲存，它描述資料更改。

2、binlog的記錄格式：

Mysql binlog日志有三種格式，分别為：Statement ,MiXED ,和ROW；

  （在MySQL5.7.7版本之後，把binlog_format的預設值修改成了ROW。master将修改表的event寫入binlog中，并且master将binlog資訊發送到slave，slave重放binlog中event。基于ROW格式複制是最安全的複制，slave需要的行鎖更少；但是也有一些缺點，那就是基于ROW格式的複制，binlog會記錄更多的資料。并且無法在slave上看到從master上擷取的語句，因為都是event。但是在ROW格式下，可以開啟binlog_rows_query_log_events參數，這會讓binlog在記錄events同時，也記錄原始的sql語句，以友善後續的查詢；）

①：Statement：每一條會修改資料的sql都會記錄在binlog中。

優點：不需要記錄每一行的變化，減少了binlog日志量，節約了IO，提高性能。(相比row能節約多少性能與日志量，這個取決于應用的SQL情況，正常同一條記錄修改或者插入row格式所産生的日志量還小于Statement産生的日志量，但是考慮到如果帶條件的update操作，以及整表删除，alter表等操作，ROW格式會産生大量日志，是以在考慮是否使用ROW格式日志時應該跟據應用的實際情況，其所産生的日志量會增加多少，以及帶來的IO性能問題。)

缺點：由于記錄的隻是執行語句，為了這些語句能在slave上正确運作，是以還必須記錄每條語句在執行的時候的一些相關資訊，以保證所有語句能在slave得到和在master端執行時候相同 的結果。另外mysql 的複制,像一些特定函數功能，slave可與master上要保持一緻會有很多相關問題(如sleep()函數， last_insert_id()，以及user-defined functions(udf)會出現問題).

使用以下函數的語句也無法被複制：

* LOAD_FILE()

* UUID()

* USER()

* FOUND_ROWS()

* SYSDATE() (除非啟動時啟用了 --sysdate-is-now 選項)

------同時在INSERT ...SELECT 會産生比 RBR 更多的行級鎖

②：Row: 不記錄sql語句上下文相關資訊，僅儲存哪條記錄被修改。

優點： binlog中可以不記錄執行的sql語句的上下文相關的資訊，僅需要記錄那一條記錄被修改成什麼了。是以row level的日志内容會非常清楚的記錄下每一行資料修改的細節。而且不會出現某些特定情況下的存儲過程，或function，以及trigger的調用和觸發無法被正确複制的問題

缺點: 所有的執行的語句當記錄到日志中的時候，都将以每行記錄的修改來記錄，這樣可能會産生大量的日志内容,比如一條update語句，修改多條記錄，則binlog中每一條修改都會有記錄，這樣造成binlog日志量會很大，特别是當執行alter table之類的語句的時候，由于表結構修改，每條記錄都發生改變，那麼該表每一條記錄都會記錄到日志中。

③：Mixedlevel: 是以上兩種level的混合使用，

  一般的語句修改使用statment格式儲存binlog，如一些函數，statement無法完成主從複制的操作，則采用row格式儲存binlog,MySQL會根據執行的每一條具體的sql語句來區分對待記錄的日志形式，也就是在Statement和Row之間選擇一種.新版本的MySQL中隊row level模式也被做了優化，并不是所有的修改都會以row level來記錄，像遇到表結構變更的時候就會以statement模式來記錄。至于update或者delete等修改資料的語句，還是會記錄所有行的變更。

二、Binlog基本配制與格式設定

1、基本配制

Mysql BInlog日志格式可以通過mysql的my.cnf檔案的屬性binlog_format指定。如以下：

<code>binlog_format = ROW             ---binlog日志格式</code>

<code>log_bin =</code><code>/mysql/mysql-bin</code><code>.log        ---binlog日志名</code>

<code>expire_logs_days = 7            ---binlog過期清理時間</code>

<code>max_binlog_size 100m            ---binlog每個日志檔案大小</code>

2.Binlog日志格式選擇

Mysql預設是使用Statement日志格式，推薦使用ROW.

  由于一些特殊使用，可以考慮使用ROWED，如自己通過binlog日志來同步資料的修改，這樣會節省很多相關操作。對于binlog資料處理會變得非常輕松,相對mixed，解析也會很輕松(當然前提是增加的日志量所帶來的IO開銷在容忍的範圍内即可)。

3.mysqlbinlog格式選擇

mysql對于日志格式的標明原則:如果是采用 INSERT，UPDATE，DELETE 等直接操作表的情況，則日志格式根據 binlog_format 的設定而記錄,如果是采用 GRANT，REVOKE，SET PASSWORD 等管理語句來做的話，那麼無論如何 都采用 SBR 模式記錄。

三、binlog的相關參數：

<code>--log_bin：</code>

<code>設定此參數表示啟用binlog功能，并指定路徑名稱</code>

<code>--log_bin_index</code>

<code>設定此參數是指定二進制索引檔案的路徑與名稱</code>

<code>--binlog_do_db</code>

<code>此參數表示隻記錄指定資料庫的二進制日志</code>

<code>--binlog_ignore_db</code>

<code>此參數表示不記錄指定的資料庫的二進制日志</code>

<code>--max_binlog_cache_size</code>

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

<code>--binlog_cache_size</code>

<code>此參數表示binlog使用的記憶體大小，可以通過狀态變量binlog_cache_use和binlog_cache_disk_use來幫助測試。</code>

<code>    </code><code>binlog_cache_use：使用二進制日志緩存的事務數量</code>

<code>    </code><code>binlog_cache_disk_use:使用二進制日志緩存但超過binlog_cache_size值并使用臨時檔案來儲存事務中的語句的事務數量</code>

<code>--max_binlog_size</code>

<code>Binlog最大值，最大和預設值是1GB，該設定并不能嚴格控制Binlog的大小，尤其是Binlog比較靠近最大值而又遇到一個比較大事務時，為了保證事務的完整性，不可能做切換日志的動作，隻能将該事務的所有SQL都記錄進目前日志，直到事務結束</code>

<code>--sync_binlog</code>

<code>這個參數直接影響mysql的性能和完整性</code>

<code>--sync_binlog=0：</code>

<code>當事務送出後，Mysql僅僅是将binlog_cache中的資料寫入Binlog檔案，但不執行fsync之類的磁盤        同步指令通知檔案系統将緩存重新整理到磁盤，而讓Filesystem自行決定什麼時候來做同步，這個是性能最好的。</code>

<code>--sync_binlog=n，在進行n次事務送出以後，Mysql将執行一次fsync之類的磁盤同步指令，同志檔案系統将Binlog檔案緩存重新整理到磁盤。</code>

<code>注意：Mysql中預設的設定是sync_binlog=0，即不作任何強制性的磁盤重新整理指令，這時性能是最好的，但風險也是最大的。一旦系統繃Crash，在檔案系統緩存中的所有Binlog資訊都會丢失</code>

四、binlog的删除：

  binlog的删除可以手工删除或自動删除

1、自動删除binlog

<code>通過binlog參數（expire_logs_days ）來實作mysql自動删除binlog</code>

<code>mysql&gt; show binary logs;</code>

<code>mysql&gt; show variables like </code><code>'expire_logs_days'</code><code>;</code>

<code>mysql&gt; </code><code>set</code> <code>global expire_logs_days=3;</code>

2、手工删除binlog

<code>mysql&gt; reset master;       ----删除master的binlog</code>

<code>mysql&gt; reset slave;        ----删除slave的中繼日志</code>

<code>mysql&gt; purge master logs before </code><code>'2017-03-30 17:20:00'</code><code>;   -----删除指定日期以前的日志索引中binlog日志檔案</code>

<code>mysql&gt; purge master logs to </code><code>'binlog.000002'</code><code>;     ----删除指定日志檔案的日志索引中binlog日志檔案或者直接用作業系統指令直接删除</code>

<code>mysql&gt; </code><code>set</code> <code>sql_log_bin=1</code><code>/0</code><code>;    ----如果使用者有super權限，可以啟用或禁用目前會話的binlog記錄</code>

<code>mysql&gt; show master logs;     ---檢視master的binlog日志 </code>

<code>mysql&gt; show binary logs;     ---檢視master的binlog日志</code>

<code>mysql&gt; show master status;   ---用于提供master二進制日志檔案的狀态資訊</code>

<code>mysql&gt; show slave hosts;     ---顯示目前注冊的slave的清單。不以--report-host=slave_name選項為開頭的slave不會顯示在本清單中</code>

3、binglog的檢視

通過mysqlbinlog指令可以檢視binlog的内容

<code>[root@localhost ~]</code><code># mysqlbinlog  /home/mysql/binlog/binlog.000003  | more</code>

<code>/*!40019 SET @@session.max_insert_delayed_threads=0*/;</code>

<code>/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;</code>

<code>DELIMITER /*!*/;</code>

<code># at 4</code>

<code>#120330 16:51:46 server id 1  end_log_pos 98    Start: binlog v 4, server v 5.0.45-log created 120330 1</code>

<code>6:51:46</code>

<code># Warning: this binlog was not closed properly. Most probably mysqld crashed writing it.</code>

<code># at 196</code>

<code>#120330 17:54:15 server id 1  end_log_pos 294   Query   thread_id=3     exec_time=2     error_code=0</code>

<code>SET TIMESTAMP=1333101255/*!*/;</code>

<code>insert into tt7 </code><code>select</code> <code>* from tt7/*!*/;</code>

<code># at 294</code>

<code>#120330 17:54:46 server id 1  end_log_pos 388   Query   thread_id=3     exec_time=28    error_code=0</code>

<code>SET TIMESTAMP=1333101286/*!*/;</code>

<code>alter table tt7 engine=innodb/*!*/;</code>

3.1、解析binlog格式

--位置

位于檔案中的位置，“at 196”說明“事件”的起點，是以第196位元組開始；“end_log_pos 294”說明以第294位元組結束

--時間戳

事件發生的時間戳：“120330 17:54:46”

--事件執行時間

事件執行花費的時間:"exec_time=28"

--錯誤碼

錯誤碼為：“error_code=0”

--伺服器的辨別

伺服器的辨別id：“server id 1”

注意：

1、binlog事件中的時間戳是從語句那裡繼承過來的，一條語句産生多個事件，那這些事件的時間戳都是一樣的，而且都是和第一個事件一緻的；

2、有時候我們會在binlog中發現執行語句和送出時間不一緻的情況，是因為有些事務是自動送出的，在這個事務中隻有一條語句執行了一定的時間；

innodb中rowid對binlog的影響：

1、在galera cluster上面最好不要讓一個事務更新太多的資料，可以适當控制在1萬行以内，都是麼有問題的，因為galera cluster的驗證和送出都是串行    的，一個事務太大，會導緻叢集其他事務都等待這個事務完成造成叢集假死現象；

2、在innodb中如果沒有指定主鍵，就會建立一個rowid，但是MySQL的binlog是server層，而innodb中的rowid是存儲引擎的東西，server層根本感覺不到      rowid的存在；

3、rowid是innodb自己定義的一個列，隻有在表中沒有定義主鍵的時候，系統才會給這個表加上這一列，但這一列隻是為了存儲，構成一個聚簇索引，但不    會暴露給邏輯層，并且上層也用不到它，是以直接忽略它即可；

4、在MySQL資料庫的使用中，一定要定義主鍵，如果沒有主鍵，并且是row模式的複制，就必然會造成這樣的問題，而不像其他資料庫一樣，如果沒有主鍵，    還可以使用rowid來操作表；

5、在galera cluster中，更要定義主鍵。如果沒有定義，必然會造成故障，這不是galera cluster的問題，隻是它會把這個問題放大而已；

本文轉自一個笨小孩51CTO部落格，原文連結http://blog.51cto.com/fengfeng688/1951160： ，如需轉載請自行聯系原作者