MySQL數(shù)據(jù)庫(kù)主從復(fù)制原理及常見故障介紹

厙雯軒

民航氣象中心 北京 100015

引言

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

1 MySQL數(shù)據(jù)庫(kù)簡(jiǎn)介

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

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

2 MySQL主從復(fù)制

2.1 主從復(fù)制原理

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

MySQL的主從復(fù)制遵從以下過程：

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

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

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

2.2 主從復(fù)制優(yōu)點(diǎn)

使用主從復(fù)制，即對(duì)數(shù)據(jù)做了冗余，數(shù)據(jù)不會(huì)因?yàn)閱蝹€(gè)服務(wù)器宕機(jī)而導(dǎo)致丟失，使數(shù)據(jù)存儲(chǔ)更加安全。一主多從的服務(wù)器結(jié)構(gòu)，可設(shè)定不同用戶從不同的服務(wù)器讀取數(shù)據(jù)，可提升服務(wù)器讀取性能。當(dāng)業(yè)務(wù)量增加時(shí)，可以增加從服務(wù)器的數(shù)量，減少業(yè)務(wù)增加對(duì)系統(tǒng)使用的影響。

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

3 常見MySQL主從復(fù)制延遲原因

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

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

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

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

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

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

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

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

3.5 事務(wù)運(yùn)行時(shí)間長(zhǎng)操作多

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

3.6 鎖等待

對(duì)于 SQL 單線程來說，當(dāng)遇到阻塞時(shí)就會(huì)一直等待，直到執(zhí)行成功才會(huì)繼續(xù)進(jìn)行。如果某一時(shí)刻從庫(kù)因?yàn)椴樵儺a(chǎn)生了鎖等待的情況，此時(shí)只有當(dāng)前的操作執(zhí)行完成后才會(huì)進(jìn)行下面的操作，同理也就產(chǎn)生了主從延遲的情況。

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

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

4 MySQL主從復(fù)制延遲排查方法

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

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

4.2 查看錯(cuò)誤日志

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

4.3 查看二進(jìn)制日志文件

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

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

5 MySQL主從復(fù)制延遲常見的報(bào)錯(cuò)信息及解決方法

5.1 減少延遲的方法

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

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

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

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

5.2 常見報(bào)錯(cuò)及處理方法

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

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

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

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

排查方法：查看主庫(kù)的二進(jìn)制文件Master_Log_File中end_log_pos，可以看到報(bào)錯(cuò)的事務(wù)項(xiàng)。

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

5.2.3 報(bào)錯(cuò)13114主庫(kù)binlog丟失。錯(cuò)誤原因：主庫(kù)binlog日志丟失，從庫(kù)在二進(jìn)制日志索引文件中找不到第一個(gè)日志文件名。

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

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

5.2.4 報(bào)錯(cuò)13121從庫(kù)日志丟失。錯(cuò)誤原因：從relay log日志丟失。

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

6 結(jié)束語(yǔ)

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

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