MySQL數(shù)據(jù)庫(kù)丟失數(shù)據(jù)的場(chǎng)景分析

本篇內(nèi)容主要講解“MySQL數(shù)據(jù)庫(kù)丟失數(shù)據(jù)的場(chǎng)景分析”，感興趣的朋友不妨來(lái)看看。本文介紹的方法操作簡(jiǎn)單快捷，實(shí)用性強(qiáng)。下面就讓小編來(lái)帶大家學(xué)習(xí)“MySQL數(shù)據(jù)庫(kù)丟失數(shù)據(jù)的場(chǎng)景分析”吧!

MySQL數(shù)據(jù)庫(kù)丟失數(shù)據(jù)場(chǎng)景分析

1.引擎層數(shù)據(jù)丟失場(chǎng)景

1.1.InnoDB丟失數(shù)據(jù)場(chǎng)景分析

InnoDB作為MySQL支持事務(wù)的存儲(chǔ)引擎，同Oracle類(lèi)似，事務(wù)提交時(shí)需要寫(xiě)redo、undo log。 InnoDB采用預(yù)寫(xiě)日志（Write Ahead Log）策略，數(shù)據(jù)頁(yè)的變更會(huì)首先在內(nèi)存中完成，同時(shí)將事務(wù)操作順序記錄到redo log中。完成上述的操作后表示該事務(wù)已經(jīng)完成，可以返回給應(yīng)用已提交的信息。 但此時(shí)實(shí)際被更改的數(shù)據(jù)頁(yè)還保存在內(nèi)存中（稱(chēng)為臟頁(yè)），并沒(méi)有flush到磁盤(pán)上即沒(méi)有落地。刷盤(pán)操作一般會(huì)在達(dá)到一定條件后，觸發(fā)checkpoint機(jī)制，此時(shí)會(huì)將內(nèi)存中的臟頁(yè)合并寫(xiě)入到磁盤(pán)里，完成數(shù)據(jù)同步。刷盤(pán)策略由innodb_flush_log_at_trx_commit參數(shù)控制的策略。

    注：這里應(yīng)為redo log 而非 binlog。

參數(shù)innodb_flush_log_at_trx_commit：

     =0 ：每秒 write os cache & flush disk

     =1 ：每次commit都 write os cache & flush disk

     =2 ：每次commit都 write os cache，然后根據(jù)innodb_flush_log_at_timeout參數(shù)（默認(rèn)為1s） flush disk

    innodb_flush_log_at_trx_commit=1最為安全，因?yàn)槊看蝐ommit都保證redo log寫(xiě)入了disk。但是這種方式性能對(duì)DML性能來(lái)說(shuō)比較低，在我們的測(cè)試中發(fā)現(xiàn)，如果設(shè)置為2，DML性能要比設(shè)置為1高10倍左右（所以在短信平臺(tái)出現(xiàn)寫(xiě)入問(wèn)題時(shí)，已將該參數(shù)設(shè)置為2）。

innodb_flush_log_at_trx_commit為0或2的區(qū)別主要體現(xiàn)在在mysql service crash或system crash時(shí)丟失事務(wù)的類(lèi)型：

1）當(dāng)mysql service crash時(shí)，設(shè)置為0就會(huì)丟失1秒內(nèi)的所有已提交及未提交的事務(wù)，且無(wú)法回滾（因?yàn)閞edo log還記錄在log buffer中，沒(méi)有落盤(pán)到redo log）。而設(shè)置為2時(shí)，每次提交都會(huì)寫(xiě)入到os cache中，即使service crash掉，也只會(huì)丟失1秒內(nèi)所有未提交的事務(wù)，而已提交的事務(wù)已經(jīng)寫(xiě)入redo log中，可以回滾。

2）當(dāng)system crash時(shí)，與上述類(lèi)似。

   因此，業(yè)內(nèi)的共識(shí)是在一些DML操作頻繁的場(chǎng)景下，參數(shù)innodb_flush_log_at_trx_commit設(shè)置為2。

雖然這樣就存在丟數(shù)據(jù)的風(fēng)險(xiǎn)：當(dāng)出現(xiàn)mysql service crash時(shí)，重啟后InnoDB會(huì)進(jìn)行crash recovery，則會(huì)丟失innodb_flush_log_at_timeout秒內(nèi)的已提交的數(shù)據(jù)。未提交的數(shù)據(jù)則可由應(yīng)用中的事務(wù)補(bǔ)償機(jī)制處理。但是IO性能可以提高至少10倍。  

PS:當(dāng)開(kāi)啟了內(nèi)部XA事務(wù)（默認(rèn)開(kāi)啟），且開(kāi)啟binlog，情況稍有不一樣。見(jiàn)下文。

