目前很多公司中的生產(chǎn)環(huán)境中都使用了MySQL Replication 贪染，也叫 MySQL 復(fù)制，搭建配置方便等很多特性讓 MySQL Replication 的應(yīng)用很廣泛棚品，我們曾經(jīng)使用過一主拖20多個從庫來分擔(dān)業(yè)務(wù)壓力卜高。關(guān)于 MySQL Replication 的文章網(wǎng)絡(luò)上也有很多，但大多數(shù)都是講如何搭建MySQL Replication南片，并沒有說清楚如何才能搭建出高可靠的MySQL Replication掺涛。這篇文章也對半同步復(fù)制，無損復(fù)制疼进，多源復(fù)制做了講解薪缆。

復(fù)制有哪些用途

讀寫分離

災(zāi)備

高可用

線下統(tǒng)計

備份

復(fù)制是如何工作的

從上圖中可以看到，復(fù)制的主要步驟：

master 將改變記錄到二進(jìn)制日志 binary log 中

slave 上的IO線程將主庫上的日志復(fù)制到自己的 relay log 中

slave 上SQL線程回放中繼日志的內(nèi)容，使 slave 上的數(shù)據(jù)與 master 達(dá)到一致

binlog二進(jìn)制日志文件拣帽，用于記錄 MySQL 的數(shù)據(jù)變更疼电。

relay-log中繼日志文件，slave 的I/O線程讀取 master 的 binlog减拭，記錄到 relay-log 中蔽豺，然后 SQL 線程會讀取 relay-log 日志的內(nèi)容并應(yīng)用到 slave 服務(wù)器。

binlog 記錄的格式

STATEMENT（記錄操作的SQL語句）

優(yōu)點減少了 binlog 日志量拧粪，節(jié)約IO修陡，提高性能，易于理解

缺點不是所有的DML語句都能被復(fù)制可霎，有些函數(shù)UUID() 魄鸦、FOUND_ROWS()、USER() 也無法被復(fù)制

ROW（記錄操作的每一行數(shù)據(jù)的變化信息癣朗，RC 隔離級別拾因，必須是 row 格式）

優(yōu)點任何情況都可以被復(fù)制，ROW 模式是最安全可靠的

缺點產(chǎn)生大量的日志旷余，特別是 copy data 的 DDL 會讓日志暴漲

建議表一定要有主鍵

MIXED（混合模式）

先使用 STATEMENT 模式記錄 binlog 绢记，對于 STATEMENT 模式無法復(fù)制的操作使用 ROW 模式保存 binlog，MySQL 會根據(jù)執(zhí)行的 SQL 語句選擇日志記錄方式正卧。Bug 較多庭惜，不建議使用。

binlog Events

我們都知道　binlog 日志用于記錄所有對 MySQL 的操作的變更穗酥，而這每一個變更都會對應(yīng)的事件护赊，也就是 Event，index文件記錄了所有的 binlog 位置砾跃，每個 binlog 會有 header event骏啰，rotate 三個 event，binlog 的結(jié)構(gòu)如下抽高。

常見的Event如下：

Format_desc：全新的binlog日志文件

Rotate ：日志分割

Table_map：表判耕，列等元數(shù)據(jù)

Query：查詢

Write_rows: 插入

Update_rows：更新

Delete_rows：刪除

在了解了以上基礎(chǔ)的內(nèi)容后，我們可以帶著以下的三個問題去學(xué)習(xí)復(fù)制到底是怎樣工作的翘骂。

事務(wù)是如何提交的壁熄？事務(wù)提交先寫 binlog 還是 redo log？

為什么 MySQL 有 binlog碳竟，還有redo log?

如何保證這兩部分的日志做到一致性草丧？

務(wù)是如何提交的？事務(wù)提交先寫 binlog 還是 redo log莹桅？

以上的圖片中可以看到昌执，事務(wù)的提交主要分三個主要步驟：

InnoDB 層寫 prepare log，此時SQL已經(jīng)成功執(zhí)行，并生成 xid 信息及 redo 和 undo 的內(nèi)存日志懂拾，寫入 redo log file

MySQL Server 層寫 binlog （write --》 fsync）

InnoDB 層寫 commit log煤禽， InnoDB存儲引擎內(nèi)提交，使 undo 和 redo 永久寫入磁盤

為什么 MySQL 有 binlog岖赋，還有 redo log?

