mysql復(fù)制主要有三種方式:基于SQL語(yǔ)句的復(fù)制(statement-based replication, SBR)，基于行的復(fù)制(row-based replication, RBR)，混合模式復(fù)制(mixed-based replication,MBR),對(duì)應(yīng)的,binlog的格式也有三種:STATEMENT,ROW,MIXED.

① STATEMENT模式（SBR）

每一條會(huì)修改數(shù)據(jù)的sql語(yǔ)句會(huì)記錄到binlog中。優(yōu)點(diǎn)是并不需要記錄每一條sql語(yǔ)句和每一行的數(shù)據(jù)變化，減少了binlog日志量，節(jié)約IO，提高性能。缺點(diǎn)是在某些情況下會(huì)導(dǎo)致master-slave中的數(shù)據(jù)不一致(如sleep()函數(shù)， last_insert_id()，以及user-defined functions(udf)等會(huì)出現(xiàn)問(wèn)題)

② ROW模式（RBR）

不記錄每條sql語(yǔ)句的上下文信息，僅需記錄哪條數(shù)據(jù)被修改了，修改成什么樣了。而且不會(huì)出現(xiàn)某些特定情況下的存儲(chǔ)過(guò)程、或function、或trigger的調(diào)用和觸發(fā)無(wú)法被正確復(fù)制的問(wèn)題。缺點(diǎn)是會(huì)產(chǎn)生大量的日志，尤其是alter table的時(shí)候會(huì)讓日志暴漲。

③ MIXED模式（MBR）

以上兩種模式的混合使用，一般的復(fù)制使用STATEMENT模式保存binlog,對(duì)于STATEMENT模式無(wú)法復(fù)制的操作使用ROW模式保存binlog,MySQL會(huì)根據(jù)執(zhí)行的SQL語(yǔ)句選擇日志保存方式。

binlog復(fù)制配置,在mysql的配置文件my.cnf中,可以通過(guò)一下選項(xiàng)配置binglog相關(guān),代碼如下:

1. binlog_format = MIXED  //binlog日志格式，mysql默認(rèn)采用statement，建議使用mixed 
2. log-bin = /data/mysql/mysql-bin.log    //binlog日志文件 
3. expire_logs_days   = 7       //binlog過(guò)期清理時(shí)間 
4. max_binlog_size = 100m      //binlog每個(gè)日志文件大小 
5. binlog_cache_size  = 4m   //binlog緩存大小 Vevb.com
6. max_binlog_cache_size  = 512m   //最大binlog緩存大小 

三 MIXED說(shuō)明

對(duì)于執(zhí)行的SQL語(yǔ)句中包含now()這樣的時(shí)間函數(shù),會(huì)在日志中產(chǎn)生對(duì)應(yīng)的unix_timestamp()*1000的時(shí)間字符串,slave在完成同步時(shí),取用的是sqlEvent發(fā)生的時(shí)間來(lái)保證數(shù)據(jù)的準(zhǔn)確性,另外對(duì)于一些功能性函數(shù)slave能完成相應(yīng)的數(shù)據(jù)同步,而對(duì)于上面指定的一些類似于UDF函數(shù),導(dǎo)致Slave無(wú)法知曉的情況,則會(huì)采用ROW格式存儲(chǔ)這些Binlog,以保證產(chǎn)生的Binlog可以供Slave完成數(shù)據(jù)同步.

現(xiàn)在來(lái)比較以下 SBR 和 RBR 2中模式各自的優(yōu)缺點(diǎn).

SBR 的優(yōu)點(diǎn):

歷史悠久,技術(shù)成熟,binlog文件較小,binlog中包含了所有數(shù)據(jù)庫(kù)更改信息,可以據(jù)此來(lái)審核數(shù)據(jù)庫(kù)的安全等情況,binlog可以用于實(shí)時(shí)的還原,而不僅僅用于復(fù)制,主從版本可以不一樣,從服務(wù)器版本可以比主服務(wù)器版本高.

SBR 的缺點(diǎn):

不是所有的UPDATE語(yǔ)句都能被復(fù)制,尤其是包含不確定操作的時(shí)候,調(diào)用具有不確定因素的 UDF 時(shí)復(fù)制也可能出問(wèn)題,使用以下函數(shù)的語(yǔ)句也無(wú)法被復(fù)制:

