復制的問題和解決方案

中斷MySQL的復制并不是件難事。因為實現(xiàn)簡單，配置相當容易，但也意味著有很多方式會導致復制停止，陷入混亂并中斷。這邊描述了一些比較普遍的問題，討論如何重現(xiàn)這些問題，以及當遇到這些問題時如何解決或者阻止其發(fā)生。

數(shù)據(jù)損壞或丟失的錯誤

由于各種各樣的原因，MySQL的復制并不能很好地從服務(wù)器崩潰、掉電、磁盤損壞、 內(nèi)存或網(wǎng)絡(luò)錯誤中恢復。遇到這些問題時幾乎可以肯定都需要從某個點開始重啟復制。

大部分由于非正常關(guān)機后導致的復制問題都是由于沒有把數(shù)據(jù)及時地刷到磁盤。下面是. 意外關(guān)閉服務(wù)器時可能會碰到的情況。

主庫意外關(guān)閉

• 如果沒有設(shè)置主庫的sync_binlog 選項，就可能在崩潰前沒有將最后的幾個二進制日志事件刷新到磁盤中。備庫I/O線程因此也可一直處于讀不到尚未寫入磁盤的事件的狀態(tài)中。當主庫重新啟動時，備庫將重連到主庫并再次嘗試去讀該事件，但主庫會告訴備庫沒有這個二進制日志偏移量。二進制日志轉(zhuǎn)儲線程通常很快，因此這 種情況并不經(jīng)常發(fā)生。

• 解決這個問題的方法是指定備庫從下一個二進制日志的開頭讀日志。但是一些日志事件將永久地丟失，建議使用Percona Toolkit中的pt-table-checksum工具來檢查主備一致性，以便于修復?？梢酝ㄟ^在主庫開啟sync_binlog 來避免事件丟失。 即使開啟了sync_binlog,MyISAM表的數(shù)據(jù)仍然可能在崩潰的時候損壞，對于 InnoDB事務(wù),如果innodb_flush_log_at_trx_commit 沒有設(shè)為1 ,也可能丟失數(shù)據(jù)(但數(shù)據(jù)不會損壞)。

備庫意外關(guān)閉

• 當備庫在一次非計劃中的關(guān)閉后重啟時，會去讀master.info文件以找到上次停止復制的位置。不幸的是，該文件并沒有同步寫到磁盤，文件中存儲的信息可能是錯誤的。備庫可能會嘗試重新執(zhí)行一些二進制日志事件，這可能會導致唯一索引錯誤。 除非能確定備庫在哪里停止(通常不太可能)，否則唯一的辦法就是忽略那些錯誤。 Percona Toolkit中的pt-slave-restart工具可以幫助完成這一點。

• 如果使用的都是InnoDB表，可以在重啟后觀察MySQL錯誤日志。InnoDB在恢復過程中會打印出它的恢復點的二進制日志坐標?？梢允褂眠@個值來決定備庫指向主庫的偏移量。Percona Server提供了一個新的特性，可以在恢復的過程中自動將這些 信息提取出來,并更新masterinfo文件,從根本上使得復制能夠協(xié)調(diào)好備庫上的事務(wù)。MySQL 5.5也提供了一些選項來控制如何將master.info和其他文件刷新到磁盤，這有助于減少這些問題。

除了由于MySQL非正常關(guān)閉導致的數(shù)據(jù)丟失外，磁盤上的二進制日志或中繼日志文件損壞并不罕見。下面是一些更普遍的場景:

主庫上的二進制日志損壞

• 如果主庫上的二進制日志損壞，除了忽略損壞的位置外你別無選擇?？梢栽谥鲙焐蠄?zhí)行FLUSH LOGS命令，這樣主庫會開始一個新的日志文件，然后將備庫指向該文件的開始位置。也可以試著去發(fā)現(xiàn)損壞區(qū)域的結(jié)束位置。某些情況下可以通過SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1 來忽略一個損壞的事件。如果有多個損壞的事件，就需要重復該步驟，直到跳過所有損壞的事件。但如果有太多的損壞事件， 這么做可能就沒有意義了。損壞的事件頭會阻止服務(wù)器找到下一個事件。這種情況下， 可能不得不手動地去找到下一個完好的事件。

備庫上的中繼日志損壞

