轉(zhuǎn)自dbaplus:?http://dbaplus.cn/news-11-1320-1.html
作者介紹
楊奇龍，前阿里數(shù)據(jù)庫(kù)團(tuán)隊(duì)資深DBA，主要負(fù)責(zé)淘寶業(yè)務(wù)線，經(jīng)歷多次雙十一，有海量業(yè)務(wù)訪問(wèn)DB架構(gòu)設(shè)計(jì)經(jīng)驗(yàn)。目前就職于有贊科技，負(fù)責(zé)數(shù)據(jù)庫(kù)運(yùn)維工作，熟悉MySQL性能優(yōu)化、故障診斷、性能壓測(cè)。
簡(jiǎn)介
和團(tuán)隊(duì)內(nèi)部的同事一起溝通，討論了MySQL數(shù)據(jù)庫(kù)系統(tǒng)數(shù)據(jù)安全性問(wèn)題，主要針對(duì)MySQL丟數(shù)據(jù) 、主從不一致的場(chǎng)景 ，還有業(yè)務(wù)層面使用不得當(dāng)導(dǎo)致主備庫(kù)數(shù)據(jù)結(jié)構(gòu)不一樣的情況，本文是基于以上的討論和總結(jié)做的思維導(dǎo)圖。
思維導(dǎo)圖
內(nèi)容展示
OS
BBU：數(shù)據(jù)庫(kù)服務(wù)器要配置BBU，BBU在電源供應(yīng)出現(xiàn)問(wèn)題的時(shí)候，為RAID控制器緩存提供電源。當(dāng)電源斷電時(shí)，BBU電力可以使控制器內(nèi)緩存中的數(shù)據(jù)可以保存一定時(shí)間（根據(jù)BBU的型號(hào)而決定）。用戶只需要在BBU電力耗盡之前恢復(fù)正常供電，緩存中的數(shù)據(jù)即可被完整的寫(xiě)回RAID中,避免斷電導(dǎo)致數(shù)據(jù)丟失
防止OS異常斷電導(dǎo)致數(shù)據(jù)無(wú)法正常落盤(pán)
磁盤(pán)禁用cache，MySQL的 O_DIRECT 方式可以跳過(guò)pagecache寫(xiě)數(shù)據(jù)
單機(jī)
（1）redo log
innodb_flush_log_at_timeout
< 5.6.6： 每隔一秒將redo log buffer中的數(shù)據(jù)刷新到磁盤(pán)
>= 5.6.6：每隔innodb_flush_log_at_timeout秒將數(shù)據(jù)刷新到磁盤(pán)中去
innodb_flush_log_at_trx_commit=1
（2）binlog
sync_binlog? =1
（3）innodb buffer data
不同的flush mathod刷數(shù)據(jù)的圖形展示。圖片來(lái)自hatemysql.com。
（4）InnoDB 落盤(pán)
MySQL數(shù)據(jù)落盤(pán)的路徑，圖片來(lái)自李春hatemysql.com。
主從不一致
主庫(kù)insert之后再回滾 ，主備庫(kù)自增主鍵不一致
使用replace into操作，導(dǎo)致主備庫(kù)自增主鍵不一致
set session sql_log_bin=0
業(yè)務(wù)架構(gòu)
常見(jiàn)的雙寫(xiě)
“丟”數(shù)據(jù)的場(chǎng)景
（1）slave_skip_counter 不合理
slave_skip_counter =1
slave_skip_counter >1
（2）DB Crash，OS正常
innodb_flush_log_at_trx_commit=0
事務(wù)提交時(shí)，不刷新緩存，系統(tǒng)刷新的頻率是1s，故會(huì)丟失1s的數(shù)據(jù)。
innodb_flush_log_at_trx_commit=1
事務(wù)提交時(shí)，會(huì)刷新到磁盤(pán)，保證事務(wù)落盤(pán)，故不丟數(shù)據(jù)。
innodb_flush_log_at_trx_commit=2
事務(wù)提交時(shí)，刷新到os cache，系統(tǒng)沒(méi)有crash，數(shù)據(jù)無(wú)丟失。
（3）DB正常，OS Crash
帶有 BBU
innodb_flush_log_at_trx_commit=0
事務(wù)提交時(shí)，不刷新緩存，系統(tǒng)刷新的頻率是1s，故會(huì)丟失1s的數(shù)據(jù)。
innodb_flush_log_at_trx_commit=1
事務(wù)提交時(shí)，會(huì)刷新到磁盤(pán)，保證事務(wù)落盤(pán)，故不丟數(shù)據(jù)。
innodb_flush_log_at_trx_commit=2
事務(wù)提交時(shí)，刷新到os cache，系統(tǒng)沒(méi)有crash，數(shù)據(jù)無(wú)丟失。
（4）slave非實(shí)時(shí)寫(xiě)redo和binlog丟失數(shù)據(jù)
在slave機(jī)器上會(huì)存在三個(gè)文件來(lái)保證事件的正確重放：relay log、 relay log info、 master info。
（5）異步模式
事務(wù)T1寫(xiě)入binlog buffer；
dumper線程通知slave有新的事務(wù)T1；
binlog buffer進(jìn)行checkpoint；
slave因?yàn)榫W(wǎng)絡(luò)不穩(wěn)定，一直沒(méi)有收到t1；master掛掉，slave提升為新的master，t1丟失。
（6）semi sysnc
after_commit
比如主庫(kù)操作update t1 set val=1 where id=10將val從5修改為1 。
會(huì)話session1在主庫(kù)提交update t1 set val=1 where id=10 ；commit；
主庫(kù)根據(jù)二階段提交將數(shù)據(jù)持久化到innodb和提交日志binlog；
同步日志到slave ，并等待slave 返回ack信息，等待的實(shí)際時(shí)間以 rpl_semi_sync_master_timeout 為準(zhǔn)，超過(guò)該設(shè)置時(shí)間則超時(shí)，主庫(kù)返回給客戶端成功寫(xiě)入信息。
接收到來(lái)自slave的ack信息，返回成功給OK客戶端。
分析：
第四步之前，master還未收到slave的ack信息，此時(shí)由于事務(wù)已經(jīng)提交，除了session1，其他會(huì)話是可以看到 val=1。
主庫(kù)服務(wù)器down或者主庫(kù)實(shí)例crash，此時(shí)發(fā)生HA切換。
主庫(kù)未接收到slave的ack信息，slave接收到日志并落盤(pán)，應(yīng)用binlog更新。t1.val=1，此時(shí)業(yè)務(wù)切換到slave上能獲取到一致的數(shù)據(jù)。
如果在slave還未接收到binlog并且主庫(kù)掛了，因?yàn)橹鲙?kù)已經(jīng)提交，此時(shí)主庫(kù)t1.val是1而從庫(kù)t1.val是5，主備不一致。
after_sync
比如主庫(kù)操作update t1 set val=1 where id=10將val從5修改為1 。
會(huì)話session1在主庫(kù)提交 :update t1 set val=1 where id=10；commit；
主庫(kù)將事務(wù)寫(xiě)入binlog。
將binlog同步給slave，不提交。
等待slave返回ack信息，等待的實(shí)際時(shí)間以rpl_semi_sync_master_timeout為準(zhǔn)，如果超時(shí)master改為異步模式。
接收到來(lái)自slave的ack信息，主庫(kù)進(jìn)行提交并且返回成功給OK客戶端。
分析：
如果在第3步等待slave ack的過(guò)程中，主庫(kù)發(fā)生crash(此時(shí)t1.val=5),HA 切換到slave，應(yīng)用查詢slave 。如果slave接收到binlog并發(fā)送ack給master，則t1.val=1。
如果slave響應(yīng)主庫(kù)，但是主庫(kù)crash ，此時(shí)因?yàn)橹鲙?kù)還沒(méi)提交t1.val=1, slave t1.val=5，但是主庫(kù)啟動(dòng)恢復(fù)之后t1.val會(huì)變成5，主備還是一致的。
如果slave未接收到事務(wù)和響應(yīng)主庫(kù)，此時(shí)t1.val=5，無(wú)論哪種狀態(tài)，對(duì)于所有客戶端數(shù)據(jù)庫(kù)都是一致，事務(wù)都沒(méi)有丟失。
知識(shí)點(diǎn)：兩階段提交
第一階段是先prepare、再同步寫(xiě)redo log，第二階段同步寫(xiě)binlog、再commit，如果在寫(xiě)入commit標(biāo)志時(shí)崩潰，則恢復(fù)時(shí)，會(huì)重新對(duì)commit標(biāo)志進(jìn)行寫(xiě)入。
HA切換
（6）主從
binlog_format
ROW（最安全）
MIXED（不推薦）
STATEMENT（不推薦）
sync_binlog
=0：由os系統(tǒng)的刷新機(jī)制來(lái)控制，刷新數(shù)據(jù)到磁盤(pán)的頻率
=1：每次commit刷新到磁盤(pán)
>1：每N次提交刷新到磁盤(pán)
innodb_support_xa
版本要打開(kāi)，保證binlog提交的順序，否則亂序的binlog在恢復(fù)或者slave應(yīng)用的時(shí)候會(huì)有問(wèn)題，及以后廢棄，始終支持兩階段提交。
crash safe
crash-safe就是將relay-info.log的信息保存在InnoDB的事務(wù)表中，這時(shí)執(zhí)行relay log中的事務(wù)和寫(xiě)relay info在一個(gè)事務(wù)中，就能得到原子性保證。從而避免已執(zhí)行的binlog位點(diǎn)和寫(xiě)入relay log info的位點(diǎn)信息不一致的情況發(fā)生。
IO thread
master-info-repository=TABLE
sync_master_info=N：每N個(gè)event刷新一次表
SQL thread
relay-log-info-repository=TABLE
sync_relay_info=N：每N個(gè)event刷新一次表
relay-log-recovery
當(dāng)slave從庫(kù)宕機(jī)后，假如relay-log損壞了，導(dǎo)致一部分中繼日志沒(méi)有處理，則自動(dòng)放棄所有未執(zhí)行的relay-log，并且重新從master上獲取日志，這樣就保證了relay-log的完整性。
relay_log_info_repository = TABLE
relay_log_recovery?????????? = 1
http://mysqlserverteam.com/relay-log-recovery-when-sql-threads-position-is-unavailable/
semi_sync
after commit：master把每一個(gè)事務(wù)寫(xiě)到二進(jìn)制日志并保存到磁盤(pán)上，并且提交（commit）事務(wù)，再把事務(wù)發(fā)送給從庫(kù)，開(kāi)始等待slave的應(yīng)答。響應(yīng)后master返回結(jié)果給客戶端，客戶端才可繼續(xù)。
after sync：master把每一個(gè)事務(wù)寫(xiě)到二進(jìn)制日志并保存磁盤(pán)上，并且把事務(wù)發(fā)送給從庫(kù)，開(kāi)始等待slave的應(yīng)答。確認(rèn)slave響應(yīng)后，再提交（commit）事務(wù)到存儲(chǔ)引擎，并返回結(jié)果給客戶端，客戶端才可繼續(xù)。
GTID
相比位點(diǎn)復(fù)制，能減少不一致的概率
參考資料
MySQL數(shù)據(jù)丟失討論
細(xì)看InnoDB數(shù)據(jù)落盤(pán)
MySQL5.7 深度解析：Loss-Less半同步復(fù)制技術(shù)
MySQL 5.7 Replication相關(guān)新功能說(shuō)明
下載方式
登錄云盤(pán)http://pan.baidu.com/s/1qYbOOyg，可下載高清版思維導(dǎo)圖。
