GreatSQL一個關於主從複製的限制描述與規避

来源:https://www.cnblogs.com/greatsql/archive/2023/09/27/17731957.html
-Advertisement-
Play Games

一、背景 分享一個在項目運維中遇到的一個主從複製限制的一個坑，項目的架構為主集群+災備集群，每個集群為一主兩從模式。主集群到災備集群的同步為主從複製的方式，根據業務需求災備集群需要忽略系統庫跟某些配置表，所以才會觸發此限制，而這個限制如果我們之前沒有遇到過，那麼排查起來也是相對不易的。 二、限制描述 ...


一、背景

分享一個在項目運維中遇到的一個主從複製限制的一個坑，項目的架構為主集群+災備集群，每個集群為一主兩從模式。主集群到災備集群的同步為主從複製的方式，根據業務需求災備集群需要忽略系統庫跟某些配置表，所以才會觸發此限制，而這個限制如果我們之前沒有遇到過，那麼排查起來也是相對不易的。

二、限制描述

1、主從同步出現報錯

greatsql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.xxx.xxx
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: greatsql-bin.000990
          Read_Master_Log_Pos: 92274290
               Relay_Log_File: greatsql-relay.002963     -----
                Relay_Log_Pos: 701548899
        Relay_Master_Log_File: greatsql-bin.000988
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB: mysql,dbscale,dbscale_tmp,information_schema,performance_schema,sys
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table: A.ab,B.bc
                   Last_Errno: 1146
                   Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction '9e668a93-2618-11ee-93ee-bc16954181bb:47508257' at master log greatdb-bin.000988, end_log_pos 701570116. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 701548690
              Relay_Log_Space: 2246320360
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 1146
               Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction '9e668a93-2618-11ee-93ee-bc16954181bb:47508257' at master log greatdb-bin.000988, end_log_pos 701570116. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1943306
                  Master_UUID: 9e668a93-2618-11ee-93ee-bc16954181bb
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State:
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp: 230822 14:14:18
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set: 9e668a93-2618-11ee-93ee-bc16954181bb:2-47565802
            Executed_Gtid_Set: 30873cfe-8750-11ed-b56f-744aa4073024:1-270,
9e668a93-2618-11ee-93ee-bc16954181bb:1-47508256
                Auto_Position: 1
         Replicate_Rewrite_DB:  
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

根據slave status狀態信息可以看出

  • 報錯的GTID為：'9e668a93-2618-11ee-93ee-bc16954181bb:47508257'
  • 應用的主集群的binlog為：greatsql-bin.000988
  • 災備集群的relay log為：greatsql-relay.002963

詳細信息查看performance_schema.replication_applier_status_by_worker表

2、查看錯誤的詳細信息

greatsql> select * from performance_schema.replication_applier_status_by_worker\G
*************************** 1. row ***************************
         CHANNEL_NAME:
            WORKER_ID: 1
            THREAD_ID: NULL
        SERVICE_STATE: OFF
LAST_SEEN_TRANSACTION: 9e668a93-2618-11ee-93ee-bc16954181bb:47508257
    LAST_ERROR_NUMBER: 1146
   LAST_ERROR_MESSAGE: Worker 1 failed executing transaction
'9e668a93-2618-11ee-93ee-bc16954181bb:47508257' at master log greatdb-bin.000988,
end_log_pos 701570116; Error executing row event: 'Table 'abs_xxx.tmp_xxx_info' doesn't exist'
LAST_ERROR_TIMESTAMP: 2023-08-22 14:14:18

上述信息說明根據performance_schema.replication_applier_status_by_worker表中的詳細錯誤信息可以發現為災備集群abs_xxx.tmp_xxx_info表不存在，導致同步報錯

3、問題分析

3.1、確認災備集群中目標表是否存在

greatsql> show create table abs_xxx.tmp_xxx_info;
ERROR 1146 (42S02): Table 'abs_xxx.tmp_xxx_info' doesn't exist
greatsql> desc abs_xxx.tmp_xxx_info;
ERROR 1146 (42S02): Table 'abs_xxx.tmp_xxx_info' doesn't exist

結論：災備集群中目標表的確不存在

3.2、根據主從報錯信息解析主集群binlog，報錯的SQL

解析主集群binlog

SET @@SESSION.GTID_NEXT= '9e668a93-2618-11ee-93ee-bc16954181bb:47508257'/*!*/;
……
#230822 14:14:18 server id 1943306  end_log_pos 701570000         Table_map: `abs_xxx`.`tmp_xxx_info` mapped to number 1595
# at 701570000
#230822 14:14:18 server id 1943306  end_log_pos 701570116         Write_rows: table id 1595 flags: STMT_END_F
### INSERT INTO `abs_xxx`.`tmp_xxx_info`
### SET
###   @1=2
###   @2='自動化'
###   @3='2300121212120000'
###   @4='90000000'
###   @5='1'
###   @6='202001290231001'
###   @7='2021-01-31 00:00:00'
# at 701570116
#230822 14:14:18 server id 1943306  end_log_pos 701570143         Xid = 800998400
COMMIT/*!*/;
# at 701570143
#230822 14:14:18 server id 1943306  end_log_pos 701570204         GTID        last_committed=26491        sequence_number=26521        rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;

