Mysql

MySQL 複製從屬狀態正常但數據不一致

  • January 23, 2019

我們使用的是 MySQL 5.7，並且有一個主伺服器和 3 個從伺服器複製設置。所有 4 台伺服器僅供 MySQL 使用，屬於同一個數據中心，通過專用網路連接。所以我相信複製的網路問題應該是最小的。

所以，我SHOW SLAVE STATUS\G在 Slave 1 伺服器上做了：

mysql> show slave status\G
*************************** 1. row ***************************
              Slave_IO_State: Waiting for master to send event
                 Master_Host: <removed for anonymity>
                 Master_User: repl
                 Master_Port: 3306
               Connect_Retry: 60
             Master_Log_File: mysql-bin.004803
         Read_Master_Log_Pos: 885020002
              Relay_Log_File: ubuntu-s-4vcpu-8gb-blr1-slave02-relay-bin.001056
               Relay_Log_Pos: 885020215
       Relay_Master_Log_File: mysql-bin.004803
            Slave_IO_Running: Yes
           Slave_SQL_Running: Yes
             Replicate_Do_DB: 
         Replicate_Ignore_DB: 
          Replicate_Do_Table: 
      Replicate_Ignore_Table: 
     Replicate_Wild_Do_Table: 
 Replicate_Wild_Ignore_Table: 
                  Last_Errno: 0
                  Last_Error: 
                Skip_Counter: 0
         Exec_Master_Log_Pos: 885020002
             Relay_Log_Space: 885020495
             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: 0
Master_SSL_Verify_Server_Cert: No
               Last_IO_Errno: 0
               Last_IO_Error: 
              Last_SQL_Errno: 0
              Last_SQL_Error: 
 Replicate_Ignore_Server_Ids: 
            Master_Server_Id: 103
                 Master_UUID: 78e93502-d3af-11e7-9af0-5aaa686987ef
            Master_Info_File: /var/lib/mysql/master.info
                   SQL_Delay: 0
         SQL_Remaining_Delay: NULL
     Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
          Master_Retry_Count: 86400
                 Master_Bind: 
     Last_IO_Error_Timestamp: 
    Last_SQL_Error_Timestamp: 
              Master_SSL_Crl: 
          Master_SSL_Crlpath: 
          Retrieved_Gtid_Set: 
           Executed_Gtid_Set: 
               Auto_Position: 0
        Replicate_Rewrite_DB: 
                Channel_Name: 
          Master_TLS_Version: 
1 row in set (0.00 sec)

它清楚地表明一切都很好。我們還有很多監控工具，比如 Zabbix、Packetbeat (Kibana) setup；如果三個從伺服器中的任何一個中的複制存在延遲，它也會向我們發出警報。我們也沒有收到有關可能的複制問題的任何警報。

現在，我在 Slave 1 伺服器上執行了一個查詢：

mysql> select order_id, order_status_id, buyer_invoice_id 
      from oc_suborder where order_id = 62284;
+----------+-----------------+------------------+
| order_id | order_status_id | buyer_invoice_id |
+----------+-----------------+------------------+
|    62284 |               1 |             NULL |
|    62284 |              15 |            76729 |
+----------+-----------------+------------------+
2 rows in set (0.01 sec)

在這種情況下它返回兩行，這是錯誤的。

我在主伺服器上執行了相同的查詢：

mysql> select order_id, order_status_id, buyer_invoice_id 
      from oc_suborder where order_id = 62284;
+----------+-----------------+------------------+
| order_id | order_status_id | buyer_invoice_id |
+----------+-----------------+------------------+
|    62284 |              15 |            76729 |
+----------+-----------------+------------------+
1 row in set (0.18 sec)

它只返回一行，這是正確的；因此，儘管 Slave Status 顯示為 OK ，但Slave 肯定有不一致的數據。

我還在 Master 上執行了一些 DML 操作，這些更改確實在 Slave 伺服器上複製了；所以複製正在執行。我們正在使用第三方 DBA 服務，而且每次都發生這種情況；他們所建議的只是重建從伺服器。我們已經擁有 100 GB 以上的數據，而且這個過程需要好幾個小時（他們在過去幾個月裡已經完成了 4 次）；但是數據不一致的情況卻一次又一次地出現。

