值得深入了解的MySQL故障有哪些

這篇文章將為大家詳細(xì)講解有關(guān)值得深入了解的MySQL故障有哪些，文章內(nèi)容質(zhì)量較高，因此小編分享給大家做個(gè)參考，希望大家閱讀完這篇文章后對(duì)相關(guān)知識(shí)有一定的了解。

一、主從復(fù)制的模式和原理解讀

MySQL主從復(fù)制可以簡(jiǎn)單解釋為數(shù)據(jù)可以從一個(gè)MySQL數(shù)據(jù)庫(kù)服務(wù)器主節(jié)點(diǎn)復(fù)制到一個(gè)或多個(gè)從節(jié)點(diǎn)。MySQL 默認(rèn)采用異步復(fù)制方式，這樣從節(jié)點(diǎn)不用一直訪問(wèn)主服務(wù)器來(lái)更新自己的數(shù)據(jù)，數(shù)據(jù)的更新可以在遠(yuǎn)程連接上進(jìn)行，從節(jié)點(diǎn)可以復(fù)制主數(shù)據(jù)庫(kù)中的所有數(shù)據(jù)庫(kù)或者特定的數(shù)據(jù)庫(kù)或者特定的表。

MySQL 主從復(fù)制主要用途包括讀寫(xiě)分離、 數(shù)據(jù)實(shí)時(shí)備份（當(dāng)系統(tǒng)中某個(gè)節(jié)點(diǎn)發(fā)生故障時(shí)，可以方便的故障切換）、 架構(gòu)擴(kuò)展、高可以用HA等。

MySQL 主從復(fù)制的主要形式包括：一主多從、多主一從、雙主復(fù)制、級(jí)聯(lián)復(fù)制（部分slave的數(shù)據(jù)同步不連接master節(jié)點(diǎn)，而是連接slave節(jié)點(diǎn)。因?yàn)槿绻鹠aster節(jié)點(diǎn)有太多的從節(jié)點(diǎn)，就會(huì)損耗一部分性能用于replication，那么可以讓一些slave節(jié)點(diǎn)連接主節(jié)點(diǎn)，其它從節(jié)點(diǎn)作為二級(jí)或者三級(jí)與slave節(jié)點(diǎn)連接）等。

MySQL主從復(fù)制涉及到三個(gè)線程，一個(gè)運(yùn)行在master節(jié)點(diǎn)（log dump thread），其余兩個(gè)(I/O thread, SQL thread)運(yùn)行在slave節(jié)點(diǎn)，如下圖所示:

• master節(jié)點(diǎn) binary log dump 線程

當(dāng)salve節(jié)點(diǎn)連接master節(jié)點(diǎn)時(shí)，master節(jié)點(diǎn)會(huì)創(chuàng)建一個(gè)log dump 線程，用于發(fā)送binlog的內(nèi)容。在讀取binlog中的操作時(shí)，此線程會(huì)對(duì)主節(jié)點(diǎn)上的binlog加鎖，當(dāng)讀取完成，在發(fā)送給slave節(jié)點(diǎn)之前，鎖會(huì)被釋放。

• slave節(jié)點(diǎn)I/O線程

當(dāng)slave節(jié)點(diǎn)上執(zhí)行start slave命令之后，slave節(jié)點(diǎn)會(huì)創(chuàng)建一個(gè)I/O線程用來(lái)連接master節(jié)點(diǎn)，請(qǐng)求master節(jié)點(diǎn)中更新的binlog。I/O線程接收到master節(jié)點(diǎn)binlog dump 進(jìn)程發(fā)來(lái)的更新之后，保存在本地relay-log中。

• slave節(jié)點(diǎn)SQL線程

SQL線程負(fù)責(zé)讀取relay log中的內(nèi)容，解析成具體的操作并執(zhí)行，最終保證主從數(shù)據(jù)的一致性。