• 如果主庫上的日志是完好的，就可以通過CHANGE MASTER TO命令丟棄并重新獲取損壞的事件。只需要將備庫指向它當前正在復制的位置(Relay_Master_Log_File/ Exec_Master_Log_Pos) 。這會導致備庫丟棄所有在磁盤上的中繼日志。就這一點而言， MySQL 5.5做了一些改進，它能夠在崩潰后自動重新獲取中繼日志。

二進制日志與InnoDB事務(wù)日志不同步

• 當主庫崩潰時，InnoDB可能將一個事務(wù)標記為已提交，此時該事務(wù)可能還沒有記錄到二進制日志中。除非是某個備庫的中繼日志已經(jīng)保存，否則沒有任何辦法恢復丟失的事務(wù)。在MySQL 5.0版本可以設(shè)置sync_binlog 選項來防止該問題，對于更早 的MySQL 4.1可以設(shè)置sync_binlog和safe_binlog選項。

當一個二進制日志損壞時，能恢復多少數(shù)據(jù)取決于損壞的類型，有幾種比較常見的類型:

數(shù)據(jù)改變，但事件仍是有效的SQL

• 不幸的是，MySQL甚至無法察覺這種損壞。因此最好還是經(jīng)常檢查備庫的數(shù)據(jù)是否正確。在MySQL未來的版本中可能會被修復。

數(shù)據(jù)改變并且事件是無效的SQL

• 這種情況可以通過mysqlbinlog提取出事件并看到一些錯亂的數(shù)據(jù)，例如;

UPDATE tbl SET col????

可以通過增加偏移量的方式來嘗試找到下一個事件，這樣就可以只忽略這個損壞的事件。

數(shù)據(jù)遺漏并且/或者事件的長度是錯誤的

• 這種情況下，mysqlbinlog可能會發(fā)生錯誤退出或者直接崩潰，因為它無法讀取事件, 并且找不到下一個事件的開始位置。

某些事件已經(jīng)損壞或被覆蓋，或者偏移量已經(jīng)改變并且下一個事件的起始偏移量也是錯誤的.

• 同樣的，這種情況下mysqlbinlog也起不了多少作用。

當損壞非常嚴重，通過mysqlbinlog已經(jīng)無法獲取日志事件時，就不得不進行一些十六進制的編輯或者通過一些煩瑣的技術(shù)來找到日志事件的邊界。這通常并不困難，因為有一些可辨識的標記會分割事件。

InnoDB加鎖引起的鎖征用

正常情況下，InnoDB的讀操作是非阻塞的，但在某些情況下需要加鎖。特別是在使用 基于語句的復制方式時，執(zhí)行INSERT.. .SELECT操作會鎖定源表上的所有行。MySQL需要加鎖以確保該語句的執(zhí)行結(jié)果在主庫和備庫上是一致的。實際上，加鎖導致主庫上 的語句串行化，以確保和備庫上執(zhí)行的方式相符。

這種設(shè)計可能導致鎖競爭、阻塞，以及鎖等待超時等情況。一種緩解的辦法就是避免讓事務(wù)開啟太久以減少阻塞?？梢栽谥鲙焐媳M快地提交事務(wù)以釋放鎖。

把大命令拆分成小命令，使其盡可能簡短。這也是一種減少鎖競爭的有效方法。即使有時很難做到，但也是值得的( 使用Percona Toolkit中的pt- archiver工具會很簡單)。

另一種方法是替換掉INSERT.. . SELECT語句，在主庫上先執(zhí)行SELECT INTO OUTFILE, 再執(zhí)行L0AD DATA INFILE。 這種方法更快，并且不需要加鎖。這種方法很特殊，但有時 還是有用的。最大的問題是為輸出文件選擇一個唯一的名字，并在完成后清理掉文件。 可以通過之前討論過的CONNECTION_ID() 來保證文件名的唯一性， 并且可以使用定時任務(wù)(UNIX的crontab, ,Windows平臺的計劃任務(wù))在連接不再使用這些文件后進行自動清理。

也可以嘗試關(guān)閉上面的這種鎖機制，而不是使用上面的變通方法。有一種方法可以做到, 但在大多數(shù)場景下并不是好辦法，備庫可能會在不知不覺間就失去和主庫的數(shù)據(jù)同步。 這也會導致在做恢復時二進制日志變得毫無用處。但如果確實覺得這么做的利大于弊, 可以使用下面的辦法來關(guān)閉這種鎖機制:

