binlog的幾種復(fù)制形式

MySQL?5.5?中對(duì)于二進(jìn)制日志?(binlog)?有?3?種不同的格式可選：Mixed,Statement,Row，默認(rèn)格式是?Statement?？偨Y(jié)一下這三種格式日志的優(yōu)缺點(diǎn)。

MySQL?Replication?復(fù)制可以是基于一條語(yǔ)句?(Statement?Level)?，也可以是基于一條記錄?(Row?Level)，可以在?MySQL?的配置參數(shù)中設(shè)定這個(gè)復(fù)制級(jí)別，不同復(fù)制級(jí)別的設(shè)置會(huì)影響到?Master?端的?bin-log?日志格式。

1.?Row

日志中會(huì)記錄成每一行數(shù)據(jù)被修改的形式，然后在?slave?端再對(duì)相同的數(shù)據(jù)進(jìn)行修改。

優(yōu)點(diǎn)：在?row?模式下，bin-log?中可以不記錄執(zhí)行的?SQL?語(yǔ)句的上下文相關(guān)的信息，僅僅只需要記錄哪一條記錄被修改了，修改成什么樣了。所以?row?的日志內(nèi)容會(huì)非常清楚的記錄下每一行數(shù)據(jù)修改的細(xì)節(jié)，非常容易理解。而且不會(huì)出現(xiàn)某些特定情況下的存儲(chǔ)過(guò)程或?function?，以及?trigger?的調(diào)用和觸發(fā)無(wú)法被正確復(fù)制的問(wèn)題。

缺點(diǎn)：在?row?模式下，所有的執(zhí)行的語(yǔ)句當(dāng)記錄到日志中的時(shí)候，都將以每行記錄的修改來(lái)記錄，這樣可能會(huì)產(chǎn)生大量的日志內(nèi)容，比如有這樣一條?update?語(yǔ)句：

1 ?	UPDATE?product?SET?owner_member_id?=?'b'?WHERE?owner_member_id?=?'a'

執(zhí)行之后，日志中記錄的不是這條?update?語(yǔ)句所對(duì)應(yīng)的事件?(MySQL?以事件的形式來(lái)記錄?bin-log?日志)?，而是這條語(yǔ)句所更新的每一條記錄的變化情況，這樣就記錄成很多條記錄被更新的很多個(gè)事件。自然，bin-log?日志的量就會(huì)很大。尤其是當(dāng)執(zhí)行?alter?table?之類的語(yǔ)句的時(shí)候，產(chǎn)生的日志量是驚人的。因?yàn)?MySQL?對(duì)于?alter?table?之類的表結(jié)構(gòu)變更語(yǔ)句的處理方式是整個(gè)表的每一條記錄都需要變動(dòng)，實(shí)際上就是重建了整個(gè)表。那么該表的每一條記錄都會(huì)被記錄到日志中。

2.?Statement

每一條會(huì)修改數(shù)據(jù)的?SQL?都會(huì)記錄到?master?的?bin-log?中。slave?在復(fù)制的時(shí)候?SQL?進(jìn)程會(huì)解析成和原來(lái)?master?端執(zhí)行過(guò)的相同的?SQL?再次執(zhí)行。

優(yōu)點(diǎn)：在?statement?模式下，首先就是解決了?row?模式的缺點(diǎn)，不需要記錄每一行數(shù)據(jù)的變化，減少了?bin-log?日志量，節(jié)省?I/O?以及存儲(chǔ)資源，提高性能。因?yàn)樗恍枰涗浽?master?上所執(zhí)行的語(yǔ)句的細(xì)節(jié)，以及執(zhí)行語(yǔ)句時(shí)候的上下文的信息。

缺點(diǎn)：在?statement?模式下，由于他是記錄的執(zhí)行語(yǔ)句，所以，為了讓這些語(yǔ)句在?slave?端也能正確執(zhí)行，那么他還必須記錄每條語(yǔ)句在執(zhí)行的時(shí)候的一些相關(guān)信息，也就是上下文信息，以保證所有語(yǔ)句在?slave?端杯執(zhí)行的時(shí)候能夠得到和在?master?端執(zhí)行時(shí)候相同的結(jié)果。