要實(shí)現(xiàn)主從復(fù)制，必須打開(kāi)Master 節(jié)點(diǎn)的binary log功能。因?yàn)檎麄€(gè)復(fù)制過(guò)程實(shí)際上就是Slave 節(jié)點(diǎn)從Master 節(jié)點(diǎn)獲取該日志然后再在自己身上完全順序的執(zhí)行日志中所記錄的各種操作。

• slave節(jié)點(diǎn)上的I/O 進(jìn)程連接主節(jié)點(diǎn)，并請(qǐng)求從指定日志文件的指定位置之后的日志內(nèi)容；

• master節(jié)點(diǎn)接收到slave節(jié)點(diǎn)的I/O請(qǐng)求后，通過(guò)復(fù)制的I/O進(jìn)程根據(jù)請(qǐng)求信息讀取指定日志指定位置之后的日志信息，返回給slave節(jié)點(diǎn)。返回信息中除了日志所包含的信息之外，還包括本次返回的信息的binlog file 的以及binlog position。slave節(jié)點(diǎn)的I/O進(jìn)程接收到內(nèi)容后，將接收到的日志內(nèi)容更新到本機(jī)的relay log中，并將讀取到的binary log文件名和位置保存到master-info 文件中，在下一次讀取的時(shí)能告訴master節(jié)點(diǎn)需要從某個(gè)binlog的哪個(gè)位置開(kāi)始往后的日志；

• slave節(jié)點(diǎn) 的 SQL線程檢測(cè)到relay-log 中新增加了內(nèi)容后，會(huì)將relay-log的內(nèi)容解析成在master節(jié)點(diǎn)上實(shí)際執(zhí)行過(guò)的操作，并在本數(shù)據(jù)庫(kù)中執(zhí)行。

MySQL主從復(fù)制模式分為異步模式、半同步模式、全同步模式。

①異步模式master節(jié)點(diǎn)不會(huì)主動(dòng)push binlog到slave節(jié)點(diǎn)，有可能導(dǎo)致fail over情況下，也許slave節(jié)點(diǎn)沒(méi)有即時(shí)地將最新的binlog同步到本地。

②半同步模式半同步復(fù)制模式可以確保至少有一個(gè)slave節(jié)點(diǎn)（可配置）在接受完master節(jié)點(diǎn)發(fā)送的binlog日志文件并寫(xiě)入到中relay log后，返回給主節(jié)點(diǎn)一個(gè)ack信號(hào)，告訴master節(jié)點(diǎn)已經(jīng)接收完日志，這時(shí)主節(jié)點(diǎn)線程才返回給當(dāng)前session提交信息。

③全同步模式

全同步模式是指slave節(jié)點(diǎn)接收到master節(jié)點(diǎn)發(fā)送的binlog日志文件并寫(xiě)入到中relay log，并且完成回放之后，返回給主節(jié)點(diǎn)一個(gè)ack信號(hào)，master節(jié)點(diǎn)才會(huì)向客戶端返回成功。

二、DBbrian如何判斷主從延遲

從前面講到的的主從復(fù)制原理中不難發(fā)現(xiàn)，MySQL在使用“異步”和“半同步”的復(fù)制模式下可能會(huì)出現(xiàn)主從延時(shí)。MySQL數(shù)據(jù)庫(kù)復(fù)制延遲會(huì)給業(yè)務(wù)帶來(lái)一系列嚴(yán)重問(wèn)題：讀寫(xiě)分離架構(gòu)不利于高實(shí)時(shí)一致性業(yè)務(wù)；高可靠架構(gòu)設(shè)計(jì)中也難以確定RTO/RPO指標(biāo)。檢測(cè)，定位和解決MySQL主從復(fù)制延遲問(wèn)題一直是DBA重點(diǎn)工作之一。

數(shù)據(jù)庫(kù)智能管家DBbrain為云上用戶提供了7*24小時(shí)數(shù)據(jù)庫(kù)智能運(yùn)維服務(wù)，對(duì)于“主從復(fù)制”延遲的故障，DBbrain又是怎么診斷的呢？接下來(lái)就為大家一起揭秘這一問(wèn)題。