# THIS IS NOT SAFE!
innodb_locks_unsafe_for_binlog = 1

這使得查詢的結(jié)果所依賴的數(shù)據(jù)不再加鎖。如果第二條查詢修改了數(shù)據(jù)并在第一條查詢之前先提交。在主庫和備庫上執(zhí)行這兩條語句的結(jié)果可能不相同。對于復制和基于時間點的恢復都是如此。

為了了解鎖定讀取是如何防止混亂的，假設(shè)有兩張表:一個沒有數(shù)據(jù)，另一個只有一行 數(shù)據(jù)，值為99。有兩個事務(wù)更新數(shù)據(jù)。事務(wù)1將第二張表的數(shù)據(jù)插入到第一張表，事務(wù)2更新第二張表(源表)， 如下圖所示。

image.png

第二步非常重要，事務(wù)2嘗試去更新源表，這需要在更新的行上加排他鎖(寫鎖)。排他鎖與其他鎖是不相容的，包括事務(wù)1在行記錄上加的共享鎖。因此事務(wù)2需要等待直到事務(wù)1完成。事務(wù)按照其提交的順序在二進制日志中記錄，所以在備庫重放這些事務(wù)時產(chǎn)生相同的結(jié)果。

但從另一方面來說， 如果事務(wù)1沒有在讀取的行上加共享鎖，就無法保證了。下圖顯示了在沒有鎖的情況下可能的事件序列。

image.png

如果沒有加鎖，記錄在日志中的事務(wù)順序在主備上可能會產(chǎn)生不同的結(jié)果。MySQL會先記錄事務(wù)2，這會影響到事務(wù)1在備庫上的結(jié)果(先修改事務(wù)2為100,然后復制到事務(wù)1上)，而主庫上則不會發(fā)生，從而導致了主備的數(shù)據(jù)不一致。

過大的復制延遲

復制延遲是一個很普遍的問題。不管怎么樣，最好在設(shè)計應(yīng)用程序時能夠讓其容忍備庫出現(xiàn)延遲。如果系統(tǒng)在備庫出現(xiàn)延遲時就無法很好地工作，那么應(yīng)用程序也許就不應(yīng)該用到復制。但是也有一些辦法可以讓備庫跟上主庫。

MySQL單線程復制的設(shè)計導致備庫的效率相當?shù)拖?。即使備庫有很多磁盤、CPU或者內(nèi)存,也會很容易落后于主庫。因為備庫的單線程通常只會有效地使用一個CPU和磁盤。 而事實上，備庫通常都會和主庫使用相同配置的機器。

備庫上的鎖同樣也是問題。其他在備庫運行的查詢可能會阻塞住復制線程。因為復制是單線程的，復制線程在等待時將無法做別的事情。

復制一般有兩種產(chǎn)生延遲的方式:突然產(chǎn)生延遲然后再跟上，或者穩(wěn)定的延遲增大。前一種通常是由于一條運行很長時間的查詢導致的，而后者即使在沒有長時間運行的查詢時也會出現(xiàn)。

不幸的是，目前我們沒那么容易確定備庫是否接近其容量上限。正如之前提到的。如果負載總是保持均勻的，備庫在負載達到99%時和其負載在10%的時候表現(xiàn)的性能相同， 但一旦達到100%時就會突然開始產(chǎn)生延遲。但實際上負載不太可能很穩(wěn)定，所以當備庫接近寫容量時，就可能在尖峰負載時看到復制延遲的增加。

當備庫無法跟上時，可以記錄備庫上的查詢并使用一個日志分析工具找出哪里慢了。不要依賴于自己的直覺，也不要基于查詢在主庫上的查詢性能進行判斷，因為主庫和備庫性能特征很不相同。最好的分析辦法是暫時在備庫上打開慢查詢?nèi)罩居涗?，然后使用pt-query-digest工具來分析。如果打開了log_slow_slave_statements選項， 在標準的MySQL慢查詢?nèi)罩灸軌蛴涗汳ySQL 5.1及更新的版本中復制線程執(zhí)行的語句， 這樣就可以找到在復制時哪些語句執(zhí)行慢了。Percona Server和MariaDB允許開啟或禁止該選項而無須重啟服務(wù)器。

