厙雯軒

民航氣象中心 北京 100015

引言

民航氣象中心采用MySQL數(shù)據(jù)庫存儲值班人員信息、值班日志信息、機場配置及業(yè)務(wù)系統(tǒng)配置等相關(guān)信息，并采用一主兩從的MySQL集群來增加數(shù)據(jù)存儲的響應速度和數(shù)據(jù)安全性，為更好地保證數(shù)據(jù)存儲的完整性及安全性，民航氣象中心使用主從復制功能進行MySQL主從數(shù)據(jù)庫之間的數(shù)據(jù)同步。此功能可以有效提升服務(wù)器讀寫性能，主庫出現(xiàn)異常時，可以快速切換至從庫，減少對業(yè)務(wù)運行的影響。民航氣象中心在使用此功能的過程中，出現(xiàn)了幾次MySQL主從數(shù)據(jù)庫復制延遲的現(xiàn)象，對值班人員使用造成了一定的影響。本文主要介紹MySQL數(shù)據(jù)庫的主從復制原理，主從延遲的排查方法、常見的MySQL主從復制故障原因、現(xiàn)象及處理方法。旨在未來出現(xiàn)MySQL主從復制故障時，可以快速進行故障判斷，及時進行故障處理，減少故障對業(yè)務(wù)運行帶來的影響。

1 MySQL數(shù)據(jù)庫簡介

MySQL是一個開源小型關(guān)聯(lián)式數(shù)據(jù)庫管理系統(tǒng)，具有體積小、速度快、成本低的特點，使用最常用的數(shù)據(jù)庫管理語言結(jié)構(gòu)化查詢語句（sql）進行數(shù)據(jù)庫管理[1]。對于使用者來說，MySQL涵蓋了常用的數(shù)據(jù)庫功能，且操作簡單便捷。對于開發(fā)人員來說，其開源的特性可由開發(fā)者根據(jù)自身或客戶的需求進行定制化處理。MySQL是以客戶機/服務(wù)器結(jié)構(gòu)的實現(xiàn)，由一個服務(wù)器守護程序MySQL和很多不同的客戶程序與庫組成，能夠快速、安全且有效地處理大量的數(shù)據(jù)。

隨著業(yè)務(wù)及數(shù)據(jù)存儲量的不斷增加，單臺MySQL服務(wù)器所提供的服務(wù)能力往往不能滿足業(yè)務(wù)的實際需求，為提升MySQL存取數(shù)據(jù)的響應速度及安全性，大型企業(yè)通常會搭建一個能夠同時實現(xiàn)高并發(fā)和負載均衡的MySQL集群服務(wù)器。為提升數(shù)據(jù)讀寫效率，需要對數(shù)據(jù)進行讀寫分離；為確保安全，需要對數(shù)據(jù)進行熱備份。為實現(xiàn)上述功能，MySQL提供了一個稱為“主從復制”的功能來實現(xiàn)多臺服務(wù)器之間的數(shù)據(jù)自動備份。

2 MySQL主從復制

2.1 主從復制原理

一般將提供增刪改服務(wù)的服務(wù)器稱作主用服務(wù)器，將提供數(shù)據(jù)查詢服務(wù)的一個或多個服務(wù)器稱作備用服務(wù)器。MySQL主從復制是指將MySQL的某一臺主機（Master）的數(shù)據(jù)復制到其他主機（Slave）上，并重新執(zhí)行一遍來實現(xiàn)。復制過程中一個服務(wù)器充當主服務(wù)器，而一個或多個其他服務(wù)器充當從服務(wù)器[2]。MySQL主從復制是基于主服務(wù)器在二進制日志跟蹤所有對數(shù)據(jù)庫的更改。因此，要進行復制，必須在主服務(wù)器上啟用二進制日志。

MySQL的主從復制遵從以下過程：

2.1.1 主用服務(wù)器（Master）進行數(shù)據(jù)更新操作后將相應的操作記錄到二進制日志（Binary Log）中。

2.1.2 備用服務(wù)器（Slave）將主用服務(wù)器（Master）的二進制日志復制到它的中繼日志（Relay Log）中。

2.1.3 備用服務(wù)器（Slave）重做中繼日志中的事件，進行備用服務(wù)器的數(shù)據(jù)更新。

2.2 主從復制優(yōu)點

使用主從復制，即對數(shù)據(jù)做了冗余，數(shù)據(jù)不會因為單個服務(wù)器宕機而導致丟失，使數(shù)據(jù)存儲更加安全。一主多從的服務(wù)器結(jié)構(gòu)，可設(shè)定不同用戶從不同的服務(wù)器讀取數(shù)據(jù)，可提升服務(wù)器讀取性能。當業(yè)務(wù)量增加時，可以增加從服務(wù)器的數(shù)量，減少業(yè)務(wù)增加對系統(tǒng)使用的影響。

