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

創(chuàng)新互聯(lián)建站憑借專業(yè)的設計團隊扎實的技術支持、優(yōu)質高效的服務意識和豐厚的資源優(yōu)勢，提供專業(yè)的網(wǎng)站策劃、做網(wǎng)站、成都網(wǎng)站建設、網(wǎng)站優(yōu)化、軟件開發(fā)、網(wǎng)站改版等服務，在成都十多年的網(wǎng)站建設設計經(jīng)驗，為成都數(shù)千家中小型企業(yè)策劃設計了網(wǎng)站。

一、主從復制的模式和原理解讀

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

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

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

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

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

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

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

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

• slave節(jié)點SQL線程

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

要實現(xiàn)主從復制，必須打開Master 節(jié)點的binary log功能。因為整個復制過程實際上就是Slave 節(jié)點從Master 節(jié)點獲取該日志然后再在自己身上完全順序的執(zhí)行日志中所記錄的各種操作。

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

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

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

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

①異步模式master節(jié)點不會主動push binlog到slave節(jié)點，有可能導致fail over情況下，也許slave節(jié)點沒有即時地將最新的binlog同步到本地。

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

③全同步模式

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

二、DBbrian如何判斷主從延遲

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

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

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

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

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

①利用seconds_ behind_ master的值

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

在某些場景中也會出現(xiàn)seconds_ behind_ master對復制延遲表征不準確的情況，例如：

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

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

②通過對比位點

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

③對比GTID集合

對于開啟GTID的數(shù)據(jù)庫實例，DBbrain會使用對比GTID集合的方式來檢測復制延遲是否存在。（Auto_Position=1，表示這對主備關系使用了GTID協(xié)議）

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

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

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

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

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

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

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

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

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

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

• 備庫事務阻塞復制：備庫/只讀實例長事務或未提交事務導致復制延遲或中止的情況容易被忽視。比如備庫開啟事務，執(zhí)行查詢后并未提交；這個時候主庫過來的DDL語句會等待MDL鎖；而DDL語句會繼續(xù)阻塞后續(xù)過來的其它事件執(zhí)行。

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

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

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

另外通過會話快照也可以直接看到當前被阻塞的DDL語句：

實例上查看長時間未提交的事務：

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

四、主從延遲的妙用

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

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

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

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

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

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

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

名稱欄目：值得深入了解的MySQL故障有哪些-創(chuàng)新互聯(lián)
URL分享：http://weahome.cn/article/dpcgod.html