1.2.MyISAM丟失數(shù)據(jù)場(chǎng)景分析

    MyISAM存儲(chǔ)引擎在我們的生產(chǎn)環(huán)境中基本沒(méi)有使用。而且我們線上的5.6版本已將系統(tǒng)的數(shù)據(jù)字典表元數(shù)據(jù)表等系統(tǒng)表的默認(rèn)存儲(chǔ)引擎修改為InnoDB。

    由于MyISAM不支持事務(wù)，且沒(méi)有data cache，所有DML操作只寫(xiě)到OS cache中，flush disk操作均由OS來(lái)完成，因此如果服務(wù)器宕機(jī)，這部分?jǐn)?shù)據(jù)肯定會(huì)丟失。

2.主從復(fù)制導(dǎo)致數(shù)據(jù)不一致的場(chǎng)景

    MySQL主從復(fù)制原理：MySQL主庫(kù)在事務(wù)提交時(shí)寫(xiě)binlog，并通過(guò)sync_binlog參數(shù)來(lái)控制binlog刷新到磁盤(pán)“落地”。從庫(kù)中有兩個(gè)線程： IO線程負(fù)責(zé)從主庫(kù)讀取binlog，并記錄到本地的relay log中；SQL線程再將relay log中的記錄應(yīng)用到從庫(kù)。如下圖所示：

2.1.binlog刷新機(jī)制

    master寫(xiě)binlog與innodb引擎寫(xiě)redo類(lèi)似，由參數(shù)sync_binlog控制：

     = 0 ：表示MySQL不控制binlog的刷新，由文件系統(tǒng)控制binlog cache的刷盤(pán)操作

     = N ：表示每sync_binlog在N次事務(wù)提交后，MySQL調(diào)用文件系統(tǒng)的flush操作將binlog cache中的內(nèi)容刷盤(pán)

sync_binlog=1時(shí)最安全，即表示每次事務(wù)提交，MySQL都會(huì)把binlog cache中的內(nèi)容flush disk。這樣在掉電等情況下，系統(tǒng)只有可能丟失1個(gè)事務(wù)的數(shù)據(jù)。但是sync_binlog為1時(shí)，系統(tǒng)的IO消耗非常大。

但是N的值也不易過(guò)大，否則在系統(tǒng)掉電時(shí)會(huì)丟失較多的事務(wù)。當(dāng)前我們生產(chǎn)系統(tǒng)設(shè)置為100.

2.2.內(nèi)部XA事務(wù)原理

    MySQL的存儲(chǔ)引擎與MySQL服務(wù)層之間，或者存儲(chǔ)引擎與存儲(chǔ)引擎之間的分布式事務(wù)，稱(chēng)之為MySQL內(nèi)部XA事務(wù)。最為常見(jiàn)的內(nèi)部XA事務(wù)存在與binlog與InnoDB存儲(chǔ)引擎之間。在事務(wù)提交時(shí)，先寫(xiě)二進(jìn)制日志，再寫(xiě)InnoDB存儲(chǔ)引擎的redo log。對(duì)于這個(gè)操作要求必須是原子的，即需要保證兩者同時(shí)寫(xiě)入。內(nèi)部XA事務(wù)機(jī)制就是保證兩者的同時(shí)寫(xiě)入。

XA事務(wù)的大致流程：

1）事務(wù)提交后，InnoDB存儲(chǔ)引擎會(huì)先做一個(gè)PREPARE操作，將事務(wù)的XID寫(xiě)入到redo log中

2）寫(xiě)binlog

3）將該事務(wù)的commit信息寫(xiě)到redo log中
        

     如果在步驟1和步驟2失敗的情況下，整個(gè)事務(wù)會(huì)回滾，如果在步驟3失敗的情況下，MySQL數(shù)據(jù)庫(kù)在重啟后會(huì)先檢查PREPARE的XID事務(wù)是否已經(jīng)提交，若沒(méi)有，則在存儲(chǔ)引擎層再進(jìn)行一次提交操作。這樣就保證了redo與binlog的一致性，防止丟失事務(wù)。

2.3.主庫(kù)寫(xiě)redo log、binlog不實(shí)時(shí)造成的數(shù)據(jù)不一致

    上面我們介紹了MySQL的內(nèi)部XA事務(wù)流程，但是這個(gè)流程并不是天衣無(wú)縫的，redo的ib_logfile與binlog日志如果被設(shè)置非實(shí)時(shí)flush，就有可能出現(xiàn)以下數(shù)據(jù)不一致的情況：

