技術分享 | MySQL Shell 收集 MySQL 診斷報告（上）

愛可生雲資料庫發表於2023-01-17
MySql

作者：楊濤濤

資深資料庫專家，專研 MySQL 十餘年。擅長 MySQL、PostgreSQL、MongoDB 等開源資料庫相關的備份恢復、SQL 調優、監控運維、高可用架構設計等。目前任職於愛可生，為各大運營商及銀行金融企業提供 MySQL 相關技術支援、MySQL 相關課程培訓等工作。

本文來源：原創投稿

*愛可生開源社群出品，原創內容未經授權不得隨意使用，轉載請聯絡小編並註明來源。

通常對於MySQL執行慢、異常執行等等現象，需要透過收集當時的診斷報告以便後期重點分析並且給出對應解決方案。對於MySQL來講，目前收集診斷報告的方法大致有以下幾類：

  1. 手動寫指令碼收集。
  2. Percona-toolkit工具集裡自帶的pt-stalk。
  3. MySQL的sys庫自帶儲存過程diagnostics。
  4. MySQL Shell 工具的util 元件（需升級到MySQL 8.0.31 最新版才能體驗全部診斷程式）

這些工具基本上都可以從不同程度收集OS 以及MySQL SERVER 的診斷資料，並且生成對應的診斷報告。 今天我們來介紹MySQL Shell 最新版本8.0.31 的util元件帶來的全新診斷報告收集功能。

util.debug屬性有三個診斷函式：

  1. collect_diagnostics 用來收集單例項、副本集、InnoDB Cluster 的診斷資料。（<u>舊版本8.0.30 也可以用，不過功能不是很全面</u>）
  2. collect_high_load_diagnostics 用來迴圈多次收集，並找出負載異常的診斷資料。
  3. collect_slow_query_diagnostics 用來對函式collect_diagnostics收集到的慢日誌做進一步分析。
今天我們先來介紹第一個函式collect_diagnostics 如何使用：

函式collect_diagnostics 用來收集如下診斷資料並給出對應診斷報告：

  1. 無主鍵的表
  2. 死索引的表
  3. MySQL錯誤日誌
  4. 二進位制日誌後設資料
  5. 副本集狀態（包含主庫和從庫）
  6. InnoDB Cluster 監控資料
  7. 表鎖、行鎖等資料
  8. 當前連線會話資料
  9. 當前記憶體資料
  10. 當前狀態變數資料
  11. 當前MySQL 慢日誌（需主動開啟開關）
  12. OS 資料（CPU、記憶體、IO、網路、MySQL程式嚴重錯誤日誌過濾等）

函式collect_diagnostics 有兩個入參：一個是輸出路徑；另一個是可選字典配置選項，比如可以配置慢日誌收集、定製執行SQL 語句、定製執行SHELL命令等等。

以下是常用呼叫示例：

  1. 只傳遞引數1，給定診斷資料打包輸出路徑，診斷報告會整體打包為/tmp/cd1.zip。
util.debug.collect_diagnostics('/tmp/cd1')
  1. 啟用慢日誌抓取（必需條件：MySQL慢日誌開關開啟、日誌輸出格式為TABLE），診斷報告會整體打包為/tmp/cd2.zip，並且包含慢日誌診斷報告。
util.debug.collect_diagnostics('/tmp/cd2',{"slowQueries":True})
  1. 定製執行SQL： 收集預置診斷報告同時也收集給定的SQL語句執行結果。
util.debug.collect_diagnostics('/tmp/cd3',{"customSql":["select * from ytt.t1 order by id desc limit 100"]})
  1. 定製執行SHELL命令： 收集預置診斷報告同時也收集給定的SHELL命令執行結果。
util.debug.collect_diagnostics('/tmp/cd4',{"customShell":["ps aux | grep mysqld"]})
  1. 收集所有成員診斷資料（副本集或者InnoDB Cluster）。
util.debug.collect_diagnostics('/tmp/cd5',{"allMembers":True})

分別執行以上5條命令，在/tmp目錄下會生成如下檔案： 以下5個打包檔案即是我們執行的5條命令的結果。

