Mysql

MySQL Slave 卡在單個 bin 日誌 + bin 日誌位置 17 小時以上

  • February 26, 2021

tl;dr:複製在特定的二進制日誌和位置上停止,我不知道為什麼


我有一個 MySQL 5.5 的 MySQL 複製設置。

這種複制設置沒有落後的歷史,並且一直很穩定。

今天早上,我注意到奴隸比主人晚了 17 個小時。

做更多的研究,它看起來是 SQL_Thread 的一個問題。

目前的主日誌文件,根據從屬(通過SLAVE STATUS),是mysql-bin.001306@position 20520499。這與MASTER STATUSmaster 的輸出一致。

但是,SLAVE STATUS表明Relay_Master_Log_File是目前mysql-bin.001302具有Exec_Master_Log_Pos36573336。今天早上我一直在監視它們時,Relay_Master_Log_Filenor根本沒有進步。Exec_Master_Log_Pos

查看 master 上的 binlogs,這是位於以下位置的語句mysql-bin.001302@3657336

# at 36573053
#170221 14:33:48 server id 1  end_log_pos 36573130      Query   thread_id=96205677      exec_time=0     error_code=0
SET TIMESTAMP=1487716428/*!*/;
BEGIN
/*!*/;
# at 36573130
# at 36573213
#170221 14:33:48 server id 1  end_log_pos 36573213      Table_map: `database-name`.`table-name` mapped to number 5873
#170221 14:33:48 server id 1  end_log_pos 36573309      Write_rows: table id 5873 flags: STMT_END_F
### INSERT INTO `database-name`.`table-name`
### SET
###   @1='xxxxxxxx'
###   @2=6920826
###   @3='xxxxxxxx'
###   @4='GET'
###   @5='address'
###   @6=2017-02-21 14:40:24
###   @7=2017-02-21 14:40:24
# at 36573309
#170221 14:33:48 server id 1  end_log_pos 36573336      Xid = 1668637037
COMMIT/*!*/;
# at 36573336

大約在昨天的這個時候,我確實執行了一些大型查詢以將數據遷移到新表。這個過程看起來有點像這樣;

mysql> insert into tmp_table ( select <rows> from origin table ); -- 44 million rows
mysql> insert into dest_table ( select * from tmp_table ); -- 44 million rows

有問題的兩個表上沒有主鍵或唯一鍵,我讀過這可能是個問題。然而,雖然上面 binlog 條目中顯示的數據庫 + 表是此處的目標表——顯示的插入記錄不是在遷移期間生成的。

如果你已經走到這一步,你應該得到網際網路積分。

在這一點上,我不確定還需要考慮什麼,或者在哪裡尋找日誌停止的原因。任何見解都值得讚賞。

謝謝。


作為參考,這是截至本文發佈時的MASTER STATUS和輸出:SLAVE STATUS

主狀態

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.001306 | 20520499 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

從屬狀態

mysql> show slave status \G
*************************** 1. row ***************************
              Slave_IO_State: Waiting for master to send event
                 Master_Host: master-host
                 Master_User: replication-user
                 Master_Port: 3306
               Connect_Retry: 60
             Master_Log_File: mysql-bin.001306
         Read_Master_Log_Pos: 20520499
              Relay_Log_File: relay-bin.002601
               Relay_Log_Pos: 36573482
       Relay_Master_Log_File: mysql-bin.001302
            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: 36573336
             Relay_Log_Space: 3565987462
             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: 63435
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: 1
1 row in set (0.00 sec)

從昨天開始的大量查詢交易中,我走在了正確的軌道上。

遷移數據後,我在原始表上執行了一條 DELETE 語句,以刪除我已遷移走的行。

這些表只是充滿了跟踪數據,因此它們上沒有任何主鍵或唯一鍵。

由於基於 ROW 的複制是如何工作的,slave 不會執行在 master 上執行的相同 DELETE 語句,而是為每一行執行一個 DELETE 語句,最終看起來像這樣:

DELETE FROM table WHERE colA=foo AND colB=bar AND colC=baz....etc

而且,由於沒有與該查詢匹配的索引,因此單執行緒複製 SQL 執行緒執行了 4000 萬+條刪除語句(或…試圖執行),由於必須進行所有掃描,這需要很長時間才能執行完成以辨識每一行(當時該表的大小約為 8000 萬行)。

最後,我通過停止從屬執行緒 ( STOP SLAVE) 跳過單個從屬事務 ( SET GLOBAL sql_slave_skip_counter = 1;) 並重新啟動從屬執行緒 ( START SLAVE) 來處理這個問題。

這導致我的 Master 和 Slave 在這裡有問題的表上不同步 - 但我能夠利用基於行的複制的性質通過在 Master 上執行以下操作使其恢復同步:

mysql> CREATE TABLE table_tmp; -- with the same schema as 'table' (SHOW CREATE TABLE table;)
mysql> RENAME TABLE table TO table_bak, table_tmp TO table;
mysql> INSERT INTO table ( SELECT * FROM table_bak );
mysql> DROP TABLE table_bak;

由於DELETE是在Master上執行的,所以這裡的INSERT只插入了我想保留的記錄(刪除的已經沒有了)。而且,由於基於行的複制單獨插入每一行而不是執行相同的 INSERT INTO…SELECT 語句,所以從表只填充了所需的數據。然後,隨後的 DROP TABLE 語句刪除從屬上的表,而不必單獨處理每一行。

這裡需要注意的是,因為表的主版本仍然是 30-40 百萬行……插入和後續複製最終會鎖定你的從屬一段時間(重複上面的問題),但它的停頓時間要短得多(最終大約是 20 分鐘)由於 mysql 不必掃描數據庫以刪除要刪除的行。

我希望這對將來的某人有所幫助。對不起,它很囉嗦,希望它提供資訊和幫助。

引用自:https://serverfault.com/questions/834232