MySQL複製模式的全面剖析

               MySQL Replication  Format

1：MySQL支援的複製的模式：

2：三種模式的簡單定義：

  基於語句的複製：就是從庫（slave）基於產生變化的SQL語句從主庫(master)進行復制。在MySQL5.1.4版本之前是binlog和複製唯一支援的模式，也是MySQL5.5中預設的格式。

  基於行的複製：基於行的複製不復制SQL語句，而是將插入，刪除或更新操作的各行進行復制。master的binlog記錄的是各個表中行的變化。

  基於混合模式的複製：它是根據事件的型別實時的改變binlog的格式。當設定為混合模式時，預設為基於語句的格式，但在特定的情況下它會自動的轉變為基於行的模式。

3：基於語句的複製和基於行的複製的優缺點：

  1）：基於語句的複製的優點

  .久經考驗，或者說是很成熟的技術，從MySQL3.23版本已經開始支援。

  .更少的資料需要寫入binlog檔案中；當update，insert或者delete影響很多行時，它會佔用很少的儲存空間，同樣也意味著當從備份中恢復會更快。

  .由於binlog檔案記錄了所有可能導致變化的語句，所以可以用它來審計資料庫。

  2）：基於語句的複製的缺點

    .基於語句的複製的語句，資料是不安全的。不是所有可能修改資料的語句都可以通過基於語句的複製而進行安全，無誤差的複製。一些不確定的因素或者說行為導致很難通過基於語句進行復制。例如，一些DML語句：

    .如果語句含有UDF的呼叫或stored programs(stored procedures and functions,triggers and events)的呼叫，則無法保證slave上使用的是相同的值。由於返回的值不能保證僅僅是跟函式或儲存程式的引數有關係。關於此部分請參考：http://dev.mysql.com/doc/refman/5.5/en/replication-features-invoked.html

    .如果update,delete或insert語句包含limit從句，則執行期間的資料庫崩潰可能帶來問題。由於受影響行的順序沒有定義。

    .如果語句含有任何不確定的函式呼叫，則可能會導致Master與Slave之間產生不一致的情況。這些函式有：

    LOAD_FILE(filename)  :讀取檔案，以字串的形式返回值。

    UUID()             :生成唯一值的函式,128位。

    UUID_SHORT()        :同UUID(),64位。

    USER()             :以utf8字符集返回當前連線MySQL的使用者名稱及主機名。

    FOUND_ROWS()        :返回一個包含limit字句獲得的查詢總數。

    SYSDATE()          :返回動態執行的時間。

    GET_LOCK(str,timeout) :獲得一個以字串命名的鎖，且有超時時間。

    IS_USED_LOCK(str)    :檢測str命名的鎖是否在使用中。

    IS_FREE_LOCK(str)    :檢測str命名的鎖是否沒在使用。

    MASTER_POS_WAIT()    :控制master/slave的同步。

    RAND()             :實現隨機數的方法。

    RELEASE_LOCK()       :用來釋放因GET_LOCK()獲取鎖的str。

    SLEEP()            :實現延遲動作的時間的方法。

    VERSION()           :返回MySQL版本資訊的函式。

特別說明：

關於sysdate(),如果master,salve啟動時加上–sysdate-is-now 引數，就不會影響複製了。

    .Insert ….. Select語句當其需要大量的行級鎖時。

    .Update語句當其進行全表掃描時(由於沒有索引或者合適的索引)。

    .For InnoDB,一個帶有AUTO_INCREMENT的Insert語句。

    .對於很複雜的語句，當在Slave上執行前還需進行解析，優化。而基於行模式，僅僅是修改影響的行

    .Master,Slave表結構必須幾乎一樣。

    .如果在從庫上執行復雜的語句出現了錯誤，很可能將影響到Slave上改變的行數。

   3）：基於行復制的優點

    .所有的改變都可複製，這是最安全的複製模式。

    .基於行復制的實現的技術和其它大多數的資料庫是一樣的，瞭解了其它的資料庫系統，就相當於瞭解了它的實現。

    .在Master上需要的行鎖會很少，從而獲得更高的併發性，如下幾種語句：

      . Insert …… Select

      . 帶有AUTO_INCREMENT欄位的Insert。

      . 帶有Where子句且沒有使用索引或改變很少滿足條件的Update或delete語句。

    .Slave上執行Insert,Update,Delete語句時鎖更少了。

   4）：基於行復制的缺點

    .binlog需記錄更多的資料，意味著從備份中回滾時需要更多的時間；此外如果遇到改變很多行時，所有改變都會寫入binlog中，而基於語句的複製只會寫一次，這會導致頻繁發生binlog的併發寫問題

    .UDFs產生的大的BLOB值會導致複製變慢。

    .不能夠從binlog中看到執行過具體那些語句，同樣也不能夠檢視Slave正在執行那些從Master上得到的語句。

    .對MyISAM表併發插入時，不支援基於行的模式。

特別說明：

  基於行復制，如果想看binlog那些資料改變了，使用mysqlbinlog加上–base64-output=DECODE-ROWS 和–verbose引數。

4：基於語句，基於行的複製的選擇性

  .語句是否更新大量的行，還是通常只改變或插入少量行？

   如果語句改變大量的行，基於語句的複製執行更快。但是由於語句也在Slave上執行，所以並不總是這

   樣。如果語句的優化和執行計劃很複雜，這時可能基於行的複製，因為尋找行的邏輯快的多。

   如果語句只改變或插入少量行，則基於行的複製更快，因為不需要解析，所有的處理都直接交給儲存

   引擎。

  .是否需要知道執行了那些語句?至少可以說，基於行的複製的事件處理很難解碼。而基於語句的複製，

   被寫入二進位制日誌中，因此可以直接讀取。

  .基於語句的複製的複製模型很簡單：只要在Slave上執行相同的語句即可。這種技術應用已久，很多

   DBA對其熟悉。而基於行的複製相對較新，如果複製過程出現故障，可能難以解決。

  .如果Master和Slave上資料不同，執行語句的結果也會不同。有時是故意的(這時應該使用基於語句的

   複製)，但有時是無意的，可以通過基於行的複製來避免這一情況。

5：基於混合模式的複製

   混合模式複製背後的原理很簡單：正常情況下使用基於語句的複製，對於不安全的語句切換為基於行的複製。當出現以下情況時，混合模式需要切換到基於行的複製：

   .該語句呼叫了：

    UUID函式；

    使用者自定義函式(UDFs);

    CURRENT_USER 或USER函式；

    LOAD_FILE函式。

   .同一個語句更改了兩張或更多包含AUTO_INCREMENT列的表。

   .語句中使用了伺服器變數。

   .儲存引擎不允許使用基於語句複製，如：MySQL Cluster引擎。

6：關於binlog_format的簡介

特別注意:

   你可以在一個正在執行MySQL例項改變binlog的格式，但是並不推薦你在一個正在執行的主從框架中修改它的值。因為主庫的修改，並不能影響從庫的日誌格式，只有從庫自己才可以改變自己的日誌格式。

本文轉自 kuchuli 51CTO部落格，原文連結：http://blog.51cto.com/lgdvsehome/1226339，如需轉載請自行聯絡原作者