那么，首先簡(jiǎn)要的介紹一下主從延遲（復(fù)制延遲）是如何發(fā)生的。

MySQL備庫(kù)復(fù)制會(huì)啟動(dòng)兩類線程：IO線程負(fù)責(zé)連接主庫(kù)讀取binlog事件，然后將其寫(xiě)入本地binlog文件；SQL線程則從復(fù)制得到的binlog文件中讀取事件apply到備庫(kù)?？梢酝ㄟ^(guò) "show slave status" 查看備庫(kù)的復(fù)制狀態(tài)。其中 SecondsBehindMaster 值表示延遲時(shí)間，單位為秒。將主庫(kù)執(zhí)行SQL語(yǔ)句時(shí)刻標(biāo)記為T(mén)1，備庫(kù)執(zhí)行SQL的時(shí)刻標(biāo)記為T(mén)2，這兩個(gè)時(shí)刻之間的差值就是主備延遲時(shí)間。不過(guò)僅僅從 "show slave status" 結(jié)果看到的延遲時(shí)間可能”不準(zhǔn)“。該值除精度問(wèn)題外，還和主庫(kù)事務(wù)相關(guān)。如果在主庫(kù)開(kāi)啟事務(wù)執(zhí)行了IUD操作，但是commit有一分鐘滯后，那么這個(gè)時(shí)間差也會(huì)在備庫(kù)復(fù)制延遲狀態(tài)中體現(xiàn)出來(lái)。我們通?？吹絺鋷?kù)延遲性能曲線始終存在1，2秒的延遲波動(dòng)，大概率是主庫(kù)事務(wù)導(dǎo)致的；若從事務(wù)提交的時(shí)間點(diǎn)算，大延遲并不存在；在主備切換時(shí)為了確保主備數(shù)據(jù)一致，需要確認(rèn)主備binlog日志文件和和位點(diǎn)一致后才能操作。

數(shù)據(jù)庫(kù)智能管家DBbrain針對(duì)主從延遲（復(fù)制延遲）的異常場(chǎng)景采用的發(fā)現(xiàn)機(jī)制和方式主要可以分為以下三種：

①利用seconds_ behind_ master的值

在show slave status結(jié)果里的seconds_ behind_ master（In essence, this field measures the time difference in seconds between the slave SQL thread and the slave I/O thread.)的值可以用來(lái)衡量主備延遲時(shí)間的長(zhǎng)短（單位是秒）。判斷seconds_ behind_ master 是否已經(jīng)等于0，如果這個(gè)參數(shù)等于0，表示主從復(fù)制基本上無(wú)延遲。seconds_ behind__master是通過(guò)比較sql_thread執(zhí)行的event的timestamp和io_thread復(fù)制好的event的timestamp進(jìn)行比較，而得到的差值。

在某些場(chǎng)景中也會(huì)出現(xiàn)seconds_ behind_ master對(duì)復(fù)制延遲表征不準(zhǔn)確的情況，例如：

• 在網(wǎng)絡(luò)環(huán)境特別差的情況下，I/O thread同步很慢，每次同步過(guò)來(lái)，SQL thread就能立即執(zhí)行，這樣，在slave上查看到的seconds_ behind__master是0，而真正的，slave已經(jīng)落后master有一定距離。

• 有一段時(shí)間沒(méi)有數(shù)據(jù)提交，slave I/O thread time和slave SQL thread time都保持在舊值，比如T(但事實(shí)上master上的時(shí)間已經(jīng)到T+I了)，這個(gè)時(shí)候主庫(kù)出現(xiàn)提交，slave I/O開(kāi)始去和master同步binlog，slave I/O thread time更新到T+I，但是slave SQL thread time保持在T值，這時(shí)的seconds_behind_master=I，但其實(shí)是否出現(xiàn)延遲是不確定的。