這個是因為 MySQL 體系結(jié)構(gòu)的原因檬果，MySQL 是多存儲引擎的，不管使用那種存儲引擎唐断，都會有 binlog选脊，而不一定有 redo log，簡單的說栗涂，binlog 是 MySQL Server 層的知牌，redo log 是 InnoDB 層的祈争。

如何保證這兩部分的日志做到一致性斤程？

事務(wù)提交的過程上面已經(jīng)說到，需要寫 redo log 還要寫 binlog菩混，那么如何保證數(shù)據(jù)的一致性呢忿墅，如果不能保證寫這兩個文件在同一事務(wù)中，那么就會造成數(shù)據(jù)不一致沮峡，這個不一致包括MySQL crash時和主從復(fù)制的數(shù)據(jù)不一致疚脐。

面試時經(jīng)常會問的一個問題，影響MySQL寫入性能邢疙、數(shù)據(jù)一致性的參數(shù)有哪些棍弄？無疑是雙一參數(shù)innodb_flush_log_at_trx_commit和sync_binlog， 這兩個參數(shù)是控制 MySQL 磁盤寫入策略以及數(shù)據(jù)安全性的關(guān)鍵參數(shù)疟游，MySQL 為了保證 master 和 slave 的數(shù)據(jù)一致性呼畸，就必須保證 binlog 和 InnoDB redo 日志的一致性。

參數(shù)說明如下：

innodb_flush_log_at_trx_commit（redo）

0 log buffer 每秒一次地寫入 log file 中颁虐，且進(jìn)行 flush 操作

1 每次事務(wù)提交時都會把 log buffer 的數(shù)據(jù)寫入 log file蛮原，并進(jìn)行 flush 操作

2 每次事務(wù)提交時 MySQL 都會把 log buffer 的數(shù)據(jù)寫入 log file，不進(jìn)行 flush 操作

sync_binlog （binlog）

0 刷新binlog_cache中的信息到磁盤由os決定

N 每N次事務(wù)提交刷新binlog_cache中的信息到磁盤

那么如何保證 binlog 和 InnoDB redo 日志的一致性另绩，MySQL 使用內(nèi)部分布式事物來保證一致性儒陨，MySQL 在 prepare 階段會生成 xid，xid 會寫入prepare log中笋籽，也會寫入到binlog中蹦漠，當(dāng)恢復(fù)時會對比此事務(wù)的xid在兩個文件中是否都有，如果都存在該xid對應(yīng)的事物會提交车海，反之會rollback此事務(wù)津辩，下面是幾種情況分析：

當(dāng)事務(wù)在 prepare 階段 crash，將該事務(wù) rollback。

當(dāng)事務(wù)在 binlog 階段 crash喘沿，此時日志還沒有成功寫入到磁盤中闸度，啟動時會 rollback 此事務(wù)

當(dāng)事務(wù)在 binlog 日志已經(jīng) fsync() 到磁盤后 crash，但是InnoDB沒有來得及 commit,此時 MySQL 啟動時會重新將該事務(wù)重做并 commit蚜印，使 InnoDB 存儲引擎的 redo log 和 binlog 始終保持一致莺禁。

總結(jié)起來說就是如果一個事物在 prepare log 階段中落盤成功，并在 MySQL Server 層中的 binlog 也寫入成功窄赋，那這個事務(wù)必定commit成功哟冬。

組提交

現(xiàn)在回頭看事務(wù)是如何提交的那張圖，會發(fā)現(xiàn)innodb層每個事務(wù)是并行的忆绰，但是在寫binlog時浩峡，MySQL Server層就變成了串行，這是因為每次提交都會去申請prepare_commit_mutex這把鎖造成的错敢，在MySQL 5.6版本中翰灾，提供了Binary Log Group Commit 也就是組提交，Group Commit分為了三個階段稚茅。

Flush stage：Leader線程遍歷FLUSH_STAGE鏈表纸淮，寫入binary log緩存

Sync stage ：將binlog緩存sync到磁盤，當(dāng)sync_binlog=1時所有該隊列事務(wù)的二進(jìn)制日志緩存永久寫入磁盤

Commit stage: leader根據(jù)順序讓InnoDB存儲引擎完成Commit

下面是我們測試組提交的一張圖亚享，可以看到組提交的TPS高不少咽块。

1062、1053復(fù)制錯誤

