記一次MySQL從節點伺服器宕機重啟後,從節點出現主鍵衝突異常的處理

来源:https://www.cnblogs.com/wy123/p/17994084
-Advertisement-
Play Games

環境 MySQL 5.7 非GTID模式多線程複製。 現象 某MySQL資料庫從節點因故障宕機(因故障直接宕機,非正常關閉),重啟之後發現複製狀態異常,show slave的結果中Slave_SQL_Running為No,錯誤代碼為1062 error code,從系統表performance_sc ...


環境

MySQL 5.7 非GTID模式多線程複製。

現象

某MySQL資料庫從節點因故障宕機(因故障直接宕機,非正常關閉),重啟之後發現複製狀態異常,show slave的結果中Slave_SQL_Running為No,錯誤代碼為1062 error code,從系統表performance_schema.replication_applier_status_by_worker以及error log中顯示某條數據因為已存在於從節點,主從複製的sql線程試圖再次插入這個數據從而導致從節點上數據的主鍵衝突而失敗

原因分析

上述故障發生之前,確信從節點不會寫入數據,既然確信從節點的數據不是外界寫入的,那麼為什麼從節點會在回放relay log過程中出現主鍵衝突錯誤?
這裡在排除從節點的數據是外部寫入的情況下,懷疑是sql線程“重覆”回放relay log同一條事務造成的。

先梳理一下複製相關的幾個重點參數的含義:

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host:                      
                  Master_User:                                             
                  Master_Port: 
                Connect_Retry: 
              Master_Log_File: ***.000066            --從節點IO線程讀到的master節點的binlog file
          Read_Master_Log_Pos: 64117146              --從節點IO線程讀到的master節點的binlog file 的坐標位
               Relay_Log_File: ***.000143            --從節點正在apply哪個relaylog
                Relay_Log_Pos: 38472356              --從節點正在apply relaylog的坐標位,複製的過程會一直變化
        Relay_Master_Log_File: ***.000049            --從節點apply本地的relay log事件,在master上對應的master binlog file
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
                   Last_Errno: 1062
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 38472155             --從節點apply relay log事件位置在master上對應的master binlog file的位點(如何確定Exec_Master_Log_Pos會在回放relay log時同步更新?異常宕機之後呢?)
              Relay_Log_Space: 1848930175

再簡單梳理一下從節點異常宕機,重新啟動後複製的啟動過程:
MySQL服務啟動後,主從複製進程會自動啟動(skip_slave_start = off)
1,IO 線程會根據Master_Log_File+Read_Master_Log_Pos繼續去主節點拉binlog。
2,SQL 線程會根據Relay_Master_Log_File+Exec_Master_Log_Pos確定當前節點已經回放的relay log的文件和具體的位置,基於此坐標點位繼續回放relay log。
如果回放relay log與更新坐標位是絕對準確的,那麼MySQL重啟後,SQL線程會繼續基於這個坐標位點繼續回放,理論上不會出錯。
設想一下:一個事物已經“回放”過了,但是沒有同步更新Relay_Master_Log_File+Exec_Master_Log_Pos坐標位點的情況下宕機,此時重啟後會繼續基於這個位點做回放,此時就會出現重覆回放某個事務日誌,其結果就會造成上面的逐漸衝突錯誤

事實上是不是這樣呢?從這裡:https://dev.mysql.com/doc/refman/8.0/en/show-replica-status.html
發現官方文檔對Exec_Master_Log_Pos(MySQL 8.0.23之後改名為Exec_Source_Log_Pos)的解釋如下:
The position in the current source binary log file to which the replication SQL thread has read and executed, marking the start of the next transaction or event to be processed. This is set to zero for a replication channel with the GTID_ONLY setting after a server start. It will be updated when a transaction is executed or skipped.
You can use this value with the CHANGE REPLICATION SOURCE TO statement's SOURCE_LOG_POS option (from MySQL 8.0.23) or the CHANGE MASTER TO statement's MASTER_LOG_POS option (before MySQL 8.0.23) when starting a new replica from an existing replica, so that the new replica reads from this point. The coordinates given by (Relay_Source_Log_File, Exec_Source_Log_Pos) in the source's binary log correspond to the coordinates given by (Relay_Log_File, Relay_Log_Pos) in the relay log.
Inconsistencies in the sequence of transactions from the relay log which have been executed can cause this value to be a “low-water mark”. In other words, transactions appearing before the position are guaranteed to have committed, but transactions after the position may have committed or not. If these gaps need to be corrected, use START REPLICA UNTIL SQL_AFTER_MTS_GAPS. See Section 17.5.1.34, “Replication and Transaction Inconsistencies” for more information

翻譯過來大概意思是:

複製SQL線程讀取並執行了當前源二進位日誌文件中的位置,標記了要處理的下一個事務或事件的開始。對於具有GTID_ONLY設置的複製通道,在伺服器啟動後,此值將設置為零。當事務被執行或跳過時,此值將被更新。

你可以在從一個現有副本開始新副本時使用此值(指的是Exec_Master_Log_Pos)與CHANGE REPLICATION SOURCE TO語句的SOURCE_LOG_POS選項(從MySQL 8.0.23開始)或CHANGE MASTER TO語句的MASTER_LOG_POS選項(在MySQL 8.0.23之前)一起使用,以便新副本從此點(指的是Exec_Master_Log_Pos)開始讀取。源的二進位日誌中的(Relay_Source_Log_File,Exec_Source_Log_Pos)坐標對應於中繼日誌中的(Relay_Log_File,Relay_Log_Pos)坐標。

從中繼日誌中已執行的事務序列( the sequence of transactions)中的不一致可能會導致此值成為“低水位標記”(low-water mark)。換句話說就是:出現在該位置之前的事務確實是被提交了的,但位置之後的事務可能已經提交或未提交。如果需要糾正這些差距,請使用START REPLICA UNTIL SQL_AFTER_MTS_GAPS。有關更多信息,請參閱第17.5.1.34節“複製和事務不一致性”。


繼續參閱第17.5.1.34節“複製和事務不一致性: https://dev.mysql.com/doc/refman/8.0/en/replication-features-transaction-inconsistencies.html中提到
Source binary log position lag.Even in the absence of gaps, it is possible that transactions after Exec_master_log_pos have been applied. That is, all transactions up to point N have been applied, and no transactions after N have been applied, but Exec_master_log_pos has a value smaller than N. In this situation, Exec_master_log_pos is a “low-water mark” of the transactions applied, and lags behind the position of the most recently applied transaction. This can only happen on multithreaded replicas. Enabling replica_preserve_commit_order or slave_preserve_commit_order does not prevent source binary log position lag.

源binlog位置延遲。即使沒有gaps,也可能會出現Exec_master_log_pos之後的事務被回放的情況(筆者註:從節點上Exec_master_log_pos比實際apply的log位置要小的情況)。也就是說,所有N 點位之前的事務都已applied,並且N之後的事務都沒有被應用,但Exec_master_log_pos的值小於N。在這種情況下,Exec_master_log_pos是已應用事務的“低水位標記”(low-water mark),並且落後於最近應用的事務的位置。這隻能在多線程複製上發生。啟用replica_preserve_commit_order或slave_preserve_commit_order不會阻止源二進位日誌位置延遲。

 

事實上,多種情況下都潛在 Replication and Transaction Inconsistencies

上述文檔證支持上面的猜測:
因為Exec_Master_Log_Pos是延遲更新的,MySQL slave節點重啟後,sql線程在“重覆”回放relay log同一條事務造成的主鍵衝突,在從節點宕機之前,某一條事務(insert操作)已經被回放並寫入資料庫中,此時沒來得及更新Exec_Master_Log_Pos信息的情況下宕機,從其之後從Exec_Master_Log_Pos開始回放relay,但是當前Exec_Master_Log_Pos之後的一個或者多個事務在(宕機之前)已經被回訪過,(重啟之後)再次回放的過程就會發生逐漸衝突錯誤。

 

解決