結論：根據複製的報錯信息得知具體的GTID號以及主集群的binlog文件，解析binlog得知此事務為一條INSERT語句，語句中的目標表與performance_schema.replication_applier_status_by_worker表中信息一致

3.3、尋找主集群目標表binlog中是否有建表語句

在同一binlog日誌中尋找建表語句

SET TIMESTAMP=1692684495/*!*/;
CREATE DATABASE IF NOT EXISTS `abs_xxx` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_bin */
/*!*/;
……
use `information_schema`/*!*/;
SET TIMESTAMP=1692684495/*!*/;
CREATE TABLE `abs_xxx`.`tmp_xxx_info` (
  `ID` int(64) NOT NULL AUTO_INCREMENT,
  `CUSTOMER_NAME` varchar(256) COLLATE utf8mb4_bin DEFAULT NULL,
  `CUSTOMER_NUM` varchar(256) COLLATE utf8mb4_bin DEFAULT NULL,
  `PRODIST_SKU_NUM` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL,
  `STATUS` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL,
  `AGREEMENT_NUM` varchar(256) COLLATE utf8mb4_bin DEFAULT NULL,
  `END_DATE` datetime DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `PK_PRODIST_SKU_NUM_AGREEMENT` (`PRODIST_SKU_NUM`) USING BTREE,
  KEY `IDX_AGREEMENT_NUM` (`AGREEMENT_NUM`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC
/*!*/;
# at 475864451

結論：在主集群的binlog日誌中找到了目標表的建表語句，說明主集群執行DDL時並沒有關閉binlog日誌，那麼繼續查看在災備集群的中繼日誌中是否存在DDL語句

3.4、解析災備集群的中繼日誌，確認是否拉取到災備集群

#230822 14:08:15 server id 1943306  end_log_pos 475863662         GTID        last_committed=16341        sequence_number=16342        rbr_only=no
SET @@SESSION.GTID_NEXT= '9e668a93-2618-11ee-93ee-bc16954181bb:47498079'/*!*/;
……
use `information_schema`/*!*/;
SET TIMESTAMP=1692684495/*!*/;
CREATE TABLE `abs_xxx`.`tmp_xxx_info` (
  `ID` int(64) NOT NULL AUTO_INCREMENT,
  `CUSTOMER_NAME` varchar(256) COLLATE utf8mb4_bin DEFAULT NULL,
  `CUSTOMER_NUM` varchar(256) COLLATE utf8mb4_bin DEFAULT NULL,
  `PRODIST_SKU_NUM` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL,
  `STATUS` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL,
  `AGREEMENT_NUM` varchar(256) COLLATE utf8mb4_bin DEFAULT NULL,
  `END_DATE` datetime DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `PK_PRODIST_SKU_NUM_AGREEMENT` (`PRODIST_SKU_NUM`) USING BTREE,
  KEY `IDX_AGREEMENT_NUM` (`AGREEMENT_NUM`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC
/*!*/;
# at 475864660
#230822 14:08:15 server id 1943306  end_log_pos 475864512         GTID        last_committed=16342        sequence_number=16343        rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;

結論：災備集群的中繼日誌中存在DDL建表語句，說明並不是IO線程出了問題

3.5、排查複製配置的忽略庫表

Replicate_Ignore_DB: mysql,dbscale,dbscale_tmp,information_schema,performance_schema,sys
Replicate_Wild_Ignore_Table: A.ab,B.bc

結論：忽略庫表中並不包含目標表，但是根據以上解析日誌發現，在主集群binlog日誌中建表語句之前有個use information_schema/!/; 的語句，此庫為同步忽略的系統庫，因此觸發了GreatSQL的規範限制，在忽略庫下對未忽略進行操作Statement模式下記錄語句預設不起作用 （詳情：https://dev.mysql.com/doc/refman/5.7/en/replication-options-replica.html#option_mysqld_replicate-do-db）

4、解決同步報錯

在災備集群創建目標表

greatsql> CREATE TABLE `abs_xxx`.`tmp_xxx_info` (
  `ID` int(64) NOT NULL AUTO_INCREMENT,
  `CUSTOMER_NAME` varchar(256) COLLATE utf8mb4_bin DEFAULT NULL,
  `CUSTOMER_NUM` varchar(256) COLLATE utf8mb4_bin DEFAULT NULL,
  `PRODIST_SKU_NUM` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL,
  `STATUS` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL,
  `AGREEMENT_NUM` varchar(256) COLLATE utf8mb4_bin DEFAULT NULL,
  `END_DATE` datetime DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `PK_PRODIST_SKU_NUM_AGREEMENT` (`PRODIST_SKU_NUM`) USING BTREE,
  KEY `IDX_AGREEMENT_NUM` (`AGREEMENT_NUM`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC;

greatsql> stop slave;
greatsql> start slave;

結論：在災備集群創建目標表後重啟複製恢覆成功

三、限制規避

1、第一種規避方式

執行DDL時進入目標庫

greatsql> use abs_cust
greatsql> DDL 語句（CREATE\DROP\ALTER）

說明：在應用連接資料庫時有可能預設就是information_schema庫，而此環境將系統庫全部忽略，所以為了規避類似的問題，請在執行SQL語句時請先use到目標表的目標庫。

2、第二種規避方式

修改主從複製配置，以下步驟為測試環境

關閉災備集群在複製同步

greatsql> stop slave;
Query OK, 0 rows affected, 1 warning (0.03 sec)

修改忽略庫

greatsql> change replication filter Replicate_Ignore_DB=();

修改忽略表

greatsql> change replication filter replicate_wild_ignore_table =('mysql.%','information_schema.%','sys.%','performance_schema.%');

啟動同步

greatsql> start slave;
Query OK, 0 rows affected, 1 warning (0.37 sec)

測試驗證

主集群：

greatsql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed

greatsql> create table test111.test111(id int primary key);
Query OK, 0 rows affected (0.06 sec)

greatsql> show tables;
+-------------------+
| Tables_in_test111 |
+-------------------+
| test111           |
+-------------------+
1 row in set (0.00 sec)

災備集群：

greatsql> use test111
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed

greatsql> show tables;
+-------------------+
| Tables_in_test111 |
+-------------------+
| test111           |
+-------------------+
1 row in set (0.00 sec)

說明：複製配置中參數Replicate_Ignore_DB設置為空，將replicate_wild_ignore_table參數設置為shema_name.%的方式也可以規避類似的問題

四、特別說明

  • 在MySQL 5.7跟8.0版本也存在此限制

Enjoy GreatSQL

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

-Advertisement-
Play Games
更多相關文章
  • 讀高性能MySQL（第4版）筆記16_複製（上）
    1. 概述 1.1. 複製解決的基本問題是讓一臺伺服器的數據與其他伺服器保持同步 1.2. 在源伺服器（source server）上，任何數據修改和數據結構變更的事件（event）都會被寫入日誌文件中 1.3. 副本伺服器從源伺服器上的日誌文件中讀取這些事件併在本地重放執行 1.4. 一個非同步處理 ...
  • 日常Bug排查-讀從庫沒有原子性？
    日常Bug排查系列都是一些簡單Bug排查。問題雖小，但經常遇到，瞭解這些問題，會讓我們少走點彎路，提升效率。說不定有些問題你遇到過哦:) Bug現場 業務開發同學突然問了筆者一個問題，從庫讀會不會沒有原子性？我下意識的反應怎麼可能，只要是遵守MySQL主從Replication協議的原子性至少是能夠 ...
  • MySql explain命令返回結果解讀
    1. explain命令是什麼東西？ explain 是MySql提供的SQL語句查詢性能的工具，是我們優化SQL的重要指標手段，要看懂explain返回的結果集就尤為重要 2. explain命令返回列解讀 + + + + + + + + + + + + + | id | select_type ...
  • 袋鼠雲代碼檢查服務，揭秘高質量代碼背後的秘密
    質量是產品的生命線，代碼檢查是軟體開發過程中至關重要的一環，它可以幫助我們發現並糾正潛在的錯誤，提高軟體質量，降低維護成本。 在袋鼠雲產品中也存在這個問題，由於離線數據開發人員 SQL 水平不一，導致代碼書寫混亂、SQL 代碼運行問題較多。本文將介紹在離線產品中如何利用 SQL 檢查規則規範化 SQ ...
  • 四問複合索引，讓你的數據查詢速度飛起
    索引雖然能加速查詢，但是會降低寫操作的性能，以及耗費更多的磁碟空間。所以建立索引之前需要考慮是不是必要的。 ...
  • WhaleStudio 分鐘級構建 AI 模型，強大 Ops 能力簡化模型調度與部署
    什麼是機器學習(ML)? 它有什麼作用 機器學習(ML)是人工智慧(AI)的一個子集，通過演算法發現數據中的通用模式，並根據持續不斷的訓練來優化調整最終結果。ML模型從過去的經驗中學習，並根據已有的經驗進行預測。例如，現在的電商已不再會使用普遍性降價或優惠券等手段吸引客戶，取而代之的是根據每個客戶的歷 ...
  • 生態相容性進一步提升！白鯨開源 WhaleStudio 與火山引擎ByteHouse完成產品互認
    數據作為新型生產要素，已快速融入生產、分配、流通、消費和社會服務管理等各環節，深刻改變著生產方式、生活方式和治理方式。越來越多企業也在嘗試充分利用數據要素，開闢全新發展路徑，進一步實現業務價值提升。 在數字化轉型的大背景之下，白鯨開源旗下WhaleStudio與火山引擎ByteHouse依托於雙方完 ...
  • ora2pg使用記錄
    ora2pg使用記錄 前言 這篇文章是我在學習使用ora2pg過程中的學習記錄，以便日後遺忘查閱； 諸君也可跟隨我的步伐瞭解一下ora2pg，或可移步如下官方文檔參考學習：Ora2Pg : Migrates Oracle to PostgreSQL (darold.net) 本文的ora2pg安裝和 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 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 ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類