針對mysql不同binlog模式的示例分析

這篇文章主要為大家展示了“針對mysql不同binlog模式的示例分析”，內容簡而易懂，條理清晰，希望能夠幫助大家解決疑惑，下面讓小編帶領大家一起研究并學習一下“針對mysql不同binlog模式的示例分析”這篇文章吧。

我們都知道，mysql binlog有三種格式：

一：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語句的上下文相關的信息，僅需要記錄那一條記錄被修改成什么了。所以rowlevel的日志內容會非常清楚的記錄下每一行數據修改的細節。而且不會出現某些特定情況下的存儲過程，或function，以及trigger的調用和觸發無法被正確復制的問題

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

三：Mixed模式: 是以上兩種level的混合使用，一般的語句修改使用statment格式保存binlog，對于STATEMENT模式無法復制的操作使用ROW模式保存binlog，MySQL會根據執行的SQL語句選擇日志保存方式。

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

Binlog日志格式選擇：

Mysql默認是使用Statement日志格式，推薦使用MIXED.

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

針對binlog的三種格式而產生相應的主從復制的三種方式：

（１）：基于語句（Statement）的復制：  在主服務器上執行的SQL語句，在從服務器上執行同樣的語句。MySQL默認采用基于語句的復制，效率比較高。

（２）：基于行（row)的復制：把改變的內容復制過去，而不是把命令在從服務器上執行一遍. 從mysql5.0開始支持

（３）：混合類型（mixed)的復制: 默認采用基于語句（Statement）的復制，一旦發現基于語句的無法精確的復制時，就會采用基于行的復制。

如果 binlog 采用了 Mixed 模式，那么在以下幾種情況下會自動將 binlog 的模式由 statement 模式變為 row 模式（摘自網絡）

1）當 DML 語句更新一個NDB存儲引擎的表時；

2）當函數中包含 UUID() 時；

3）2 個及以上字段，并且包含 AUTO_INCREMENT 字段的表被更新時；

4）執行 INSERT DELAYED 語句時；

5）用 UDF（user defined function） 時；

6）視圖中必須要求運用 row 時，例如建立視圖時使用了 UUID() 函數；

實驗1 binlog 采用了Statement模式，導致主從兩邊不一樣。

192.168.0.144：

MariaDB [log]> show variables like 'binlog_format%';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| binlog_format | MIXED |

+---------------+-------+

1 row in set (0.00 sec)

MariaDB [log]> select count(*) from    pvlogs where lastmodify>'2017-07-19' and  lastmodify<'2017-07-20';

+----------+

| count(*) |

+----------+

|  1462803 |

+----------+

1 row in set (3.65 sec)

MariaDB [log]> insert into  pvlogs2  select * from    pvlogs where lastmodify>'2017-07-19' and  lastmodify<'2017-07-20';

Query OK, 1462803 rows affected (31.69 sec)

Records: 1462803  Duplicates: 0  Warnings: 0

MariaDB [log]> select count(*) from  pvlogs2;

+----------+

| count(*) |

+----------+

|  1462803 |

+----------+

1 row in set (0.00 sec)

192.168.0.143：

MariaDB [log]> select count(*) from    pvlogs where lastmodify>'2017-07-19' and  lastmodify<'2017-07-20';

+----------+

| count(*) |

+----------+

|  111848 |

+----------+

1 row in set (3.65 sec)

MariaDB [log]>  select count(*) from  pvlogs2;

+----------+

| count(*) |

+----------+

|   111848 |

+----------+

1 row in set (0.00 sec)

實驗1說明：當主庫binlog采用MIXED 模式的時候，默認會使用Statement模式，由于Statement模式是僅僅是把執行的sql記錄進binlog中，然后傳給從庫，然而由于本身主從兩邊pvlogs表不一樣，進而導致pvlogs2表數據不一樣。

實驗2：因為涉及到了uuid()函數，導致mixed方式自動轉換成row模式，來精確復制。

