Mysql的二進制日志binlog的模式說明

binlog模式總共可分為以下三種：row，statement，mixed

1.Row

日志中會記錄成每一行資料被修改的形式，然後在slave端再對相同的資料進行修改，隻記錄要修改的資料，隻有value，不會有sql多表關聯的情況。

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

缺點：在row模式下，所有的執行的語句當記錄到日志中的時候，都将以每行記錄的修改來記錄，這樣可能會産生大量的日志内容。

mysql> insert into username(username) select * from aa;

ERROR 1146 (42S02): Table 'test.username' doesn't exist

mysql> insert into user(username) select * from aa;

Query OK, 1 row affected (0.01 sec)

Records: 1  Duplicates: 0  Warnings: 0

檢視binlog

root@xuebinbin:/vobiledata/mysqllog# mysqlbinlog mysql-bin.000017

BINLOG '

63EfUBNQAAAALgAAAA8CAAAAAA8AAAAAAAEABHRlc3QABHVzZXIAAgIPAi0AAA==

63EfUBdQAAAAJgAAADUCAAAAAA8AAAAAAAEAAv/8BAAFYmFveXU=

'/*!*/;

### INSERT INTO test.user

### SET

###   @1=4 /* SHORTINT meta=0 nullable=0 is_null=0 */

###   @2='baoyu' /* VARSTRING(45) meta=45 nullable=0 is_null=0 */

# at 565

#120806  0:27:39 server id 80  end_log_pos 592     Xid = 20

COMMIT/*!*/;

DELIMITER ;

# End of log file

ROLLBACK /* added by mysqlbinlog */;

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

由此可見，row模式是針對每一行的資料，而于關聯表無關，它把關聯中的相應資料記錄在log中。這樣一來會産生大量的資料。

2.statement

每一條會修改資料的sql都會記錄到master的binlog中，slave在複制的時候sql程序會解析成和原來master端執行多相同的sql再執行。

優點：在statement模式下首先就是解決了row模式的缺點，不需要記錄每一行資料的變化減少了binlog日志量，節省了I/O以及存儲資源，提高性能。因為他隻需要激勵在master上所執行的語句的細節一屆執行語句時候的上下的資訊。

缺點：在statement模式下，由于他是記錄的執行語句，是以，為了讓這些語句在slave端也能正确執行，那麼他還必須記錄每條語句在執行的時候的一些相關資訊，也就是上下文資訊，以保證所有語句在slave端被執行的時候能夠得到和在master端執行時候相同的結果。另外就是，由于mysql現在發展比較快，很多的新功能不斷的加入，使mysql的複制遇到了不小的挑戰，自然複制的時候涉及到越複雜的内容，bug也就越容易出現。在statement中，目前已經發現不少情況會造成Mysql的複制出現問題，主要是修改資料的時候使用了某些特定的函數或者功能的時候會出現，比如：sleep()函數在有些版本中就不能被正确複制，在存儲過程中使用了last_insert_id()函數，可能會使slave和master上得到不一緻的id等等。由于row是基于每一行來記錄的變化，是以不會出現，類似的問題。

mysql> insert into user(username) values('xuebinbin');

ERROR 1598 (HY000): Binary logging not possible. Message: Transaction level 'READ-COMMITTED' in InnoDB is not safe for binlog mode 'STATEMENT'

mysql> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ

    -> ;

Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

root@xuebinbin:/vobiledata/mysqllog# mysqlbinlog mysql-bin.000008

BEGIN

/*!*/;

# at 174

#120806 14:47:35 server id 80  end_log_pos 202     Intvar

SET INSERT_ID=2/*!*/;

# at 202

#120806 14:47:35 server id 80  end_log_pos 311     Query    thread_id=5    exec_time=0    error_code=0

use test/*!*/;

SET TIMESTAMP=1344235655/*!*/;

insert into user(username) values('xuebinbin')

# at 311

#120806 14:47:35 server id 80  end_log_pos 338     Xid = 20

# at 338

#120806 14:53:18 server id 80  end_log_pos 357     Stop

結果發現statement是以sql記錄形式記錄的。這樣的話一個sql就隻記錄一條，減少了大量的資料存儲。

3.Mixed（該模式是STATEMENT和ROW的混合使用。）

早起的MySQL一直都隻有基于statemen 的複制模式，直到5.1.5版本的MySQL才開始支援row 複制。從5.0 開始，MySQL的複制已經解決了大量老版本中出現的無法正确複制的問題。但是由于存儲過程的出現，給 MySQL Replication 又帶來了更大的新挑戰。

從5.1.8 版本開始，MySQL 提供了除 Statement 和 Row 之外的第三種複制模式：Mixed，實際上就是前兩種模式的結合。

在 Mixed 模式下，MySQL 會根據執行的每一條具體的 SQL 語句來區分對待記錄的日志形式，也就是在 statement 和 row 之間選擇一種。

新版本中的 statment 還是和以前一樣，僅僅記錄執行的語句。而新版本的 MySQL 中對 row 模式也被做了優化，并不是所有的修改都會以 row 模式來記錄，比如遇到表結構變更的時候就會以 statement 模式來記錄，如果 SQL 語句确實就是 update 或者 delete 等修改資料的語句，那麼還是會記錄所有行的變更。

***************當你發現自己的才華撐不起野心時，就請安靜下來學習吧***************

本文轉自散盡浮華部落格園部落格，原文連結：http://www.cnblogs.com/kevingrace/p/5569652.html，如需轉載請自行聯系原作者