綜上所述，使用MySQL的主從復制功能，可以提高數(shù)據(jù)安全性、提升服務(wù)器性能、提高服務(wù)器擴展性。

3 常見MySQL主從復制延遲原因

3.1 從服務(wù)器過多，導致主從同步延遲

一般選用一主兩從或者一主三從的集群模式進行數(shù)據(jù)存儲。若備用服務(wù)器數(shù)量過多，要復制的從節(jié)點數(shù)量過多，復制延遲的概率越高。

3.2 從庫讀取數(shù)據(jù)壓力大

進行讀寫分離后，用戶使用指定的服務(wù)器進行讀數(shù)據(jù)操作，若設(shè)置為主寫從讀，在sql查詢語句不合理、查詢數(shù)據(jù)量過大，從庫執(zhí)行該sql語句時間過長，會導致主從復制延遲。

3.3 服務(wù)器性能不足

服務(wù)器的硬件設(shè)備對MySQL數(shù)據(jù)庫的讀取有所影響。對于MySQL數(shù)據(jù)庫服務(wù)器，CPU、內(nèi)存、磁盤等的性能都會對MySQL數(shù)據(jù)庫讀寫造成影響。

3.4 數(shù)據(jù)表過大

大數(shù)據(jù)表特點是記錄行數(shù)巨大，單表超千萬；表數(shù)據(jù)文件巨大，超過10個G。對于大數(shù)據(jù)表，很難在短時間內(nèi)過濾出需要的數(shù)據(jù)，在大數(shù)據(jù)表中篩選數(shù)據(jù)會產(chǎn)生大量的IO，影響磁盤性能；需占用更長的時間建立索引表，導致主從復制延遲；修改表結(jié)構(gòu)時需要長時間鎖表，也會造成長時間的主從復制延遲。

3.5 事務(wù)運行時間長操作多

運行時間長，操作數(shù)據(jù)比較多的事務(wù)被稱作大事務(wù)；大事務(wù)會導致鎖定的數(shù)據(jù)多，回滾時間長，執(zhí)行時間長。鎖定太多數(shù)據(jù)，造成大量阻塞和鎖超時；回滾時所需時間比較長，且數(shù)據(jù)仍然會處于鎖定；如果執(zhí)行時間長，只有當主服務(wù)器全部執(zhí)行完寫入日志時，從服務(wù)器才會開始進行同步，因此會造成主從復制延遲。

3.6 鎖等待

對于 SQL 單線程來說，當遇到阻塞時就會一直等待，直到執(zhí)行成功才會繼續(xù)進行。如果某一時刻從庫因為查詢產(chǎn)生了鎖等待的情況，此時只有當前的操作執(zhí)行完成后才會進行下面的操作，同理也就產(chǎn)生了主從延遲的情況。

3.7 從庫服務(wù)器異常

在從庫復制數(shù)據(jù)過程中，從庫服務(wù)器出現(xiàn)異常宕機，可能造成relay log損壞，無法繼續(xù)進行復制。

4 MySQL主從復制延遲排查方法

4.1 檢查主從MySQL數(shù)據(jù)庫狀態(tài)

排查MySQL主從復制延遲需要使用root用戶登錄從數(shù)據(jù)庫，輸入MySQL命令，進入MySQL。通過監(jiān)控show slave statusG命令輸出的Seconds_Behind_Master參數(shù)值來判斷。當此值為NULL時，表示io_thread或是sql_thread任意一個發(fā)生了故障；當值為0時，表示主從復制正常；當值為正值時，表示主從復制已經(jīng)出現(xiàn)了延遲，且數(shù)字越大，從庫延遲越嚴重。Relay_mastar_log_file以及exec_master_log_pos值可以查看sql線程執(zhí)行帶的relay_log名及在日志中位置。

4.2 查看錯誤日志

錯誤日志記錄了MySQL主從復制的錯誤信息、記錄復制開始和停止的相關(guān)信息。當發(fā)現(xiàn)主從復制延遲時，在MySQL中輸入show variables like “l(fā)og_error”，找到error所在位置。查看日志可以看到從庫復制的錯誤信息，可以看到從庫停止復制時，IO讀取主庫的binlog截止位置和線程執(zhí)行的relay log的截止位置。

4.3 查看二進制日志文件

二進制日志文件包括主庫的binlog、從庫的relay log、從庫的binlog等。其中主庫binlog主要記錄了主庫執(zhí)行過的事務(wù)記錄，從庫的relay log主要記錄了從庫接收到的主庫binlog日志，從庫binlog主要記錄從庫執(zhí)行的事務(wù)記錄。正常狀態(tài)下，從庫正在接受的binlog的文件和位置均應大于主庫。

在MySQL中，可以輸入“show binlog events in‘文件名’；”查看二進制文件。二進制文件中，我們需要關(guān)注當前的binlog之前執(zhí)行過的所有g(shù)tid，用于定位具體gtid；錯誤發(fā)生時間，用于確定異常時執(zhí)行的語句。