②通過(guò)對(duì)比位點(diǎn)

Master_Log_File和Read_Master_Log_Pos，表示的是讀到的主庫(kù)的最新位點(diǎn)。Relay_Master_Log_File和Exec_Master_Log_Pos，表示的是備庫(kù)執(zhí)行的最新位點(diǎn)。如果Master_Log_File和Relay_Master_Log_File、Read_Master_Log_Pos和Exec_Master_Log_Pos這兩組值完全相同，就表示接收到的日志已經(jīng)同步完成。

③對(duì)比GTID集合

對(duì)于開(kāi)啟GTID的數(shù)據(jù)庫(kù)實(shí)例，DBbrain會(huì)使用對(duì)比GTID集合的方式來(lái)檢測(cè)復(fù)制延遲是否存在。（Auto_Position=1，表示這對(duì)主備關(guān)系使用了GTID協(xié)議）

• Retrieved_Gtid_Set，是備庫(kù)收到的所有日志的GTID集合；

• Executed_Gtid_Set, 是備庫(kù)所有已經(jīng)執(zhí)行完成的GTID集合。

如果這兩個(gè)集合相同，也表示備庫(kù)接收到的日志都已經(jīng)同步完成。比判斷seconds_ behind_ master 是否為0更準(zhǔn)確。

三、DBbrian一鍵優(yōu)化的案例

通常IO線程不會(huì)引起數(shù)據(jù)復(fù)制的較大延遲，除非網(wǎng)絡(luò)問(wèn)題導(dǎo)致連接斷開(kāi)，又或者網(wǎng)絡(luò)延遲以及帶寬存在瓶頸。自動(dòng)化環(huán)境中主庫(kù)binlog被刪除或損壞也是導(dǎo)致IO線程斷開(kāi)的一種原因。在這里主要對(duì)SQL線程應(yīng)用event的延遲問(wèn)題展開(kāi)分析：

• 備庫(kù)/只讀實(shí)例資源不夠：備庫(kù)/只讀實(shí)例除了需要應(yīng)用數(shù)據(jù)變更之外，在承擔(dān)查詢?nèi)蝿?wù)時(shí)，可能需要更多的資源。當(dāng)發(fā)現(xiàn)備庫(kù)存在延遲后，需要首先確認(rèn)備庫(kù)的資源使用情況。

• 主庫(kù)高并發(fā)數(shù)據(jù)更新：業(yè)務(wù)高峰期，主庫(kù)大量并發(fā)的插入、刪除和更新操作，QPS明顯增加，產(chǎn)生大量的binlog文件。這個(gè)時(shí)候備庫(kù)應(yīng)用event的速度跟不，延遲產(chǎn)生。 備庫(kù)應(yīng)用event的方式從最初的單線程演變和優(yōu)化成當(dāng)前的并行復(fù)制。其中并行復(fù)制的實(shí)現(xiàn)方式在不同版本以及廠商之間存在差異，比如基于Schema并行復(fù)制，基于表并行復(fù)制，基于commit-parent的并行復(fù)制，基于lock-interval的并行復(fù)制等。在備庫(kù)開(kāi)啟組提交的并行復(fù)制，可以提高回放binlog性能減少延遲。

  
  

• 主庫(kù)單表大量更新：在row模式下，主庫(kù)一個(gè)sql語(yǔ)句的數(shù)據(jù)庫(kù)更改，會(huì)變成多個(gè)event復(fù)制到備庫(kù)。建議開(kāi)發(fā)人員盡量分解大事務(wù)為小事務(wù)，并及時(shí)提交。另外也見(jiàn)過(guò)一個(gè)用戶在主庫(kù)循環(huán)更新單個(gè)表數(shù)10萬(wàn)條數(shù)據(jù)的時(shí)間戳。這種場(chǎng)景備庫(kù)延遲會(huì)越來(lái)越大，備庫(kù)始終追不上主庫(kù)。最后只能建議用戶更改應(yīng)用設(shè)計(jì)。