我的問題是多方面的：

  1. 有沒有辦法跨多個模式（數據庫）簡單地辨識與數據不匹配相關的表/行；並修復這些特定行，而不是再次重建從站。我懷疑我可能有多個表存在這種數據不匹配的情況。只有當某些特定報告包含可疑數據時，我們才會發現這些不匹配。
  2. 出現數據不匹配的可能原因是什麼？即使 Slave 落後 Master 0 秒。上面查詢中的範例表使用 InnoDB。該表上執行了一些事務，我開始懷疑事務在主伺服器上成功送出，但有時在從伺服器上沒有。
  3. 使用SHOW GLOBAL VARIABLES LIKE 'binlog_format';，我發現binlog_format在我的主伺服器上設置為MIXED，而在從伺服器上設置為ROW。這可能是此錯誤或其他一些未發現錯誤的來源嗎？

如果問題看起來太寬泛（根據堆棧溢出規則），請多多包涵。我很樂意根據需要提供盡可能多的詳細資訊，或者將問題編輯為具體細節。我是Stack Overflow 的普通使用者，第一次在這裡發帖。任何指針都會非常有幫助。

編輯＃1：

是否所有從站都配置為只讀=1？您的應用程序是否有可能直接寫入從屬設備？

是的。此外，在我們的應用程式碼中，我們確實有一個用於讀寫分離的詳細程式碼。事實上，事務 ( START TRANSACTION .. COMMIT/ROLLBACK) 中的所有查詢總是路由到主伺服器，即使SELECT該塊中有查詢。甚至，為了彌補複製延遲（有時SELECT），我們僅將 DML 操作後的接下來的 2 個查詢路由到 Master。

是否有任何從機配置了從機跳過錯誤？如果是這樣，這個參數的值是多少？

如下請見：

mysql> select @@slave_skip_errors;
+---------------------+
| @@slave_skip_errors |
+---------------------+
| 1032,1062           |
+---------------------+
1 row in set (0.10 sec)

編輯#2

請為 Master 和 Slave 提供 SHOW CREATE TABLE oc_suborder 以及您詳細說明的問題。

主伺服器：

mysql> show create table oc_suborder\G;
*************************** 1. row ***************************
      Table: oc_suborder