用MySQL主從複製跳過事物錯誤的方式,首先以最小代價的方式恢復主從,同時可以檢查errorlog來發現是否存在其他數據衝突的錯誤。

 
您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 本文介紹瞭如何通過子查詢優化深度分頁查詢,以減少回表操作帶來的性能損耗。傳統的深度分頁查詢往往會面臨嚴重的性能問題,尤其在處理大量數據時更是如此。 ...
  • 本文介紹了在雲上環境的雙集群(不跨Region不跨VPC)後臺手動部署並使用細粒度容災的主要步驟,使得用戶能快速方便得搭建起細粒度容災。 ...
  • MySQL Shell 8.0.32 for GreatSQL編譯二進位包 構建MySQL Shell 8.0.32 for GreatSQL 0. 寫在前面 之前已經寫過一篇前傳 MySQL Shell 8.0.32 for GreatSQL編譯安裝,最近再次編譯MySQL Shell二進位包時, ...
  • 北京時間2024年2月20日,中國領先的開源技術公司,白鯨開源科技有限公司(以下簡稱"白鯨開源")榮幸宣佈,該公司獲得了第六屆 "年度金猿季大型主題策劃活動" 頒發的 "2023大數據產業年度最具投資價值" 獎項。這一殊榮是對白鯨開源在大數據領域取得的卓越成就和突出貢獻的認可。 金猿季推動產業升級 ...
  • 本文深度解析MySQL的COUNT(1), COUNT(*),COUNT(列)計數方式,強調COUNT(*)的廣泛應用與InnoDB存儲引擎的優化。通過性能比較,揭示COUNT(id)在索引下的性能,通過技術細節揭示MySQL查詢優化器的工作原理,最終總結適用場景,為讀者提供計數方式選擇的指導。 ...
  • 目錄MongoDB創建一張表用法示例資料分享系列文章clickhouse系列文章 MongoDB MongoDB 引擎是只讀表引擎,允許從遠程 MongoDB 集合中讀取數據(SELECT查詢)。引擎只支持非嵌套的數據類型。不支持 INSERT 查詢。 創建一張表 CREATE TABLE [IF ...
  • 工商銀行和華為雲牽頭,並聯合7家金融機構共同編製的《金融行業開放平臺資料庫轉型白皮書》榮獲2023年度十佳課題。 ...
  • 優秀的程式應該儘可能地規避問題。因此,以後在服務商系統里,當定義包含漢字的欄位時,使用 nvarchar2,而非 varchar2。 因此,在服務商系統作為我司系統的小眾系統的背景下,Oracle的技術特性我們不一一曉知是可以理解和接受的。而如何在不一一曉知這些技術特性的情況下,能夠規避這些技術特性... ...
一周排行
    -Advertisement-
    Play Games
  • 在C#中使用SQL Server實現事務的ACID(原子性、一致性、隔離性、持久性)屬性和使用資料庫鎖(悲觀鎖和樂觀鎖)時,你可以通過ADO.NET的SqlConnection和SqlTransaction類來實現。下麵是一些示例和概念說明。 實現ACID事務 ACID屬性是事務處理的四個基本特征, ...
  • 我們在《SqlSugar開發框架》中,Winform界面開發部分往往也用到了自定義的用戶控制項,對應一些特殊的界面或者常用到的一些局部界面內容,我們可以使用自定義的用戶控制項來提高界面的統一性,同時也增強了使用的便利性。如我們Winform界面中用到的分頁控制項、附件顯示內容、以及一些公司、部門、菜單的下... ...
  • 在本篇教程中,我們學習瞭如何在 Taurus.MVC WebMVC 中進行數據綁定操作。我們還學習瞭如何使用 ${屬性名稱} CMS 語法來綁定頁面上的元素與 Model 中的屬性。通過這些步驟,我們成功實現了一個簡單的數據綁定示例。 ...
  • 是在MVVM中用來傳遞消息的一種方式。它是在MVVMLight框架中提供的一個實現了IMessenger介面的類,可以用來在ViewModel之間、ViewModel和View之間傳遞消息。 Send 接受一個泛型參數,表示要發送的消息內容。 Register 方法用於註冊某個對象接收消息。 pub ...
  • 概述:在WPF中,通過EventHandler可實現基礎和高級的UI更新方式。基礎用法涉及在類中定義事件,併在UI中訂閱以執行更新操作。高級用法藉助Dispatcher類,確保在非UI線程上執行操作後,通過UI線程更新界面。這兩種方法提供了靈活而可靠的UI更新機制。 在WPF(Windows Pre ...
  • 概述:本文介紹了在C#程式開發中如何利用自定義擴展方法測量代碼執行時間。通過使用簡單的Action委托,開發者可以輕鬆獲取代碼塊的執行時間,幫助優化性能、驗證演算法效率以及監控系統性能。這種通用方法提供了一種便捷而有效的方式,有助於提高開發效率和代碼質量。 在軟體開發中,瞭解代碼執行時間是優化程式性能 ...
  • 概述:Cron表達式是一種強大的定時任務調度工具,通過配置不同欄位實現靈活的時間規定。在.NET中,Quartz庫提供了簡便的方式配置Cron表達式,實現精準的定時任務調度。這種靈活性和可擴展性使得開發者能夠根據需求輕鬆地制定和管理定時任務,例如每天備份系統日誌或其他重要操作。 Cron表達式詳解 ...
  • 概述:.NET提供多種定時器,如System.Windows.Forms.Timer適用於UI,System.Web.UI.Timer用於Web,System.Diagnostics.Timer用於性能監控,System.Threading.Timer和System.Timers.Timer用於一般 ...
  • 問題背景 有同事聯繫我說,在生產環境上,訪問不了我負責的common服務,然後我去檢查common服務的health endpoint, 沒問題,然後我問了下異常,timeout導致的System.OperationCanceledException。那大概率是客戶端的問題,會不會是埠耗盡,用ne ...
  • 前言: 在本篇 Taurus.MVC WebMVC 入門開發教程的第四篇文章中, 我們將學習如何實現數據列表的綁定,通過使用 List<Model> 來展示多個數據項。 我們將繼續使用 Taurus.Mvc 命名空間,同時探討如何在視圖中綁定並顯示一個 Model 列表。 步驟1:創建 Model ...