1. * LOAD_FILE() 
2. * UUID() 
3. * USER() 
4. * FOUND_ROWS() 
5. * SYSDATE() (除非啟動(dòng)時(shí)啟用了 --sysdate-is-now 選項(xiàng)) 

INSERT ... SELECT 會(huì)產(chǎn)生比 RBR 更多的行級(jí)鎖,復(fù)制需要進(jìn)行全表掃描(WHERE 語(yǔ)句中沒(méi)有使用到索引)的 UPDATE 時(shí),需要比 RBR 請(qǐng)求更多的行級(jí)鎖,對(duì)于有 AUTO_INCREMENT 字段的 InnoDB表而言,INSERT 語(yǔ)句會(huì)阻塞其他 INSERT 語(yǔ)句.

對(duì)于一些復(fù)雜的語(yǔ)句,在從服務(wù)器上的耗資源情況會(huì)更嚴(yán)重,而 RBR 模式下,只會(huì)對(duì)那個(gè)發(fā)生變化的記錄產(chǎn)生影響,存儲(chǔ)函數(shù)(不是存儲(chǔ)過(guò)程)在被調(diào)用的同時(shí)也會(huì)執(zhí)行一次 NOW() 函數(shù)，這個(gè)可以說(shuō)是壞事也可能是好事,確定了的 UDF 也需要在從服務(wù)器上執(zhí)行.

數(shù)據(jù)表必須幾乎和主服務(wù)器保持一致才行,否則可能會(huì)導(dǎo)致復(fù)制出錯(cuò),執(zhí)行復(fù)雜語(yǔ)句如果出錯(cuò)的話,會(huì)消耗更多資源.

RBR 的優(yōu)點(diǎn):

任何情況都可以被復(fù)制,這對(duì)復(fù)制來(lái)說(shuō)是最安全可靠的,和其他大多數(shù)數(shù)據(jù)庫(kù)系統(tǒng)的復(fù)制技術(shù)一樣,多數(shù)情況下,從服務(wù)器上的表如果有主鍵的話,復(fù)制就會(huì)快了很多,復(fù)制以下幾種語(yǔ)句時(shí)的行鎖更少:

* INSERT ... SELECT

* 包含 AUTO_INCREMENT 字段的 INSERT

* 沒(méi)有附帶條件或者并沒(méi)有修改很多記錄的 UPDATE 或 DELETE 語(yǔ)句

執(zhí)行 INSERT，UPDATE，DELETE 語(yǔ)句時(shí)鎖更少,從服務(wù)器上采用多線程來(lái)執(zhí)行復(fù)制成為可能.

RBR 的缺點(diǎn):binlog 大了很多,復(fù)雜的回滾時(shí) binlog 中會(huì)包含大量的數(shù)據(jù),主服務(wù)器上執(zhí)行 UPDATE 語(yǔ)句時(shí),所有發(fā)生變化的記錄都會(huì)寫(xiě)到 binlog 中,而 SBR 只會(huì)寫(xiě)一次,這會(huì)導(dǎo)致頻繁發(fā)生 binlog 的并發(fā)寫(xiě)問(wèn)題.

UDF 產(chǎn)生的大 BLOB 值會(huì)導(dǎo)致復(fù)制變慢,無(wú)法從 binlog 中看到都復(fù)制了寫(xiě)什么語(yǔ)句,當(dāng)在非事務(wù)表上執(zhí)行一段堆積的SQL語(yǔ)句時(shí),最好采用 SBR 模式,否則很容易導(dǎo)致主從服務(wù)器的數(shù)據(jù)不一致情況發(fā)生.

另外,針對(duì)系統(tǒng)庫(kù) mysql 里面的表發(fā)生變化時(shí)的處理規(guī)則如下:如果是采用 INSERT,UPDATE,DELETE 直接操作表的情況,則日志格式根據(jù) binlog_format 的設(shè)定而記錄.

如果是采用 GRANT,REVOKE,SET PASSWORD 等管理語(yǔ)句來(lái)做的話,那么無(wú)論如何都采用 SBR 模式記錄.

注:采用 RBR 模式后,能解決很多原先出現(xiàn)的主鍵重復(fù)問(wèn)題.