除了購買更快的磁盤和CPU（固態(tài)硬盤能夠提供極大的幫助)，備庫沒有太多的調(diào)優(yōu)空間。大部分選項都是禁止某些額外的工作以減少備庫的負載。一個簡單的辦法是配置InnoDB，使其不要那么頻繁地刷新磁盤，這樣事務(wù)會提交得更快些。 可以通過設(shè)置innodb_flush_log_at_trx_commit的值為2來實現(xiàn)。還可以在備庫上禁止二進制日志記錄，把innodb_locks_unsafe_for_binlog 設(shè)置為1，并把MyISAM的 delay_key_write設(shè)置為ALL。但是這些設(shè)置以犧牲安全換取速度。如果需要將備庫提升為主庫，記得把這些選項設(shè)置回安全的值。

不要重復寫操作代價較高的部分

重構(gòu)應(yīng)用程序并且/或者優(yōu)化查詢通常是最好的保持備庫同步的辦法。嘗試去最小化系統(tǒng)中重復的工作。任何主庫上昂貴的寫操作都會在每一個備庫上重放。如果可以把工作. 轉(zhuǎn)移到備庫,那么就只有一臺備庫需要執(zhí)行,然后我們可以把寫的結(jié)果回傳到主庫,例如， 通過執(zhí)行LOAD DATA INFILE。

這里有個例子，假設(shè)有一個大表，需要匯總到一個小表中用于日常的操作:

mysql> REPLACE INTO main _db. summary_ _table (co11, col2, ...)
SELECT col1, sum(co12, ...)
FROM main _db. enormous_ table GROUP BY col1;

如果在主庫執(zhí)行查詢，每個備庫將同樣需要執(zhí)行龐大的GROUP BY查詢。當進行太多這 樣的操作時，備庫將無法跟上。把這些工作轉(zhuǎn)移到一臺備庫上也許會有幫助。在備庫上 創(chuàng)建一個特別保留的數(shù)據(jù)庫，用于避免和從主庫上復制的數(shù)據(jù)產(chǎn)生沖突?？梢詧?zhí)行以下 查詢:

總結(jié)

MySQL復制是其內(nèi)建功能中的“ 瑞士軍刀”，顯著增加了MySQL的功能和可用性。事 實上這也是MySQL這么快就如此流行的關(guān)鍵原因之一。

盡管復制有許多限制和風險，但大多數(shù)相對不重要或者對大多數(shù)用戶而言是可以避免的。 許多缺點只在一些高級特性的特殊行為中，這些特性對少數(shù)需要的人而言是有幫助的， 但大多數(shù)人并不會用到。

正因為復制提供了如此重要和復雜的功能，服務(wù)器本身不提供所有其他你需要的功能，例如，配置、監(jiān)控、管理和優(yōu)化。第三方工具可以很好地幫助你。雖然可能有失偏頗，但我們認為最值得關(guān)注的工具一定 是Percona Toolkit和Percona XtraBackup，它們能夠很好地改進你對復制的使用。在使用別的工具前，建議你先檢查它們的測試集合，如果沒有正式的、自動化的測試集合，在將其應(yīng)用到你的數(shù)據(jù)之前請認真考慮。

對于復制,不要按照想象做事，例如，使用環(huán)形復制、黑洞表或者復制過濾，除非確實有需要。使用復制簡單地去鏡像一份完整的數(shù)據(jù)拷貝，包括所有的權(quán)限。在各方面保持你的主備庫相同可以幫助你避免很多問題。

談到保持主庫和備庫相同，這里有一個簡短但很重要的列表告訴你在使用復制的時候需要做什么:

• 使用Percona Toolkit 中的pt-table-checksum以確定備庫是主庫的真實拷貝

• 監(jiān)控復制以確定其正在運行并且沒有落后于主庫。

• 理解復制的異步本質(zhì)，并且設(shè)計你的應(yīng)用以避免或容忍從備庫讀取臟的數(shù)據(jù)。

• 在一個復制拓撲中不要寫入超過一個服務(wù)器，把備庫配置為只讀，并降低權(quán)限以阻止對數(shù)據(jù)的改變。