• 主庫(kù)DDL操作：大表DDL語(yǔ)句復(fù)制到備庫(kù)執(zhí)行時(shí)，會(huì)導(dǎo)致并行復(fù)制失效，后續(xù)事件無(wú)法更新，從而延遲累積。這種場(chǎng)景會(huì)看到監(jiān)控曲線成45度斜率增長(zhǎng)。在這里重點(diǎn)提一下，很多開(kāi)發(fā)人員喜歡在主庫(kù)頻繁的使用optimize table操作，但是忽視了該語(yǔ)句容易導(dǎo)致備庫(kù)延遲的問(wèn)題。由于DDL導(dǎo)致備庫(kù)延遲的問(wèn)題比較容易定位：通過(guò) "show processlist" 會(huì)話快照可以看到正在執(zhí)行的DDL語(yǔ)句。針對(duì)大表DDL導(dǎo)致延遲問(wèn)題，有經(jīng)驗(yàn)的DBA會(huì)選擇關(guān)閉SQLLOGBIN參數(shù)后，備庫(kù)手工執(zhí)行DDL語(yǔ)句。

  
  

• 備庫(kù)執(zhí)行SQL語(yǔ)句慢：在row模式下，缺少主鍵或適當(dāng)?shù)乃饕菍?dǎo)致SQL執(zhí)行慢的主要原因。線上云環(huán)境中，若用戶創(chuàng)建表時(shí)未指定主鍵，數(shù)據(jù)庫(kù)通常會(huì)自動(dòng)引入隱式主鍵來(lái)避免該問(wèn)題。

  
  

• 備庫(kù)事務(wù)阻塞復(fù)制：備庫(kù)/只讀實(shí)例長(zhǎng)事務(wù)或未提交事務(wù)導(dǎo)致復(fù)制延遲或中止的情況容易被忽視。比如備庫(kù)開(kāi)啟事務(wù)，執(zhí)行查詢后并未提交；這個(gè)時(shí)候主庫(kù)過(guò)來(lái)的DDL語(yǔ)句會(huì)等待MDL鎖；而DDL語(yǔ)句會(huì)繼續(xù)阻塞后續(xù)過(guò)來(lái)的其它事件執(zhí)行。

下面選取其中一類問(wèn)題通過(guò)場(chǎng)景化的描述簡(jiǎn)單的還原整個(gè)優(yōu)化過(guò)程的邏輯。針對(duì)只讀實(shí)例開(kāi)啟事務(wù)執(zhí)行查詢后，不提交事務(wù)。（注意：開(kāi)始事務(wù)只做查詢是常見(jiàn)的錯(cuò)誤使用方式。這種操作不一定是開(kāi)發(fā)人員顯示的寫(xiě)在代碼中，是所使用的框架導(dǎo)致的。）

此時(shí)我們可以從監(jiān)控?cái)?shù)據(jù)看到備庫(kù)延遲產(chǎn)生：

在只讀實(shí)例上，我們可以通過(guò)一系列命令查看到復(fù)制延遲的原因。備庫(kù)復(fù)制狀態(tài)信息中，可以看到當(dāng)前SQL執(zhí)行狀態(tài)為 "Waiting for table metadata lock"。

另外通過(guò)會(huì)話快照也可以直接看到當(dāng)前被阻塞的DDL語(yǔ)句：

實(shí)例上查看長(zhǎng)時(shí)間未提交的事務(wù)：

數(shù)據(jù)庫(kù)智能管家DBbrain會(huì)主動(dòng)發(fā)現(xiàn)原因，提交或kill會(huì)話后，延遲立即消失:

四、主從延遲的妙用