Create Table: CREATE TABLE `oc_suborder` (
 `order_id` int(11) NOT NULL,
 `suborder_id` varchar(20) NOT NULL,
 `gst` tinyint(1) NOT NULL DEFAULT '1',
 `buyer_invoice_id` int(11) unsigned DEFAULT NULL,
 `invoice_no` int(11) NOT NULL DEFAULT '0',
 `invoice_date` datetime DEFAULT NULL,
 `invoice_prefix` varchar(26) NOT NULL,
 `shipping_method` varchar(128) NOT NULL,
 `shipping_code` varchar(128) NOT NULL,
 `total` decimal(15,4) DEFAULT NULL,
 `order_status_id` int(11) NOT NULL,
 `date_added` datetime NOT NULL,
 `date_modified` datetime NOT NULL,
 `cform_submit` enum('no_submit','will_submit','submitted','') NOT NULL DEFAULT 'no_submit',
 `cst_with_cform` decimal(15,4) DEFAULT NULL,
 `refundable_cform` decimal(15,4) DEFAULT NULL,
 `refund_status` enum('refunded','not_refunded','not_applicable','') NOT NULL DEFAULT 'not_applicable',
 `courier_partner` varchar(100) DEFAULT NULL,
 `tracking_no` varchar(64) DEFAULT NULL,
 `shipping_charge` decimal(15,2) DEFAULT NULL,
 `custom_totals` text,
 `no_wsb_tape` tinyint(1) NOT NULL DEFAULT '0',
 `no_invoice_with_shipment` tinyint(1) NOT NULL DEFAULT '0',
 `courier_partner_preference` varchar(255) DEFAULT NULL,
 PRIMARY KEY (`suborder_id`),
 UNIQUE KEY `order_id` (`order_id`,`suborder_id`),
 UNIQUE KEY `buyer_invoice_id` (`buyer_invoice_id`),
 KEY `invoice_date` (`invoice_date`),
 KEY `shipping_method` (`shipping_method`),
 KEY `order_status_id` (`order_status_id`),
 KEY `date_added` (`date_added`),
 KEY `tracking_no` (`tracking_no`),
 CONSTRAINT `oc_suborder_order_id_fk` FOREIGN KEY (`order_id`) REFERENCES `oc_order` (`order_id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.08 sec)

從伺服器：

mysql> show create table oc_suborder\G;
*************************** 1. row ***************************
      Table: oc_suborder
Create Table: CREATE TABLE `oc_suborder` (
 `order_id` int(11) NOT NULL,
 `suborder_id` varchar(20) NOT NULL,
 `gst` tinyint(1) NOT NULL DEFAULT '1',
 `buyer_invoice_id` int(11) unsigned DEFAULT NULL,
 `invoice_no` int(11) NOT NULL DEFAULT '0',
 `invoice_date` datetime DEFAULT NULL,
 `invoice_prefix` varchar(26) NOT NULL,
 `shipping_method` varchar(128) NOT NULL,
 `shipping_code` varchar(128) NOT NULL,
 `total` decimal(15,4) DEFAULT NULL,
 `order_status_id` int(11) NOT NULL,
 `date_added` datetime NOT NULL,
 `date_modified` datetime NOT NULL,
 `cform_submit` enum('no_submit','will_submit','submitted','') NOT NULL DEFAULT 'no_submit',
 `cst_with_cform` decimal(15,4) DEFAULT NULL,
 `refundable_cform` decimal(15,4) DEFAULT NULL,
 `refund_status` enum('refunded','not_refunded','not_applicable','') NOT NULL DEFAULT 'not_applicable',
 `courier_partner` varchar(100) DEFAULT NULL,
 `tracking_no` varchar(64) DEFAULT NULL,
 `shipping_charge` decimal(15,2) DEFAULT NULL,
 `custom_totals` text,
 `no_wsb_tape` tinyint(1) NOT NULL DEFAULT '0',
 `no_invoice_with_shipment` tinyint(1) NOT NULL DEFAULT '0',
 `courier_partner_preference` varchar(255) DEFAULT NULL,
 PRIMARY KEY (`suborder_id`),
 UNIQUE KEY `order_id` (`order_id`,`suborder_id`),
 UNIQUE KEY `buyer_invoice_id` (`buyer_invoice_id`),
 KEY `invoice_date` (`invoice_date`),
 KEY `shipping_method` (`shipping_method`),
 KEY `order_status_id` (`order_status_id`),
 KEY `date_added` (`date_added`),
 KEY `tracking_no` (`tracking_no`),
 CONSTRAINT `oc_suborder_order_id_fk` FOREIGN KEY (`order_id`) REFERENCES `oc_order` (`order_id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.07 sec)
  1. 您可以使用pt-table-checksum和pt-table-sync來修復主從之間的數據不匹配問題。這是一篇博文以供進一步參考：https ://www.percona.com/blog/2015/08/12/mysql-replication-primer-with-pt-table-checksum-and-pt-table-sync/
  2. 更新可能直接發送到從站而不是主站。也可以sql_log_bin=0在master上配合使用。read_only=1這可能會發生，但您可以通過使用或設置從屬設備來防止它發生super_read_only=1

引用自：https://dba.stackexchange.com/questions/223726

相關問答

Mysql

是否可以有 1 個從屬伺服器和多個主伺服器？

  • January 29, 2021
檢視問答
Mysql

此錯誤是什麼意思“與此從屬伺服器具有相同 server_uuid/server_id 的從屬伺服器已連接到主伺服器；”？

  • April 12, 2022
檢視問答
Mysql

修復 mysql 複製由於底層儲存損壞而崩潰

  • March 15, 2022
檢視問答
Mysql

我應該寫信給 MySQL 從屬（副本）以進行報告嗎？

  • February 11, 2022
檢視問答
Mysql

MySql 複製失敗

  • February 8, 2022
檢視問答
Mysql

如何避免複製滯後，以防所有在 master 上的寫入和在副本上的讀取？

  • January 31, 2022
檢視問答