DBA的驱动力是爱好从命令行监控MySQL

##

DBA的驱动力是爱好–从命令行监控MySQL

今年的 非常有意思，介绍了很多东西，后续的文章会逐一安排 的内容，今天我们来看第一个主题

Monitoring-MySQL-from-the-command-line，从命令行监控MySQL。

1、GUI监控解决方案

首先是列举了很多(看图片就四个，其他还有SigNoz，Prometheus，MySQL Enterprise Monitor，Paessler PRTG Network Monitor，Sematext，SolarWinds，Dynatrace，DataDog，New Relic，ManageEngine Applications Manager，Appdynamics)。其他的可以参考https://signoz.io/blog/mysql-monitoring-tools/

2、为什么选择命令行监控MySQL

一句话：Because it’s cool（怎么说呢，很羡慕这种状态）

其他不重要的原因：

• Faster ( to deploy,to find the info)
• Lighter
• Only available option
• but often not suitable for forensics analysis

3、非重点介绍

InnoTop

mytop


4、innotopGo安装与介绍

这个工具主要是两个点。MySQL 8.x only 和 written in Go

#安装手册有点问题，按下面的方式安装即可

安装

yum install -y go


下载

innotopgo-linux_static-0.3.2.tar.gz


解压

tar -zxvf innotopgo-linux_static-0.3.2.tar.gz


使用

./innotopgo-linux_static mysql://root:root@localhost:3306


查看帮助Shift+?

展示界面

总结：还不是很完善

5、dolphie介绍与安装

安装要求Python 3.8+，另外还有其他方法安装，自行查看了，pip最简单

[root@pgdb01 ~]# python --version
Python 3.9.18

[root@pgdb01 ~]# pip install dolphie
Collecting dolphie
Downloading dolphie-4.3.2-py3-none-any.whl (86 kB)
|████████████████████████████████| 86 kB 9.7 kB/s
Collecting packaging=23.2
Downloading packaging-23.2-py3-none-any.whl (53 kB)
|████████████████████████████████| 53 kB 6.9 kB/s
Collecting myloginpath=0.0.4
Downloading myloginpath-0.0.4-py3-none-any.whl (5.0 kB)
Collecting plotext=5.2.8
Downloading plotext-5.2.8-py3-none-any.whl (60 kB)
|████████████████████████████████| 60 kB 7.8 kB/s
Collecting sqlparse=0.4.4
Downloading sqlparse-0.4.4-py3-none-any.whl (41 kB)
|████████████████████████████████| 41 kB 9.8 kB/s
Collecting pymysql=1.1.0
Downloading PyMySQL-1.1.0-py3-none-any.whl (44 kB)
|████████████████████████████████| 44 kB 14 kB/s
Collecting charset-normalizer=3.2.0
Downloading charset_normalizer-3.3.2-cp39-cp39-manylinux_2_17_x86_64.manylinux2014_x86_64.whl (142 kB)
|████████████████████████████████| 142 kB 8.9 kB/s
Collecting requests=2.31.0
Downloading requests-2.31.0-py3-none-any.whl (62 kB)
|████████████████████████████████| 62 kB 20 kB/s
Collecting rich=13.7.0
Downloading rich-13.7.1-py3-none-any.whl (240 kB)
|████████████████████████████████| 240 kB 8.3 kB/s
Collecting textual=0.52.1
Downloading textual-0.52.1-py3-none-any.whl (530 kB)
|████████████████████████████████| 530 kB 10 kB/s
Collecting textual-autocomplete=2.1.0b0
Downloading textual_autocomplete-2.1.0b0-py3-none-any.whl (9.1 kB)
Collecting cryptography
Downloading cryptography-42.0.5-cp39-abi3-manylinux_2_28_x86_64.whl (4.6 MB)
|████████████████████████████████| 4.6 MB 1.8 MB/s
Collecting certifi>=2017.4.17
Downloading certifi-2024.2.2-py3-none-any.whl (163 kB)
|████████████████████████████████| 163 kB 2.2 MB/s
Requirement already satisfied: idna=2.5 in /usr/lib/python3.9/site-packages (from requests=2.31.0->dolphie) (2.10)
Requirement already satisfied: urllib3=1.21.1 in /usr/lib/python3.9/site-packages (from requests=2.31.0->dolphie) (1.26.5)
Collecting pygments=2.13.0
Downloading pygments-2.17.2-py3-none-any.whl (1.2 MB)
|████████████████████████████████| 1.2 MB 2.2 MB/s
Collecting markdown-it-py>=2.2.0
Downloading markdown_it_py-3.0.0-py3-none-any.whl (87 kB)
|████████████████████████████████| 87 kB 2.5 MB/s
Collecting mdurl~=0.1
Downloading mdurl-0.1.2-py3-none-any.whl (10.0 kB)
Collecting typing-extensions=4.4.0
Downloading typing_extensions-4.10.0-py3-none-any.whl (33 kB)
Collecting linkify-it-py=1
Downloading linkify_it_py-2.0.3-py3-none-any.whl (19 kB)
Collecting mdit-py-plugins
Downloading mdit_py_plugins-0.4.0-py3-none-any.whl (54 kB)
|████████████████████████████████| 54 kB 2.5 MB/s
Collecting uc-micro-py
Downloading uc_micro_py-1.0.3-py3-none-any.whl (6.2 kB)
Collecting cffi>=1.12
Downloading cffi-1.16.0-cp39-cp39-manylinux_2_17_x86_64.manylinux2014_x86_64.whl (443 kB)
|████████████████████████████████| 443 kB 3.0 MB/s
Collecting pycparser
Downloading pycparser-2.21-py2.py3-none-any.whl (118 kB)
|████████████████████████████████| 118 kB 3.0 MB/s
Installing collected packages: mdurl, uc-micro-py, markdown-it-py, pygments, pycparser, mdit-py-plugins, linkify-it-py, typing-extensions, rich, cffi, textual, cryptography, charset-normalizer, certifi, textual-autocomplete, sqlparse, requests, pymysql, plotext, packaging, myloginpath, dolphie
Successfully installed certifi-2024.2.2 cffi-1.16.0 charset-normalizer-3.3.2 cryptography-42.0.5 dolphie-4.3.2 linkify-it-py-2.0.3 markdown-it-py-3.0.0 mdit-py-plugins-0.4.0 mdurl-0.1.2 myloginpath-0.0.4 packaging-23.2 plotext-5.2.8 pycparser-2.21 pygments-2.17.2 pymysql-1.1.0 requests-2.31.0 rich-13.7.1 sqlparse-0.4.4 textual-0.52.1 textual-autocomplete-2.1.0b0 typing-extensions-4.10.0 uc-micro-py-1.0.3
WARNING: Running pip as the 'root' user can result in broken permissions and conflicting behaviour with the system package manager. It is recommended to use a virtual environment instead: https://pip.pypa.io/warnings/venv


使用

dolphie -uri mysql://root:root@192.168.5.130:3306


查看帮助同样是Shift+?。

使用界面展示

还可以多个界面

6、mysqlsh

collectDiagnostics()
collectHighLoadDiagnostics()
collectSlowQueryDiagnostics()


这三个功能在mysqlsh中可以帮助我们获取一些巡检信息，之前的文章也讲过了，

但最重要的是可以和python结合生产图形化的结果集

-------------------------------------------------------------------------------------------------------------