192.168.0.144：

MariaDB [log]> show variables like 'binlog_format%';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| binlog_format | MIXED |

+---------------+-------+

1 row in set (0.00 sec)

MariaDB [log]>insert into  test_log select uuid() id, member_id,jsession,ip,search_id,info_id,lastmodify,disc,status  from  pvlogs where lastmodify>'2017-07-19' and  lastmodify<'2017-07-20';

MariaDB [log]> select count(*) from  pvlogs2 where lastmodify>'2017-07-19' and  lastmodify<'2017-07-20';

+----------+

| count(*) |

+----------+

|  1462803 |

+----------+

1 row in set (0.00 sec)

MariaDB [log]> select count(*) from  test_log;

+----------+

| count(*) |

+----------+

|  1462803 |

+----------+

1 row in set (1.29 sec)

192.168.0.143：

MariaDB [log]> select count(*) from    pvlogs where lastmodify>'2017-07-19' and  lastmodify<'2017-07-20';

+----------+

| count(*) |

+----------+

|  111848 |

+----------+

1 row in set (3.65 sec)

MariaDB [log]> select count(*) from  test_log;

+----------+

| count(*) |

+----------+

|  1462803 |

+----------+

1 row in set (1.29 sec)

實驗2說明：當主庫binlog采用MIXED 模式的時候，雖然默認會使用Statement模式，但是當遇到uuid()函數的時候，因為Statement模式不能準確復制，所以會自動轉換成使用row模式，這樣把具體的變化寫進binlog，傳給從庫，實現準確復制；

實驗3：自增導致mixed轉換成rowed；

192.168.0.144：

MariaDB [log]> show variables like 'binlog_format%';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| binlog_format | MIXED |

+---------------+-------+

1 row in set (0.00 sec)

MariaDB [log]>  select count(*) from pvlogs_back where lastmodify>'2017-08-20' and  lastmodify<'2017-08-22';

+----------+

| count(*) |

+----------+

|  2638555 |

+----------+

1 row in set (4.24 sec)

MariaDB [log]> insert into  test_log select '' , member_id,jsession,ip,search_id,info_id,lastmodify,disc,status  from  pvlogs_back where lastmodify>'2017-08-20' and  lastmodify<'2017-08-22';

Query OK, 2638555 rows affected, 65535 warnings (1 min 27.18 sec)

Records: 2638555  Duplicates: 0  Warnings: 2638555

MariaDB [log]> select count(*) from  test_log;

+----------+

| count(*) |

+----------+

|  2638555 |

+----------+

1 row in set (0.93 sec)

192.168.0.143：

MariaDB [log]>  select count(*) from pvlogs_back where lastmodify>'2017-08-20' and  lastmodify<'2017-08-22';

+----------+

| count(*) |

+----------+

|  111848 |

+----------+

1 row in set (4.24 sec)

MariaDB [log]> select count(*) from  test_log;

+----------+

| count(*) |

+----------+

|  2638555 |

+----------+

1 row in set (0.93 sec)

實驗3說明：當主庫binlog采用MIXED 模式的時候，雖然默認會使用Statement模式，但2 個及以上字段，并且包含 AUTO_INCREMENT 字段的表被更新時，因為Statement模式不能準確復制，所以會自動轉換成使用row模式，這樣把具體的變化寫進binlog，傳給從庫，實現準確復制；

總結：mysql的binlog三種模式，各有優缺點，row下可能會產生大量的日志內容，但是可以做到任何情況都可以被復制，這對復制來說是最安全可靠的，并且執行 INSERT，UPDATE，DELETE 語句時鎖更少；Statement模式下產生的binlog大部分情況下比較小，但是有的一些情況是不能被準確復制的，推薦使用MIXED模式。

以上是“針對mysql不同binlog模式的示例分析”這篇文章的所有內容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內容對大家有所幫助，如果還想學習更多知識，歡迎關注億速云行業資訊頻道！