技術分享 | 一款功能全面的 MySQL Shell 外掛
作者：楊濤濤
資深資料庫專家，專研 MySQL 十餘年。擅長 MySQL、PostgreSQL、MongoDB 等開源資料庫相關的備份恢復、SQL 調優、監控運維、高可用架構設計等。目前任職於愛可生，為各大運營商及銀行金融企業提供 MySQL 相關技術支援、MySQL 相關課程培訓等工作。
本文來源：原創投稿
安裝非常簡單，建立對應的 MySQL Shell Plugins 目錄，完了把外掛整個複製到此即可。
mkdir -p ~/.mysqlsh/plugins
git clone ~/.mysqlsh/plugins
我來簡單演示其中幾個元件：
第一，複製元件：replication 。
目前的拓撲是這樣的：我本地搭了三個例項 127.0.0.1:3310 、127.0.0.1:3311 、127.0.0.1:3312 。這三個例項組建了一個副本集，3310是主，3311和3312是從。
MySQL localhost:3311 ssl Py > rs.status()
{
"replicaSet": {
"name": "rs1",
"primary": "127.0.0.1:3310",
...
"topology": {
"127.0.0.1:3310": {
"address": "127.0.0.1:3310",
"instanceRole": "PRIMARY",
"mode": "R/W",
"status": "ONLINE"
},
"127.0.0.1:3311": {
"address": "127.0.0.1:3311",
"instanceRole": "SECONDARY",
"mode": "R/O",
...
"status": "ONLINE"
},
"127.0.0.1:3312": {
"address": "127.0.0.1:3312",
"instanceRole": "SECONDARY",
"mode": "R/O",
...
"status": "ONLINE"
}
},
"type": "ASYNC"
}
}
這裡我主動製造了一個錯誤，完後連線到其中一臺從庫上使用 replication.status() 獲取本機複製狀態：此時 SQL 執行緒停止，並且顯示延遲20秒。
MySQL localhost:3312 ssl Py > replication.status();
+--------------+-----------+------------+------------+
| channel_name | IO_thread | SQL_thread | lag_in_sec |
+--------------+-----------+------------+------------+
| | ON | OFF | 20 |
+--------------+-----------+------------+------------+
使用 replication.error() 來獲取詳細錯誤：發現GTID 923dad27-8528-11ed-8796-080027382b72:78 執行失敗，失敗原因是主鍵重複。
MySQL localhost:3312 ssl Py > replication.error()
Applier error:
2022-12-26 22:37:35.146527 : Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction '923dad27-8528-11ed-8796-080027382b72:78' at master log ytt-pc-bin.000003, end_log_pos 1105. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
2022-12-26 22:37:35.146532 : Worker 1 failed executing transaction '923dad27-8528-11ed-8796-080027382b72:78' at master log ytt-pc-bin.000003, end_log_pos 1105; Could not execute Write_rows event on table ytt.t1; Duplicate entry '10' for key 't1.PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log ytt-pc-bin.000003, end_log_pos 1105
再使用 replication.get_gtid_to_skip() 來確認具體的 GTID ：確認無疑是923dad27-8528-11ed-8796-080027382b72:78。
MySQL localhost:3312 ssl Py > replication.get_gtid_to_skip()
+--------------+-----------------------------------------+
| channel_name | gtid_to_skip |
+--------------+-----------------------------------------+
| | 923dad27-8528-11ed-8796-080027382b72:78 |
+--------------+-----------------------------------------+
MySQL localhost:3312 ssl Py > replication.skip_error();
skiping 923dad27-8528-11ed-8796-080027382b72:78 for replication channel ''...
完後在從庫重啟複製執行緒：stop replica;start replica ;
MySQL localhost:3312 ssl SQL > stop replica;start replica;
Query OK, 0 rows affected (0.0194 sec)
Query OK, 0 rows affected (0.0347 sec)
再次檢視複製是否有錯誤以及當前複製狀態：沒有錯誤，一切恢復正常。
MySQL localhost:3312 ssl Py > replication.error();
no error
MySQL localhost:3312 ssl Py > replication.status();
+--------------+-----------+------------+------------+
| channel_name | IO_thread | SQL_thread | lag_in_sec |
+--------------+-----------+------------+------------+
| | ON | ON | 0 |
+--------------+-----------+------------+------------+
第二、獲取鎖相關資料的元件：locks 。
locks 元件可以獲取當前事務的鎖資料，包括當前鎖持有以及鎖等待相關執行緒 ID 、鎖持有時間、記憶體佔用、鎖的具體行數等。下面使用 locks.get_locks() 來獲取當前鎖相關資料：
MySQL localhost:3310 ssl Py > locks.get_locks()
+-----------------+--------------+-------------+-----------+----------------+-------------------+-------------------+-------------------------------------------------------------------+
| mysql_thread_id | trx_duration | cpu_latency | memory | row_locks_held | row_locks_pending | tables_with_locks | current_statement |
+-----------------+--------------+-------------+-----------+----------------+-------------------+-------------------+-------------------------------------------------------------------+
| 25 | 4.43 min | 0 ps | 2.07 MiB | 3 | 0 | ytt.t1 | NULL |
| 29 | 17.51 s | 0 ps | 2.49 MiB | 1 | 0 | ytt.t1 | update t1 set r1 = 100 where id =10 |
| 7 | 10.69 ms | | 15.66 KiB | 0 | 0 | NULL | NULL |
| 27 | 269.67 us | 0 ps | 10.31 MiB | 0 | 0 | NULL | SELECT thr.processlist_id AS m ... IT DESC LIMIT 10 |
| 13 | 136.45 us | 0 ps | 1.19 MiB | 0 | 0 | NULL | NULL |
+-----------------+--------------+-------------+-----------+----------------+-------------------+-------------------+-------------------------------------------------------------------+
根據後續提示，還可以獲取更多的資料：比如執行緒25，持有 IX 意向鎖以及 X 記錄鎖，以及當前等待此鎖的 SQL 語句；執行緒29申請X記錄鎖卻被執行緒25阻塞。
For which thread_id do you want to see locks ? (25) : 25
Metadata Locks:
---------------
GRANTED SHARED_WRITE on ytt.t1
Data Locks:
-----------
GRANTED TABLE (IX) LOCK ON ytt.t1
GRANTED RECORD (X) LOCK ON ytt.t1 (PRIMARY) (id=supremum pseudo-record)
(id=10)
BLOCKING RECORD (X,REC_NOT_GAP) LOCK ON ytt.t1 FOR 12 SECONDS (mysql_thread_id: 29)
Statement we are blocking:
--------------------------
update t1 set r1 = 100 where id =10
For which thread_id do you want to see locks ? (25) : 29
Metadata Locks:
---------------
GRANTED SHARED_WRITE on ytt.t1
Data Locks:
-----------
GRANTED TABLE (IX) LOCK ON ytt.t1
WAITING RECORD (X,REC_NOT_GAP) LOCK ON ytt.t1 (PRIMARY) (id=10)
BLOCKED FOR 8 SECONDS BY (mysql_thread_id: 25)
第三、獲取語句執行的 profiles 資料：profiling 元件。
先執行 profiling.start() ，完後執行些語句，再用 profiling.get() 來獲取具體的 profiles 資料，最後再關閉 profiling 收集。
MySQL localhost:3310 ssl ytt Py > profiling.get()
Last 5 events from the proccess list id: @@pseudo_thread_id
-----------------------------------------------------------
---Events before profiling was started are in orange---
1563 : select schema()
1555 : help 'profiling'
1551 : profiling.get()
1531 : select * from t1 order by rand() limit 10
1512 : select count(*) from t1
Which event do you want to profile ? : 1531
Profiling of:
-------------
select * from t1 order by rand() limit 10
duration: 0.0162
+------------------------------------------------+----------+
| Stage | Duration |
+------------------------------------------------+----------+
| stage/sql/starting | 0.0000 |
| stage/sql/Executing hook on transaction begin. | 0.0000 |
| stage/sql/starting | 0.0000 |
| stage/sql/checking permissions | 0.0000 |
| stage/sql/Opening tables | 0.0000 |
| stage/sql/init | 0.0000 |
| stage/sql/System lock | 0.0000 |
| stage/sql/optimizing | 0.0000 |
| stage/sql/statistics | 0.0000 |
| stage/sql/preparing | 0.0000 |
| stage/sql/Creating tmp table | 0.0000 |
| stage/sql/executing | 0.0160 |
| stage/sql/end | 0.0000 |
| stage/sql/query end | 0.0000 |
| stage/sql/waiting for handler commit | 0.0000 |
| stage/sql/closing tables | 0.0000 |
| stage/sql/freeing items | 0.0000 |
| stage/sql/cleaning up | 0.0000 |
+------------------------------------------------+----------+
Don't forget to stop the profiling when done.
第四、連線主庫使用 innodb 元件檢視有碎片的表，並且獲取表大小、索引大小、碎片率等資料。
MySQL localhost:3310 ssl ytt Py > innodb.get_fragmented_tables_disk()
Warning: information_schema_stats_expiry is set to 86400.
Do you want to change it ? (y/N) : n
+--------+-------+-----------+------------+------------+-----------+-----------+-------------+--------+
| NAME | ROWS | DATA_SIZE | INDEX_SIZE | TOTAL_SIZE | DATA_FREE | FILE_SIZE | WASTED_SIZE | FREE |
+--------+-------+-----------+------------+------------+-----------+-----------+-------------+--------+
| ytt/t1 | 28861 | 4.52 MiB | 0 bytes | 4.52 MiB | 4.00 MiB | 12.00 MiB | 7.48 MiB | 62.37% |
+--------+-------+-----------+------------+------------+-----------+-----------+-------------+--------+
Don't forget to run 'ANALYZE TABLE ...' for a more accurate result.
第五、使用 check 元件來獲取 binlog 的 IO 資料：由於格式不好看，我截了張圖。可以展示現存所有 binlog 檔案的讀寫頻率、讀寫資料等資訊。
本文關鍵字：#MySQL Shell# #MySQL Shell Plugins#
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/70024923/viewspace-2930063/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Shell（一）：功能、配置和外掛
- 技術分享 | MySQL Shell 收集 MySQL 診斷報告（上）MySql
- 剖析外掛技術 (轉)
- 推薦一款 Flutter Push 推送功能外掛Flutter
- 外掛化技術：宿主訪問外掛資源
- 遊戲反外掛技術揭祕遊戲
- 外掛輔助技術研究教程
- 跨瀏覽器外掛技術瀏覽器
- jquery實現的全選和刪除功能外掛jQuery
- 技術乾貨 | 反外掛技術的革新：如何有效應對 FPS 外掛的威脅
- framework外掛化技術-類載入Framework
- 分享一款免費開源的線上文件管理外掛
- 推薦一款顏值逆天且功能齊全的開源Shell工具
- Router:一款單品、元件化、外掛化全支援的路由框架元件化路由框架
- 技術分享 | ProxySQL 搭配 MySQL HA （下）MySql
- 外掛製作--------過NP技術漸漸
- [外掛擴充套件]百度分享外掛套件
- MySQL外掛呼叫MySql
- PC端時間日曆外掛 功能齊全 無依賴
- 非常全面的jquery 外掛網站jQuery網站
- jQuery A+ ，一款用於增強a元素標籤功能的外掛jQuery
- 社會化分享外掛整合分享
- wemall全棧移動商城技術架構分享全棧架構
- vscode外掛分享VSCode
- 解密｜一文帶你看懂外掛技術解密
- 技術分享 | MySQL InnoDB Cluster Set 介紹MySql
- 技術分享 | MySQL 如何適配 AppArmorMySqlAPP
- 技術分享 | MySQL : SSL 連線淺析MySql
- 技術分享 | MySQL：change buffer 何時生效MySql
- jQuery外掛開發全解析jQuery
- Mybatis-plus外掛功能MyBatis
- 一款好用的Java外掛 - LombokJavaLombok
- Eclipse下面的ClearCase外掛安裝Eclipse
- Android外掛化技術之旅 1 開篇 - 實現啟動外掛與呼叫外掛中的Activity和ServiceAndroid
- 『技術分享』-- 使用極光 IM 構建聊天功能
- MySQL的一些功能實用的Linux shell指令碼分享MySqlLinux指令碼
- 記一次mysql高可用技術分享MySql
- vim自動補全外掛配置