另外就是，由于?MySQL?現(xiàn)在發(fā)展比較快，很多的新功能不斷的加入，使?MySQL?的復(fù)制遇到了不小的挑戰(zhàn)，自然復(fù)制的時(shí)候涉及到越復(fù)雜的內(nèi)容，bug?也就越容易出現(xiàn)。在?statement?中，目前已經(jīng)發(fā)現(xiàn)的就有不少情況會(huì)造成?MySQL?的復(fù)制出現(xiàn)問(wèn)題，主要是修改數(shù)據(jù)的時(shí)候使用了某些特定的函數(shù)或者功能的時(shí)候會(huì)出現(xiàn)，比如：sleep()?函數(shù)在有些版本中就不能被正確復(fù)制，在存儲(chǔ)過(guò)程中使用了?last_insert_id()?函數(shù)，可能會(huì)使?slave?和?master?上得到不一致的?id?等等。由于?row?是基于每一行來(lái)記錄的變化，所以不會(huì)出現(xiàn)類似的問(wèn)題。

3.?mixed

從官方文檔中看到，之前的?MySQL?一直都只有基于?statement?的復(fù)制模式，直到?5.1.5?版本的?MySQL?才開(kāi)始支持?row?復(fù)制。從?5.0?開(kāi)始，MySQL?的復(fù)制已經(jīng)解決了大量老版本中出現(xiàn)的無(wú)法正確復(fù)制的問(wèn)題。但是由于存儲(chǔ)過(guò)程的出現(xiàn)，給?MySQL?Replication?又帶來(lái)了更大的新挑戰(zhàn)。

另外，看到官方文檔說(shuō)，從?5.1.8?版本開(kāi)始，MySQL?提供了除?Statement?和?Row?之外的第三種復(fù)制模式：Mixed，實(shí)際上就是前兩種模式的結(jié)合。在?Mixed?模式下，MySQL?會(huì)根據(jù)執(zhí)行的每一條具體的?SQL?語(yǔ)句來(lái)區(qū)分對(duì)待記錄的日志形式，也就是在?statement?和?row?之間選擇一種。新版本中的?statment?還是和以前一樣，僅僅記錄執(zhí)行的語(yǔ)句。而新版本的?MySQL?中對(duì)?row?模式也被做了優(yōu)化，并不是所有的修改都會(huì)以?row?模式來(lái)記錄，比如遇到表結(jié)構(gòu)變更的時(shí)候就會(huì)以?statement?模式來(lái)記錄，如果?SQL?語(yǔ)句確實(shí)就是?update?或者?delete?等修改數(shù)據(jù)的語(yǔ)句，那么還是會(huì)記錄所有行的變更。

其他參考信息

除以下幾種情況外，在運(yùn)行時(shí)可以動(dòng)態(tài)改變?binlog?的格式：
?????·存儲(chǔ)流程或者觸發(fā)器中間；
?????·啟用了?NDB；
?????·當(dāng)前會(huì)話使用?row?模式，并且已打開(kāi)了臨時(shí)表；

如果?binlog?采用了?Mixed?模式，那么在以下幾種情況下會(huì)自動(dòng)將?binlog?的模式由?statement?模式變?yōu)?row?模式：
?????·當(dāng)?DML?語(yǔ)句更新一個(gè)?NDB?表時(shí)；
?????·當(dāng)函數(shù)中包含?UUID()?時(shí)；
?????·?2?個(gè)及以上包含?AUTO_INCREMENT?字段的表被更新時(shí)；
?????·?執(zhí)行?INSERT?DELAYED?語(yǔ)句時(shí)；
?????·??用?UDF?時(shí)；
?????·??視圖中必須要求運(yùn)用?row?時(shí)，例如建立視圖時(shí)使用了?UUID()?函數(shù)；

設(shè)定主從復(fù)制模式：

1 2 3 4 ?

log-bin=mysql-bin #binlog_format="STATEMENT" #binlog_format="ROW" binlog_format="MIXED"

也可以在運(yùn)行時(shí)動(dòng)態(tài)修改?binlog?的格式。例如：

1 2 3 4 5 6 ?

mysql>?SET?SESSION?binlog_format?=?'STATEMENT'; mysql>?SET?SESSION?binlog_format?=?'ROW'; mysql>?SET?SESSION?binlog_format?=?'MIXED'; mysql>?SET?GLOBAL?binlog_format?=?'STATEMENT'; mysql>?SET?GLOBAL?binlog_format?=?'ROW'; mysql>?SET?GLOBAL?binlog_format?=?'MIXED';

