MySQL Slave 卡在單個 bin 日誌 + bin 日誌位置 17 小時以上
tl;dr:複製在特定的二進制日誌和位置上停止,我不知道為什麼
我有一個 MySQL 5.5 的 MySQL 複製設置。
這種複制設置沒有落後的歷史,並且一直很穩定。
今天早上,我注意到奴隸比主人晚了 17 個小時。
做更多的研究,它看起來是 SQL_Thread 的一個問題。
目前的主日誌文件,根據從屬(通過
SLAVE STATUS
),是mysql-bin.001306
@position20520499
。這與MASTER STATUS
master 的輸出一致。但是,
SLAVE STATUS
表明Relay_Master_Log_File
是目前mysql-bin.001302
具有Exec_Master_Log_Pos
的36573336
。今天早上我一直在監視它們時,Relay_Master_Log_File
nor根本沒有進步。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 不必掃描數據庫以刪除要刪除的行。
我希望這對將來的某人有所幫助。對不起,它很囉嗦,希望它提供資訊和幫助。