- 1. 背景
- 2. 疑點
- 3. 問題分析
- 4. 問題定位
- 5. checking permissions的疑惑
- 6. 探索優化思路
- 7. 補充：關于幾個timeout參數生效點
行内資料庫備份在使用某備份軟體，使用的資料庫版本
MySQL 8.0
社群版，全備使用
mysqldump
進行，DBA早上巡檢發現有一套資料庫全備份失敗，心裡一疙瘩怎麼回事呢？來看看如下報錯
[mysqldump: Error: 'Lost connection to MySQL server during query' when trying to dump tablespaces mysqldump: couldn't execute 'SHOW VARIABLES LIKE 'ndbinfo'\_version' MySQL server has gone away (2006)] 肯定有同學有疑問？
- 為什麼mysqldump會出現丢失連接配接？
- 為什麼不使用xtrabackup呢？這需要另外章節來闡述了。
為什麼mysqldump會出現丢失連接配接？帶着該問題進行以下分析：
- 1.檢查備份軟體工具負載情況
- 2.檢查資料庫中錯誤日志
- 3.資料庫的負載情況
3.1 備份軟體是否存在高負載、排隊或逾時配置導緻響應逾時？
對整個備份系統進行排查，雖然備份系統任務多，但并沒有出現性能瓶頸導緻資料庫備份時逾時，備份軟體也沒有設定備份逾時時間自動斷開的相關配置
3.2 檢查資料庫錯誤日志
2020-10-26T01:31:14.465387+08:00 149718 [Note] [MY-010914] [Server] Aborted connection 149718 to db: 'unconnected' user: 'root' host: 'localhost' (Got an error reading communication packets). 通過資料庫錯誤日志發現同備份軟體報錯一樣，對于這個錯誤，MOS上有一個比較好的解釋如下：
不管怎麼樣我們後面先來看備份軟體觸發了些什麼語句。
3.3 檢查資料庫負載情況，備份期間cpu、io均比較正常
從上述檢查來看，報錯處是Got an error reading而不是timeout，關于timeout的觸發方式我們最後總結。首先從備份軟體架構，備份軟體在資料庫中部署agent，是以連接配接屬于互動式連接配接受到參數interactive_timeout的影響，那麼為什麼導緻的逾時丢失連接配接的呢？我們可以進行問題複現，使用備份軟體對資料庫發起重新備份，對資料庫進行監控
4.1 通過備份軟體發起備份，可以看到會發起4個本地備份連接配接，3個處于sleep狀态，一個線程處于執行狀态下，如下：
SQL語句：
SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'UNDO LOG' AND FILE_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IN (SELECT DISTINCT LOGFILE_GROUP_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN ('xxx'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE, TOTAL_EXTENTS, INITIAL_SIZE ORDER BY LOGFILE_GROUP_NAME 注意這裡的狀态為checking permissions，并且這個語句長期處于這個狀态。我們以前了解的這個就是在鑒權，我們一般的認知是下面一些順序：
- starting:lex+yacc 文法語義解析,得到解析樹
- checking permissions：根據解析後的解析樹，對需要通路的表進行鑒權
- opening tables：打開通路的表，建立内部通路表的屬性（表和字段資訊），建立好和Innodb的關聯，同時加上表鎖（MDL LOCK）
- optimizing/statistics/preparing：這3個狀态處于語句的實體和邏輯優化階段，之後建立好執行計劃
- Sending data（ 8.0為executing）：select語句MySQL層和Innodb層進行資料互動，遇到這個狀态通常考慮語句是否足夠優化
- Update：同上insert語句，如果遇到行鎖會處于這個狀态下。
- Updating：同上delete/update語句，如果遇到行鎖會處于這個狀态下。
- query end（waiting for handler commit 8.0）：語句的送出過程包含在這個狀态下，遇到這個問題，主要考慮是否大事務的存在。
- closing tables：和opening tables對應，釋放表的内部通路版本放入緩存共下次使用，同時也包含語句的錯誤復原也在這個狀态下
- freeing items：釋放解析樹
我們能夠看到，鑒權實際上在比較靠前的位置，是不是說這裡語句還沒真正的開始執行呢？我們先放一放。
4.2 通過執行show processlist發現上述SQL一直處于運作狀态，于是終止備份，手工運作該SQL
由此可以推測發現，備份軟體在發起備份時會發起4個連接配接，而其中一個連接配接執行SQL比較久，而另外3個sleep連接配接在超過interactive_timeout後斷開，導緻agent整體退出關閉所有的資料庫連接配接，執行的SQL也終止，是以報錯Got an error reading。但是奇怪的是我們在日志并沒有找到Got timeout reading communication packets的日志。