MySQL 5.7並發複製和mysqldump相互阻塞引起的複寫延遲

標籤：pos   http   位置   roc   where   技術分享   set   com   isolation   

本來MySQL BINLOG和SHOW PROCESSLIST命令屬於八竿子打不著的兩個事務，但在最近故障排查中，發現主庫和從庫已經存在很嚴重的複寫延遲，但從庫上顯示slave_behind_master值為0，複製SQL線程與備份線程之間相互阻塞，但未報死結。

在從庫上執行SHOW PROCESSLIST發現複製的SQL線程等待鎖，而等待SQL的WHERE條件竟然是類似於WHERE C1=‘ABC‘ AND C2>‘2018-03-01‘ AND C2<‘2018-03-26‘ 這種個範圍查詢，第一時間想到就是怎麼是個基於STATEMENT的複製，不科學啊，我們生產環境統一使用基於ROW格式的複製，難道研發私自修改回話層級的複製格式？

使用MySQL Binlog匯出日誌一看：

發現真錯怪研發同事啦，rbr_only=yes說明基於ROW格式進行複製，“SET TRANSACTION ISOLATION LEVEL READ COMMITTED”也是基於行格式複製的典型特徵之一，last_committed和sequence_number用於MySQL 5.7版本中的並發複製，row_query後跟的是在主庫上執行的原始SQL，也就是我們在從庫SHOW PROCESSLIST中看到的SQL，但實際上從庫執行的還是BINLOG部分，該BINLOG可以直接可以直接直接在從庫上執行，也可以解析成一行行的資料DML操作，BINLOG部分如下：

 

==========================================================================================================

另外一個很有意思的問題，如果在從庫上運行mysqldump進行備份，且從庫上使用並行複製，會導致備份和複製相互阻塞：

在上面的阻塞中，多個SQL線程與備份線程相互之間阻塞，且MySQL無法有效檢測出死結環路而觸發死結的復原機制，導致複製線程和備份作業相互hang住，需要DBA進行幹預（取消備份或停止複製），在複製SQL線程被hang住期間，複製的IO線程仍可以正常工作接受到主庫的Binlog資訊，但slave_behind_master並不會隨之增大，如果僅通過監控slave_behind_master值來判斷主從複寫延遲，則會導致延遲監控存在嚴重漏洞，因此在監控複寫延遲時，除監控slave_behind_master值外，還需要監控主庫binlog位置點和從庫執行的binlog位置點。

==========================================================================================================

打完收工，妹子鎮貼：

 

MySQL 5.7並發複製和mysqldump相互阻塞引起的複寫延遲