主從延遲（復(fù)制延遲）雖然出現(xiàn)在大多數(shù)場(chǎng)景中對(duì)業(yè)務(wù)都會(huì)帶來(lái)消極影響，但是在一些場(chǎng)景，人為手動(dòng)設(shè)置“延遲”，能夠完美的解決一些特殊的業(yè)務(wù)需求。比如在將日志及時(shí)復(fù)制到備庫(kù)，但有意的不立即應(yīng)用的實(shí)現(xiàn)方式在容災(zāi)系統(tǒng)中經(jīng)常采用。容災(zāi)切換概率很低，但是可以利用現(xiàn)有的資源及時(shí)“回滾”誤操作。復(fù)制延遲是非常有價(jià)值的“撤消”選項(xiàng)。例如，如果有人意外刪除了MySQL數(shù)據(jù)庫(kù)或表，則可以輕松地從延遲的MySQL從站恢復(fù)這些數(shù)據(jù)庫(kù)和表。MySQL已經(jīng)支持 MASTER_DELAY 參數(shù)來(lái)實(shí)現(xiàn)類似功能。

五、騰訊云MySQL基于主從復(fù)制的優(yōu)化

MySQL在同步復(fù)制下耗時(shí)主要包含三個(gè)部分。第一個(gè)是SQL部分，第二是存儲(chǔ)引擎，第三部分是復(fù)制。和異步相比，我們重點(diǎn)優(yōu)化第三部分的延時(shí)。復(fù)制延時(shí)主要有兩部分：第一部分是binlog網(wǎng)絡(luò)傳輸過(guò)去的耗時(shí)，第二部分是slave落地binlog的延時(shí)。binlog傳輸耗時(shí)取決于網(wǎng)絡(luò)RTT值。我們的優(yōu)化重要集中在slave落地binlog的延時(shí)上。

在這次優(yōu)化過(guò)程中，做了一個(gè)測(cè)試進(jìn)行定量分析。在全Cache下MySQL異步的情況下，單事務(wù)耗時(shí)是3.37ms，也就是說(shuō)它的SQL加引擎一共耗時(shí)3.37ms，但是我們發(fā)現(xiàn)在半同步的情況下延時(shí)就變成了8.33ms，發(fā)現(xiàn)RTT是2.6ms，那么slave落地binlog就花費(fèi)了1.9ms，那1.9ms是否合理呢?接著做了一個(gè)測(cè)試，模擬slave落地binlog的操作，發(fā)現(xiàn)只需要0.13ms，這里面其實(shí)有接近1.8ms的優(yōu)化空間。

第二個(gè)就是如何提升系統(tǒng)吞吐。當(dāng)單個(gè)事務(wù)的延時(shí)降下來(lái)后，是不是就意味著整個(gè)系統(tǒng)的吞吐就上來(lái)了?這也未必，整個(gè)吞吐來(lái)說(shuō)取決于兩個(gè)因素，一個(gè)是支持的并發(fā)數(shù)，另外一個(gè)就是單個(gè)事務(wù)的延時(shí)。假如有一些公共資源存在很大的競(jìng)爭(zhēng)，那就可能存在并發(fā)數(shù)上不來(lái)了的問(wèn)題，我們發(fā)現(xiàn)master的binlog發(fā)送/響應(yīng)線程是有很大的優(yōu)化空間的。所以我們就基于這兩個(gè)方面去做了系統(tǒng)吞吐的優(yōu)化。

如何解決slave落地binlog的耗時(shí)呢?我們當(dāng)時(shí)分析MySQL slave的IO線程接收binlog耗時(shí)的主要瓶頸有三個(gè)：第一個(gè)就是鎖沖突，IO/SQL線程間的鎖沖突，如元數(shù)據(jù)文件鎖;第二部分就是小IO消耗，IO線程離散小磁盤(pán)IO消耗過(guò)多的IOPS;第三個(gè)問(wèn)題是串行化，IO線程接收和落盤(pán)操作串行。

關(guān)于值得深入了解的MySQL故障有哪些就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，可以學(xué)到更多知識(shí)。如果覺(jué)得文章不錯(cuò)，可以把它分享出去讓更多的人看到。