root@ytt-pc:/tmp# ll cd*
-rw------- 1 root root  893042 1月   5 10:31 cd1.zip
-rw------- 1 root root  818895 1月   5 10:55 cd2.zip
-rw------- 1 root root  819183 1月   5 11:02 cd3.zip
-rw------- 1 root root  835387 1月   5 11:06 cd4.zip
-rw------- 1 root root 2040913 1月   5 11:31 cd5.zip

要檢視具體診斷報告，得先解壓這些檔案。先來看下cd2.zip 解壓後的內容：對於收集的診斷資料，有tsv和yaml兩種格式的報告檔案。報告檔案以數字0開頭，表示這個診斷報告來自一臺單例項MySQL。

root@ytt-pc:/tmp/cd/cd2# ls|more
0.error_log
0.global_variables.tsv
0.global_variables.yaml
0.information_schema.innodb_metrics.tsv
0.information_schema.innodb_metrics.yaml
0.information_schema.innodb_trx.tsv
0.information_schema.innodb_trx.yaml
0.instance
0.metrics.tsv
0.performance_schema.events_waits_current.tsv
0.performance_schema.events_waits_current.yaml
0.performance_schema.host_cache.tsv
0.performance_schema.host_cache.yaml
0.performance_schema.metadata_locks.tsv
0.performance_schema.metadata_locks.yaml
...

比如檢視此例項的連線字串：

root@ytt-pc:/tmp/cd/cd2# cat 0.uri 
mysql://root@localhost:3306

以下為對應的慢日誌報告：分別為慢日誌資料、95分位慢日誌資料以及根據掃描行數排序的慢日誌資料。

root@ytt-pc:/tmp/cd/cd2# ls *slow*
0.slow_log.tsv   0.slow_queries_in_95_pctile.tsv   0.slow_queries_summary_by_rows_examined.tsv
0.slow_log.yaml  0.slow_queries_in_95_pctile.yaml  0.slow_queries_summary_by_rows_examined.yaml

cd1.zip、cd2.zip、cd3.zip、cd4.zip 都是基於單例項收集的診斷報告，解壓後的檔案都是以0開頭；cd5.zip是基於副本集收集的診斷報告，解壓後的檔案是以1，2，3開頭，分別代表例項3310，3311，3312。

比如檢視副本集裡3個成員的連線字串：

root@ytt-pc:/tmp/cd/cd5# cat {1,2,3}.uri
mysql://root@127.0.0.1:3310?ssl-mode=required
mysql://root@127.0.0.1:3311?ssl-mode=required
mysql://root@127.0.0.1:3312?ssl-mode=required

目前副本集的拓撲： 3310 為主，3311，3312為從，可以在主庫上執行show replicas 命令得到從庫列表

 MySQL  localhost:3310 ssl  SQL > show replicas;
+------------+-----------+------+------------+--------------------------------------+
| Server_Id  | Host      | Port | Source_Id  | Replica_UUID                         |
+------------+-----------+------+------------+--------------------------------------+
| 2736952196 | 127.0.0.1 | 3312 | 4023085694 | 0824a675-8ca9-11ed-a719-0800278da4ac |
| 3604736168 | 127.0.0.1 | 3311 | 4023085694 | 0526130e-8ca9-11ed-b797-0800278da4ac |
+------------+-----------+------+------------+--------------------------------------+
2 rows in set (0.0002 sec)

從診斷報告裡檢視例項3310 的從庫列表：

root@ytt-pc:/tmp/cd/cd5# cat 1.SHOW_REPLICAS.yaml

...

#
Host: 127.0.0.1
Port: 3312
Replica_UUID: 0824a675-8ca9-11ed-a719-0800278da4ac
Server_Id: 2736952196

Source_Id: 4023085694
---

Host: 127.0.0.1
Port: 3311
Replica_UUID: 0526130e-8ca9-11ed-b797-0800278da4ac
Server_Id: 3604736168
Source_Id: 4023085694

結語：

MySQL Shell 8.0.31 帶來的增強版收集診斷報告功能，能更好的彌補MySQL在這一塊的空缺，避免安裝第三方工具，從而簡化DBA的運維工作。

相關文章