可能DBA在使用MySQL Replication的過程中欺税，在slave 宕機或異常時侈沪，會遇到1062等錯誤，大家都是使用以下方式解決晚凿。

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1

GTID 通過空事務(wù)方式

但為什么數(shù)據(jù)會沖突呢亭罪？我們分解下復(fù)制的步驟，這里有張圖很好晃虫，圖片來自于網(wǎng)絡(luò)皆撩。

我們可以看到這里有保存Replication matadata的兩個文件，

relay-info.log保存了SQL線程回放到的Relay_log_name和Relay_log_pos哲银，以及對應(yīng)的Master的Master_log_name和Master_log_pos扛吞。

master-info.log保存了連接master的用戶，密碼荆责，端口滥比，Master_log_name 和 Master_log_pos等信息。

如下圖做院，如果SQL線程正在回放盲泛，回放完后濒持，還沒來的及寫到Replication matadata的文件中，就宕機了寺滚，此時重啟slave后柑营，就會出現(xiàn)1062錯誤。

在MySQL 5.6中村视，提供了SQL/IO thread crash-safe特性官套。通過將relay_log_info_repository=TABLE，relay-info將信息寫入到mysql.slave_relay_log_info這張表中蚁孔，不但可以保證一致性（寫文件變成同一事物的原子操作）奶赔，還提高了寫入性能。

如上圖杠氢。IO thread同理站刑，稍有不同的是 relay-log-recover 設(shè)置為1后，slave 的 IO thread 讀取 events 時鼻百，會根據(jù)從 SQL thread 回放到的位置重新讀取绞旅。

復(fù)制最優(yōu)的參數(shù)配置

上面講了這么多，其實就是得出 MySQL Replication 的最可靠的參數(shù)配置愕宋。

master：

binlog_format =ROW

transaction-isolation =READ-COMMITTE

Dexpire_logs_days =30

server-id =327

sync_binlog =1

innodb_flush_log_at_trx_commit =1

innodb_support_xa =1

slave：

log_slave_updates=1

server-id =328

relay_log_recover =1

relay_log_info_repository =TABLE

master_info_repository =TABLE

read_only =1

如何提高復(fù)制效率玻靡？

MySQL 5.6提供了并行復(fù)制,但是這種并行只是基于database的结榄。如果是基于單database的依然無法做到真正的并行回放中贝，這個階段很多DBA將數(shù)據(jù)庫進(jìn)行垂直拆分，將一個database拆分成幾個database臼朗，通過設(shè)置slave_parallel_workers=n邻寿，可以進(jìn)行database級別的并行復(fù)制，但對于熱點業(yè)務(wù)復(fù)制延遲依然無法解決视哑。

MySQL 5.6版本中還引入了GTID绣否，不但降低了主從failover時，尋找filename挡毅，position的難度蒜撮，更是加入到了組提交中，這也造就了MySQL 5.7版本中的Multi-Threaded Slave的出現(xiàn)跪呈。如下圖段磨，一組中的事務(wù)，可以并行回放耗绿。

在下圖的測試中苹支，MySQL 5.7的多線程復(fù)制極大的提升了延遲效率，在30個線程并發(fā)操作的時候還能保證平均延遲5.9秒左右误阻，而單線程復(fù)制的延遲率基本一直在上升债蜜。

Multi-Threaded Slave 相關(guān)參數(shù)

slave-parallel-type= DATABASE /LOGICAL_CLOCK--DATABASE-- 基于庫級別的并行復(fù)制 與5.6相同

--LOGICAL_CLOCK-- 邏輯時鐘晴埂，主上怎么并行執(zhí)行，從上怎么回放寻定。

slave-parallel-workers=16 ?-- 并行復(fù)制的線程數(shù)

slave_preserve_commit_order=1 --commit的順序保持一致

半同步

我們都知道儒洛，默認(rèn)的 MySQL Replication 復(fù)制為異步模式，異步也就說明會有丟失數(shù)據(jù)的可能性狼速，MySQL在5.5版本中提供了 semi-sync replication晶丘，也就是半同步，但半同步只能說減少數(shù)據(jù)丟失的風(fēng)險唐含，所以在 MySQL 5.7版本中浅浮，MySQL 提供了 lossless semi-sync replication，也就是無損復(fù)制捷枯，可最低限度的減少數(shù)據(jù)丟失（無損復(fù)制會和半同步一樣在出問題時會切換為異步復(fù)制）滚秩。

