記一次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
  • 移動開發(一):使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說,近來想嘗試開發一款安卓APP,考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP,畢竟是使用Visual Studio開發工具,使用起來也比較的順手,結合微軟官方的教程進行了安卓 ...
  • 前言 QuestPDF 是一個開源 .NET 庫,用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫,它徹底改變了我們生成 PDF 文檔的方 ...
  • 項目地址 項目後端地址: https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址: ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址: http://tree ...
  • 話不多說,直接開乾 一.下載 1.官方鏈接下載: https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe,運行開始下載SQL server; 二. ...
  • 前言 隨著物聯網(IoT)技術的迅猛發展,MQTT(消息隊列遙測傳輸)協議憑藉其輕量級和高效性,已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫,為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog支持多種接收器用於日誌存儲,增強器用於添加屬性,LogContext管理動態屬性,支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項,適用於不同需求。 ...
  • 目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據,爬取信息時需要模擬瀏覽器行為,否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下: 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • 1.前言 什麼是熱更新 游戲或者軟體更新時,無需重新下載客戶端進行安裝,而是在應用程式啟動的情況下,在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR,Xlua,ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles,Addressable, ...
  • 本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺,其實像阿裡雲,騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後,它會送 ...
  • 通過以下方式可以高效,並保證數據同步的可靠性 1.API設計 使用RESTful設計,確保API端點明確,並使用適當的HTTP方法(如POST用於創建,PUT用於更新)。 設計清晰的請求和響應模型,以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證,確保接收到的數據符合預期格 ...