5 MySQL主從復制延遲常見的報錯信息及解決方法

5.1 減少延遲的方法

5.1.1 架構(gòu)方面。對于讀寫數(shù)據(jù)庫實時性要求高的系統(tǒng)，如業(yè)務(wù)運行需要的系統(tǒng)，采取讀寫均在主庫的方式，此種讀寫方式可以提升讀寫速率，系統(tǒng)讀寫數(shù)據(jù)庫不受MySQL從庫的影響，即使出現(xiàn)MySQL主從復制延遲，也不會對系統(tǒng)運行造成影響。

對于讀數(shù)據(jù)庫實時性要求較低的系統(tǒng)，如讀取值班信息的系統(tǒng)，采取主從數(shù)據(jù)庫讀寫分離的方式，即主寫從讀，此種讀寫方式可分散主庫的壓力，減少主從復制延遲問題出現(xiàn)的概率。

5.1.2 硬件方面。服務(wù)器的性能越好，處理事務(wù)的速度越快，主從復制延遲越小。因此根據(jù)業(yè)務(wù)量，采用性能更好的密集型CPU、更大的內(nèi)存及具有更好的隨機讀取性能的固態(tài)硬盤可以有效提升服務(wù)器讀寫速率。

5.1.3 使用方面。使用合理的sql語句進行MySQL數(shù)據(jù)庫的增刪改查操作；設(shè)置生命周期，定時刪除超過生命周期的數(shù)據(jù)，減少大數(shù)據(jù)表的數(shù)量；減少同時執(zhí)行多條事務(wù)等都可以減少MySQL主從復制延遲問題出現(xiàn)[3]。

5.2 常見報錯及處理方法

5.2.1 報錯1062主鍵沖突。錯誤原因：此錯誤是從庫插入數(shù)據(jù)時，發(fā)生唯一性沖突導致的。報此錯誤表示從庫已經(jīng)有相同主鍵的數(shù)據(jù)，如果再插入相同主鍵值的數(shù)據(jù)會報1062錯誤。

排查方法：主庫MySQL中輸入show slave status G;找到Master_Log_File錯誤的事務(wù)，輸入show binlog events in‘master_bin’；查看該二進制文件中end_log_pos，可以看到從庫復制停在了哪一條插入語句。

解決方法：刪除造成主鍵沖突的數(shù)據(jù)，即可解決該問題。

5.2.2 報錯1032更改的數(shù)據(jù)不存在。錯誤原因：主庫中刪除了從庫中不存在的數(shù)據(jù)。

排查方法：查看主庫的二進制文件Master_Log_File中end_log_pos，可以看到報錯的事務(wù)項。

解決方法：先停止從庫同步主庫數(shù)據(jù)，跳過報錯的事務(wù)項后，啟動從庫同步主庫數(shù)據(jù)功能。

5.2.3 報錯13114主庫binlog丟失。錯誤原因：主庫binlog日志丟失，從庫在二進制日志索引文件中找不到第一個日志文件名。

解決方法：清空原主從配置，查到主庫當前最舊的binlog日志，找到GTID值，手動設(shè)置從庫的GTOD_PURGED值，連接主庫，重新啟動主從復制功能。

問題：由于舍棄了主庫丟失的binlog日志，導致部分同步事務(wù)丟失，主從數(shù)據(jù)庫中數(shù)據(jù)可能存在不一致[4]。

5.2.4 報錯13121從庫日志丟失。錯誤原因：從relay log日志丟失。

解決方法：根據(jù)Relay_Master_Log_File和Exec_Master_Log_Pos、Executed_Gtid_Set的值，找到從庫最后完成的事務(wù)所對應的binlog文件和位置，情況從庫的gtid及從庫信息，連接主庫，啟動主從復制功能。

6 結(jié)束語

隨著民航氣象中心業(yè)務(wù)的逐步增加，存儲的數(shù)據(jù)種類越來越多，根據(jù)數(shù)據(jù)特點選擇了不同的數(shù)據(jù)庫進行存儲。MySQL數(shù)據(jù)庫便于查詢使用、性能強大、支持多操作系統(tǒng)運行、性價比高等優(yōu)點，同時具有不支持復雜的查詢條件、不能有效的存儲大量數(shù)據(jù)的缺點。民航氣象中心的系統(tǒng)配置、人員信息等數(shù)據(jù)的數(shù)據(jù)量較為固定、日常改動較低，因此存放在MySQL數(shù)據(jù)庫中，并采用MySQL一主兩從、主從復制功能來保證數(shù)據(jù)存儲的完整性。

本文針對MySQL集群主從復制延遲的常見原因、現(xiàn)象及處理方法進行介紹，希望為未來民航氣象中心的MySQL集群主從復制延遲故障發(fā)生時的及時處理，提供思路與解決辦法。