在半同步中，至少有一個Slave節(jié)點收到binlog后再返回淮捆，不能完全避免數(shù)據(jù)丟失郁油，超時后，切回異步復(fù)制攀痊。在事物提交的過程中桐腌，在InnoDB層的 commit log 階段后，Master 節(jié)點需要收到至少一個Slave節(jié)點回復(fù)的ACK后苟径，才能繼續(xù)下一個事物案站。

無損復(fù)制

在無損復(fù)制中，master把binlog發(fā)送給slave棘街，只有在slave把binlog寫到本地的relay-log里蟆盐，master才會將事務(wù)提交到存儲引擎層，然后把請求返回給客戶端遭殉，客戶端才可以看見剛才提交的事務(wù)石挂。在一個事物提交的過程中，在MySQL Server 層的 binlog階段后险污，Master節(jié)點需要收到至少一個Slave節(jié)點回復(fù)的ACK后痹愚，才能繼續(xù)下一個事物。

半同步復(fù)制與無損復(fù)制的對比

ACK的時間點不同

半同步復(fù)制在InnoDB層的Commit Log后蛔糯，等待ACK拯腮。

無損復(fù)制在MySQL Server層的Write binlog后，等待ACK渤闷。

主從數(shù)據(jù)一致性

半同步復(fù)制意味著在Master節(jié)點上疾瓮，這個剛剛提交的事物對數(shù)據(jù)庫的修改，對其他事物是可見的飒箭。

無損復(fù)制在write binlog完成后狼电，就傳輸binlog蜒灰，但還沒有去寫commit log，意味著當(dāng)前這個事物對數(shù)據(jù)庫的修改肩碟，其他事物也是不可見的强窖。

半同步相關(guān)參數(shù)

rpl_semi_sync_master_enabled=1rpl_semi_sync_slave_enabled=1rpl_semi_sync_master_timeout=1000rpl_semi_sync_master_wait_for_slave_count=1rpl_semi_sync_master_wait_point=AFTER_SYNC

rpl_semi_sync_master_wait_for_slave_count=1

半同步相關(guān)事件統(tǒng)計

Rpl_semi_sync_master_tx_avg_wait_time--開啟Semi_sync，平均需要額外等待的時間

Rpl_semi_sync_master_net_avg_wait_time--事務(wù)進(jìn)入等待隊列后削祈，到網(wǎng)絡(luò)平均等待時間Semi-sync的網(wǎng)絡(luò)消耗有多大翅溺。Rpl_semi_sync_master_status-- 則表示當(dāng)前Semi-sync是否正常工作

Rpl_semi_sync_master_no_times--可以知道一段時間內(nèi)，Semi-sync是否有超時失敗過髓抑，記錄了失敗次數(shù)咙崎。

multi-source

然而在MySQL 5.7版本中，提供了多源復(fù)制吨拍，多源復(fù)制的出現(xiàn)對于分庫分表的業(yè)務(wù)提供了極大的便利褪猛，目前我們已經(jīng)部署了多套多源復(fù)制供統(tǒng)計使用。

如上圖羹饰，多源復(fù)制采用多通道的模式伊滋，和普通的復(fù)制相比，就是使用FOR CHANNEL進(jìn)行了分離队秩。

CHANGEMASTERTO.... FOR CHANNEL ‘m1';CHANGEMASTERTO.... FOR CHANNEL ‘m2';

上面我們也說到笑旺，為了提高復(fù)制效率，很多DBA會根據(jù)業(yè)務(wù)進(jìn)行DB拆分馍资，但拆分后又面臨一個新的問題筒主，就是join，join絕對是關(guān)系型數(shù)據(jù)庫中最常用一個特性迷帜，然而在分布式的環(huán)境中物舒，join是最難解決的一個問題色洞，使用多源復(fù)制就能很好的解決這個問題戏锹。

如果數(shù)據(jù)庫，表名一致如何使用多源復(fù)制火诸？锦针，其實只要解決了數(shù)據(jù)沖突的問題就可以使用。

如上圖的分庫分表架構(gòu)置蜀，可以使用以下參數(shù)實現(xiàn)奇偶插入的方式去解決奈搜。

auto_increment_offset=1…n

auto_increment_increment=n

但這種方式需要提前考慮擴展性。