MySQL 主備的基本原理
備庫(kù) B 跟主庫(kù) A 之間維持了一個(gè)長(zhǎng)連接。主庫(kù) A 內(nèi)部有一個(gè)線程，專門(mén)用于服務(wù)備庫(kù) B 的這個(gè)長(zhǎng)連接。
一個(gè)事務(wù)日志同步的完整過(guò)程是這樣的：
- 在備庫(kù) B 上通過(guò) change master 命令，設(shè)置主庫(kù) A 的 IP、端口、用戶名、密碼，以及要從哪個(gè)位置開(kāi)始請(qǐng)求 binlog，這個(gè)位置包含文件名和日志偏移量。
- 在備庫(kù) B 上執(zhí)行 start slave 命令，這時(shí)候備庫(kù)會(huì)啟動(dòng)兩個(gè)線程，就是圖中的 io_thread 和 sql_thread。其中 io_thread 負(fù)責(zé)與主庫(kù)建立連接。
- 主庫(kù) A 校驗(yàn)完用戶名、密碼后，開(kāi)始按照備庫(kù) B 傳過(guò)來(lái)的位置，從本地讀取 binlog，發(fā)給 B。
- 備庫(kù) B 拿到 binlog 后，寫(xiě)到本地文件，稱為中轉(zhuǎn)日志（relay log）。
- sql_thread 讀取中轉(zhuǎn)日志，解析出日志里的命令，并執(zhí)行。
binlog 三種格式
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ǔ)句：
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
從 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ì)記錄所有行的變更。
循環(huán)復(fù)制問(wèn)題
通過(guò)上面對(duì) MySQL 中 binlog 基本內(nèi)容的理解，你現(xiàn)在可以知道，binlog 的特性確保了在備庫(kù)執(zhí)行相同的 binlog，可以得到與主庫(kù)相同的狀態(tài)。
因此，我們可以認(rèn)為正常情況下主備的數(shù)據(jù)是一致的。也就是說(shuō)，圖 1 中 A、B 兩個(gè)節(jié)點(diǎn)的內(nèi)容是一致的。其實(shí)，圖 1 中我畫(huà)的是 M-S 結(jié)構(gòu)，但實(shí)際生產(chǎn)上使用比較多的是雙 M 結(jié)構(gòu)，也就是圖 9 所示的主備切換流程。
對(duì)比圖 9 和圖 1，你可以發(fā)現(xiàn)，雙 M 結(jié)構(gòu)和 M-S 結(jié)構(gòu)，其實(shí)區(qū)別只是多了一條線，即：節(jié)點(diǎn) A 和 B 之間總是互為主備關(guān)系。這樣在切換的時(shí)候就不用再修改主備關(guān)系。但是，雙 M 結(jié)構(gòu)還有一個(gè)問(wèn)題需要解決。
業(yè)務(wù)邏輯在節(jié)點(diǎn) A 上更新了一條語(yǔ)句，然后再把生成的 binlog 發(fā)給節(jié)點(diǎn) B，節(jié)點(diǎn) B 執(zhí)行完這條更新語(yǔ)句后也會(huì)生成 binlog。（我建議你把參數(shù) log_slave_updates 設(shè)置為 on，表示備庫(kù)執(zhí)行 relay log 后生成 binlog）。
那么，如果節(jié)點(diǎn) A 同時(shí)是節(jié)點(diǎn) B 的備庫(kù)，相當(dāng)于又把節(jié)點(diǎn) B 新生成的 binlog 拿過(guò)來(lái)執(zhí)行了一次，然后節(jié)點(diǎn) A 和 B 間，會(huì)不斷地循環(huán)執(zhí)行這個(gè)更新語(yǔ)句，也就是循環(huán)復(fù)制了。這個(gè)要怎么解決呢？
從上面的圖 6中可以看到，MySQL 在 binlog 中記錄了這個(gè)命令第一次執(zhí)行時(shí)所在實(shí)例的 server id。因此，我們可以用下面的邏輯，來(lái)解決兩個(gè)節(jié)點(diǎn)間的循環(huán)復(fù)制的問(wèn)題：
- 規(guī)定兩個(gè)庫(kù)的 server id 必須不同，如果相同，則它們之間不能設(shè)定為主備關(guān)系；
- 一個(gè)備庫(kù)接到 binlog 并在重放的過(guò)程中，生成與原 binlog 的 server id 相同的新的 binlog；
- 每個(gè)庫(kù)在收到從自己的主庫(kù)發(fā)過(guò)來(lái)的日志后，先判斷 server id，如果跟自己的相同，表示這個(gè)日志是自己生成的，就直接丟棄這個(gè)日志。