1）Redo log的trx_prepare未寫(xiě)入，但binlog已寫(xiě)入，則crash recovery后從庫(kù)數(shù)據(jù)比主庫(kù)多。

2）Redo log的trx_prepare與commit都寫(xiě)入了，但binlog未寫(xiě)入，則crash recovery后從庫(kù)數(shù)據(jù)量比主庫(kù)少。

從目前來(lái)看，只能犧牲性能去換取數(shù)據(jù)的安全性，必須要設(shè)置redo log和binlog為實(shí)時(shí)刷盤(pán)，如果對(duì)性能要求很高，則考慮使用SSD來(lái)替代機(jī)械盤(pán)。

2.4.從庫(kù)寫(xiě)redo log、binlog不實(shí)時(shí)造成的數(shù)據(jù)不一致

    主庫(kù)正常，但是從庫(kù)出現(xiàn)異常情況宕機(jī)，如果數(shù)據(jù)丟失，從庫(kù)的SQL線程還會(huì)重新應(yīng)用嗎？這個(gè)我們需要先了解SQL線程的機(jī)制。

    從庫(kù)讀取主庫(kù)的binlog日志后，需要落地3個(gè)文件：

    relay log：即IO Thread讀取過(guò)來(lái)的主庫(kù)binlog，內(nèi)容格式與主庫(kù)的binlog一致

    relay log info：記錄SQL Thread應(yīng)用的relay log的位置、文件號(hào)等信息

    master info：記錄IO Thread讀取主庫(kù)的binlog的位置、文件號(hào)、延遲等信息

    因此如果當(dāng)這3個(gè)文件如果不及時(shí)落地，則system crash后會(huì)導(dǎo)致數(shù)據(jù)的不一致。

在MySQL 5.6.2之前，從庫(kù)記錄的主庫(kù)信息以及從庫(kù)應(yīng)用binlog的信息存放在文件中，即master.info與relay-log.info。在5.6.2版本之后，允許記錄到table中，參數(shù)設(shè)置如下：

master-info-repository  = TABLE  relay-log-info-repository = TABLE  對(duì)應(yīng)的表分別為mysql.slave_master_info與mysql.slave_relay_log_info，且這兩個(gè)表均為innodb引擎表。

master info與relay info還有3個(gè)參數(shù)控制刷新：

1）sync_relay_log：默認(rèn)為10000，即每10000次sync_relay_log事件會(huì)刷新到磁盤(pán)。為0則表示不刷新，交由OS的cache控制。

2）sync_master_info:若master-info-repository為FILE，當(dāng)設(shè)置為0時(shí)，則每次sync_master_info事件都會(huì)刷新到磁盤(pán)，默認(rèn)為10000次刷新到磁盤(pán)；若master-info-repository為T(mén)ABLE，當(dāng)設(shè)置為0時(shí)，則表不做任何更新，設(shè)置為1，則每次事件會(huì)更新表。默認(rèn)為10000。

3）sync_relay_log_info：若relay_log_info_repository為FILE，當(dāng)設(shè)置為0時(shí)，交由OS刷新磁盤(pán)，默認(rèn)為10000次刷新到磁盤(pán)；若relay_log_info_repository為T(mén)ABLE，則無(wú)論為任何值，每次evnet都會(huì)更新表。

如果參數(shù)設(shè)置如下：

sync_relay_log = 1 

sync_master_info = 1  sync_relay_log_info = 1  master-info-repository  = TABLE  relay-log-info-repository = TABLE 

將導(dǎo)致調(diào)用fsync()/fdatasync()隨著master的事務(wù)的增加而增加，且若slave的binlog和redo也實(shí)時(shí)刷新的話，會(huì)帶來(lái)很?chē)?yán)重的IO性能瓶頸。

2.5.主庫(kù)宕機(jī)后無(wú)法及時(shí)恢復(fù)造成的數(shù)據(jù)不一致

當(dāng)主庫(kù)出現(xiàn)故障后，binlog未及時(shí)拉到從庫(kù)中，或者各個(gè)從庫(kù)收到的binlog不一致（多數(shù)是由于網(wǎng)絡(luò)原因）。且主庫(kù)無(wú)法在第一時(shí)間恢復(fù)：

    1）如果主庫(kù)不切換，則應(yīng)用只能讀寫(xiě)主庫(kù)。如果有讀寫(xiě)分離的場(chǎng)景則會(huì)影響應(yīng)用（讀寫(xiě)分離場(chǎng)景中從庫(kù)會(huì)從）。

    2）如果將某一從庫(kù)提升為新的主庫(kù)（如MHA），那么原主庫(kù)未來(lái)得及傳到從庫(kù)的binlog數(shù)據(jù)則會(huì)丟失，并且還涉及到下面2個(gè)問(wèn)題：

     a）各個(gè)從庫(kù)之間接收到的binlog不一致，如果強(qiáng)制拉起一個(gè)從庫(kù)做新主庫(kù)，則從庫(kù)之間數(shù)據(jù)會(huì)不一致。

