針對mysql不同binlog模式的一些測試

賀子_DBA時代發表於2017-08-22
MySql模式
我們都知道，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模式。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29654823/viewspace-2143957/，如需轉載，請註明出處，否則將追究法律責任。

相關文章