兩種模式的對(duì)比：
Statement?優(yōu)點(diǎn)

·歷史悠久，技術(shù)成熟；

·產(chǎn)生的?binlog?文件較小；

·binlog?中包含了所有數(shù)據(jù)庫(kù)修改信息，可以據(jù)此來(lái)審核數(shù)據(jù)庫(kù)的安全等情況；
·binlog?可以用于實(shí)時(shí)的還原，而不僅僅用于復(fù)制；
·主從版本可以不一樣，從服務(wù)器版本可以比主服務(wù)器版本高；

?

Statement?缺點(diǎn)：
·不是所有的?UPDATE?語(yǔ)句都能被復(fù)制，尤其是包含不確定操作的時(shí)候；
·調(diào)用具有不確定因素的?UDF?時(shí)復(fù)制也可能出現(xiàn)問(wèn)題；
·運(yùn)用以下函數(shù)的語(yǔ)句也不能被復(fù)制：
???*?LOAD_FILE()
???*?UUID()
???*?USER()
???*?FOUND_ROWS()
??*?SYSDATE()?(除非啟動(dòng)時(shí)啟用了?–sysdate-is-now?選項(xiàng))
·INSERT?…?SELECT?會(huì)產(chǎn)生比?RBR?更多的行級(jí)鎖；
·復(fù)制須要執(zhí)行全表掃描?(WHERE?語(yǔ)句中沒(méi)有運(yùn)用到索引)?的?UPDATE?時(shí)，須要????????比?row?請(qǐng)求更多的行級(jí)鎖；
·對(duì)于有?AUTO_INCREMENT?字段的?InnoDB?表而言，INSERT?語(yǔ)句會(huì)阻塞其他?INSERT?語(yǔ)句；
·對(duì)于一些復(fù)雜的語(yǔ)句，在從服務(wù)器上的耗資源情況會(huì)更嚴(yán)重，而?row?模式下，只會(huì)對(duì)那個(gè)發(fā)生變化的記錄產(chǎn)生影響；
·存儲(chǔ)函數(shù)(不是存儲(chǔ)流程?)在被調(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ì)消耗更多資源；

?

Row?優(yōu)點(diǎn)
·任何情況都可以被復(fù)制，這對(duì)復(fù)制來(lái)說(shuō)是最安全可靠的；
·和其他大多數(shù)數(shù)據(jù)庫(kù)系統(tǒng)的復(fù)制技能一樣；
·多數(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ù)制成為可能；

Row?缺點(diǎn)
·生成的?binlog?日志體積大了很多；
·復(fù)雜的回滾時(shí)?binlog?中會(huì)包含大量的數(shù)據(jù)；
·主服務(wù)器上執(zhí)行?UPDATE?語(yǔ)句時(shí)，所有發(fā)生變化的記錄都會(huì)寫到?binlog?中，而?statement?只會(huì)寫一次，這會(huì)導(dǎo)致頻繁發(fā)生?binlog?的寫并發(fā)請(qǐng)求；
·UDF?產(chǎn)生的大?BLOB?值會(huì)導(dǎo)致復(fù)制變慢；
·不能從?binlog?中看到都復(fù)制了寫什么語(yǔ)句(加密過(guò)的)；
·當(dāng)在非事務(wù)表上執(zhí)行一段堆積的?SQL?語(yǔ)句時(shí)，最好采用?statement?模式，否則很容易導(dǎo)致主從服務(wù)器的數(shù)據(jù)不一致情況發(fā)生；

另外，針對(duì)系統(tǒng)庫(kù)?MySQL?里面的表發(fā)生變化時(shí)的處理準(zhǔn)則如下：
·如果是采用?INSERT，UPDATE，DELETE?直接操作表的情況，則日志格式根據(jù)?binlog_format?的設(shè)定而記錄；
·如果是采用?GRANT，REVOKE，SET?PASSWORD?等管理語(yǔ)句來(lái)做的話，那么無(wú)論如何都要使用?statement?模式記錄；
·使用?statement?模式后，能處理很多原先出現(xiàn)的主鍵重復(fù)問(wèn)題；

?