b)原主庫(kù)恢復(fù)正常后，由于新的主庫(kù)日志丟棄了部分原主庫(kù)的binlog日志，那么會(huì)多出來(lái)故障時(shí)期的這部分binlog。

對(duì)于上面出現(xiàn)的問(wèn)題，業(yè)內(nèi)已經(jīng)有較成熟的方法來(lái)解決：

2.5.1確保binlog全部傳到從庫(kù)

   方案一：使用semisync replication（半同步復(fù)制）插件。半同步復(fù)制的特點(diǎn)是從庫(kù)中有一臺(tái)提交后，主庫(kù)才能提交事務(wù)。優(yōu)點(diǎn)是保證了主、從庫(kù)的數(shù)據(jù)一致性；缺點(diǎn)是對(duì)性能影響很大，依賴(lài)網(wǎng)絡(luò)，適合tps壓力小的場(chǎng)景。

   方案二：雙寫(xiě)binlog，通過(guò)DBDR OS層的文件系統(tǒng)復(fù)制到備機(jī)，或者使用共享盤(pán)保存binlog日志。優(yōu)點(diǎn)和方案一類(lèi)似，但此方案缺點(diǎn)較明顯：

1）DBDR需要部署自己的服務(wù)

2）DBDR腦裂嚴(yán)重。在發(fā)生災(zāi)難場(chǎng)景時(shí)，往往不能正確切換。

3）需要建立heartbeat機(jī)制。保證被監(jiān)控機(jī)的存活。

   方案三：架構(gòu)層面調(diào)整，引入消息隊(duì)列做異步消息處理。比如保證數(shù)據(jù)庫(kù)寫(xiě)成功后，再異步隊(duì)列的方式寫(xiě)一份，部分業(yè)務(wù)可以借助設(shè)計(jì)和數(shù)據(jù)流解決。

2.5.2保證數(shù)據(jù)最小化丟失

   上面的方案設(shè)計(jì)及架構(gòu)比較復(fù)雜，如果能容忍數(shù)據(jù)的丟失，可以考慮使用MHA。

當(dāng)master宕機(jī)后，MHA可以指定一臺(tái)或者選延遲最低或者binlog pos最新的一臺(tái)從庫(kù)，并將其提升為主庫(kù)。

MHA在切換master后，原master可以修復(fù)后以新master的slave角色重新加入集群。從而達(dá)到高可用。

3.總結(jié)

     通過(guò)上面的總結(jié)分析，MySQL丟數(shù)據(jù)的場(chǎng)景眾多，主要還是涉及到引擎層數(shù)據(jù)丟失場(chǎng)景、主從的數(shù)據(jù)不一致場(chǎng)景等。     

     根據(jù)分布式領(lǐng)域的CAP理論（Consistency一致性、Availability高可用性、Partition tolerance分區(qū)耐受性），在任何的分布式系統(tǒng)只能同時(shí)滿足2點(diǎn)，沒(méi)辦法三者兼顧。MySQL的主從環(huán)境滿足Availability，且在半同步場(chǎng)景中數(shù)據(jù)可以做到數(shù)據(jù)強(qiáng)一致性，可以滿足Consistency。但是不能完全滿足Partition tolerance。

因此現(xiàn)在業(yè)內(nèi)對(duì)于事務(wù)（數(shù)據(jù)）丟失的處理有很多解決方案，如事務(wù)補(bǔ)償機(jī)制、半同步復(fù)制、雙寫(xiě)機(jī)制、異步消息隊(duì)列等等。甚至，還可以針對(duì)業(yè)務(wù)對(duì)CAP中哪兩者更有需求來(lái)選擇相應(yīng)的數(shù)據(jù)產(chǎn)品，如需要分區(qū)耐受性和高可用兼顧時(shí)，可以使用Cassandra等列式存儲(chǔ)。都可以達(dá)到業(yè)務(wù)相應(yīng)的數(shù)據(jù)一致性需求。

到此，相信大家對(duì)“MySQL數(shù)據(jù)庫(kù)丟失數(shù)據(jù)的場(chǎng)景分析”有了更深的了解，不妨來(lái)實(shí)際操作一番吧！這里是億速云網(wǎng)站，更多相關(guān)內(nèi)容可以進(jìn)入相關(guān)頻道進(jìn)行查詢，關(guān)注我們，繼續(xù)學(xué)習(xí)！