（二） prometheus + grafana + alertmanager 配置Mysql监控

太过爱你忘了你带给我的痛 2022-10-02 03:51 190阅读 0赞

** 安装请看[https://blog.51cto.com/liuqs/2027365][https_blog.51cto.com_liuqs_2027365] ，最好是对应的版本组件，否则可能会有差别。**

[（一）prometheus + grafana + alertmanager 配置主机监控][prometheus _ grafana _ alertmanager]

[（二）prometheus + grafana + alertmanager 配置Mysql监控][prometheus _ grafana _ alertmanager _Mysql]

[（三）prometheus + grafana + alertmanager 配置Redis监控][prometheus _ grafana _ alertmanager _Redis]

[（四）prometheus + grafana + alertmanager 配置Kafka监控][prometheus _ grafana _ alertmanager _Kafka]

[（五）prometheus + grafana + alertmanager 配置ES监控][prometheus _ grafana _ alertmanager _ES]

（二）prometheus + grafana + alertmanager 配置Mysql监控

1.  mysqld\_exporter安装与配置
    
    A. mysqld服务安装在每台Linux服务器上
    
    1.  下载mysqld\_exporter到每台mysqld服务器上（下载地址： [https://pan.baidu.com/s/1pW7RptzXa3LqFlO5zxJXPw][https_pan.baidu.com_s_1pW7RptzXa3LqFlO5zxJXPw] ），并解压到/data/monitor/下
    2.  安装go环境， yum install go -y
    3.  用root用户连接当前mysql，授权监控用户
        
        <table> 
         <tbody style="border:0px;"> 
          <tr style="border-width:1px 0px 0px;border-top-style:solid;border-top-color:rgb(221,221,221);"> 
           <td style="color:rgb(79,79,79);line-height:22px;"><p><span style="font-family:'Microsoft YaHei', 'SF Pro Display', Roboto, Noto, Arial, 'PingFang SC', sans-serif;">mysql&gt;&nbsp;</span><span style="color:rgb(0,176,240);font-family:'Microsoft YaHei', 'SF Pro Display', Roboto, Noto, Arial, 'PingFang SC', sans-serif;">GRANT&nbsp;REPLICATION&nbsp;CLIENT,PROCESS&nbsp;ON&nbsp;*.*&nbsp;TO&nbsp;'mysql_monitor'@'localhost'&nbsp;identified&nbsp;by&nbsp;'Jvsa09OodhvS0VKQ';</span></p><p><span style="font-family:'Microsoft YaHei', 'SF Pro Display', Roboto, Noto, Arial, 'PingFang SC', sans-serif;">mysql&gt;&nbsp;<span style="font-family:'Microsoft YaHei', 'SF Pro Display', Roboto, Noto, Arial, 'PingFang SC', sans-serif;color:rgb(0,176,240);">FLUSH PRIVILEGES;</span></span></p></td> 
          </tr> 
         </tbody> 
        </table>
    4.  cd /data/monitor/mysqld\_exporter下，创建.my.cnf文件，vim .my.cnf
        
        \[client\]
        
        host=10.8.4.126
        
        port=3306
        
        user=mysql\_monitor
        
        password=Jvsa09OodhvS0VKQ
    5.  启动mysqld\_exporter  /data/monitor/mysqld\_exporter/bin/mysqld\_exporter \-config.my-cnf="/data/monitor/.my.cnf" &

B. 使用的是云商的mysql db（我们使用的是ucloud的udb，下面的都按这个来实现，都差不多）

1.  下载mysqld\_exporter到prometheus服务器上（（登陆到prometheus服务器，prometheus grafana alertmanager在同一台服务器上）下载地址： [https://pan.baidu.com/s/1MNPbhoZEvVV4lf1bVXWJ1g][https_pan.baidu.com_s_1pW7RptzXa3LqFlO5zxJXPw] ），并解压到/data/monitor/下
2.  如果没有安装go环境， yum install go -y
3.  用root用户连接当前mysql，授权监控用户
    
    <table> 
     <tbody style="border:0px;"> 
      <tr style="border-width:1px 0px 0px;border-top-style:solid;border-top-color:rgb(221,221,221);"> 
       <td style="color:rgb(79,79,79);line-height:22px;"><p><span style="font-family:'Microsoft YaHei', 'SF Pro Display', Roboto, Noto, Arial, 'PingFang SC', sans-serif;">mysql&gt;&nbsp;</span><span style="color:rgb(0,176,240);font-family:'Microsoft YaHei', 'SF Pro Display', Roboto, Noto, Arial, 'PingFang SC', sans-serif;">GRANT&nbsp;REPLICATION&nbsp;CLIENT,PROCESS&nbsp;ON&nbsp;*.*&nbsp;TO&nbsp;'mysql_monitor'@'%'&nbsp;identified&nbsp;by&nbsp;'Jvsa09OodhvS0VKQ';</span></p><p><span style="font-family:'Microsoft YaHei', 'SF Pro Display', Roboto, Noto, Arial, 'PingFang SC', sans-serif;">mysql&gt;&nbsp;<span style="color:rgb(0,176,240);">FLUSH PRIVILEGES;</span></span></p></td> 
      </tr> 
     </tbody> 
    </table>
4.  cd /data/monitor/mysqld\_exporter下，创建.my.cnf文件夹，然后在文件下创建每个db的连接配置文件。以下是一个的实例，其它的请参照这个来创建。
    
    cat /data/monitor/mysqld\_exporter/.my.cnf/.ba\_master\_10.8.4.126\_3306\_15049.cnf
    
    \[client\]
    
    host=10.8.4.126
    
    port=3306
    
    user=mysql\_monitor
    
    password=Jvsa09OodhvS0VKQ
5.  然后cd /data/monitor/mysqld\_exporter/scripts下，创建各个mysqld\_exporter的启动脚本，下面是一个mysql db 的mysqld\_exporter启动脚本，其它请参照这个来创建，注意监听的端口要不同和调用的.my.cnf文件要对应，
    
    cat /data/monitor/mysqld\_exporter/scripts/ba\_master\_10.8.4.126\_3306\_15049.sh
    
    nohup /data/monitor/mysqld\_exporter/bin/mysqld\_exporter -web.listen-address=':15049' -config.my-cnf=/data/monitor/mysqld\_exporter/.my.cnf/.ba\_master\_10.8.4.126\_3306\_15049.cnf -collect.info\_schema.tables=false >> /data/monitor/mysqld\_exporter/log/15049\_10.8.4.126\_3306.log 2>&1 &
6.  由于/data/monitor/mysqld\_exporter/scripts/下有很多个mysql db 的mysqld\_exporter启动脚本，所以我们cd /data/monitor/mysqld\_exporter下，然后 sh start.sh进行启动，然后检查各个端口是否已监听。

2. 配置prometheus

A. 将mysqld\_exporter的配置增加到prometheus.yml文件中，vim /data/monitor/prometheus/conf/prometheus.yml

global:

\# Server端抓取数据的时间间隔

scrape\_interval:     1m

\# 评估报警规则的时间间隔

evaluation\_interval: 1m

\# 数据抓取的超时时间

scrape\_timeout: 20s

\# 加全局标签

\#external\_labels:

\#  monitor: "hk"

\# 连接alertmanager

alerting:

alertmanagers:

- static\_configs:

- targets: \["localhost:9093"\]

\# 告警规则

rule\_files:

- /data/monitor/prometheus/conf/rule/\*.yml

\# A scrape configuration containing exactly one endpoint to scrape:

\# Here it's Prometheus itself.

scrape\_configs:

\# 监控prometheus本机

- job\_name: 'prometheus'

scrape\_interval: 15s

static\_configs:

- targets: \['10.8.53.218:9090'\]

\# 监控指定主机

- job\_name: 'node\_resources'

scrape\_interval: 1m

static\_configs:

file\_sd\_configs:

- files:

- /data/monitor/prometheus/conf/node\_conf/node\_host\_info.json

honor\_labels: true

\# mysql采集器

- job\_name: 'mysql\_global\_status'

scrape\_interval: 60s

static\_configs:

file\_sd\_configs:

- files:

- /data/monitor/prometheus/conf/node\_conf/node\_mysql\_info.json

B. 编写node\_mysql\_info.json，cat /data/monitor/prometheus/conf/node\_conf/node\_mysql\_info.json

"labels": \{

"desc": "slave\_customer\_10.8.31.101:3306",

"group": "ba",

"mysql\_addr": "10.8.31.101:3306",

"role": "slave\_customer"

\},

"targets": \[

"localhost:15050"

\},

"labels": \{

"desc": "slave\_bi\_10.8.150.188:3306",

"group": "ba",

"mysql\_addr": "10.8.150.188:3306",

"role": "slave\_bi"

\},

"targets": \[

"localhost:15221"

\},

"labels": \{

"desc": "slave\_10.8.139.209:3306",

"group": "ba",

"mysql\_addr": "10.8.139.209:3306",

"role": "slave"

\},

"targets": \[

"localhost:15052"

\},

"labels": \{

"desc": "slave\_catalog\_10.8.11.246:3306",

"group": "ba",

"mysql\_addr": "10.8.11.246:3306",

"role": "slave\_catalog"

\},

"targets": \[

"localhost:15053"

\},

"labels": \{

"desc": "master\_10.8.4.126:3306",

"group": "ba",

"mysql\_addr": "10.8.4.126:3306",

"role": "master"

\},

"targets": \[

"localhost:15049"

\},

"labels": \{

"desc": "slave\_dc\_10.8.17.124:3306",

"group": "ba",

"mysql\_addr": "10.8.17.124:3306",

"role": "slave\_dc"

\},

"targets": \[

"localhost:15051"

\},

"labels": \{

"desc": "master\_10.8.115.3:3306",

"group": "openapi",

"mysql\_addr": "10.8.115.3:3306",

"role": "master"

\},

"targets": \[

"localhost:15060"

B. 重启prometheus，cd /data/monitor/prometheus下，然后 sh reload.sh

注意：由于有很多指标无法抓取，我们用脚本再次获取，我只有ucloud的api对接抓取的python脚本，如果有需要可以加我qq: 761117826

3. 配置grafana

A. 下载mysql监控模板，下载地址： [https://pan.baidu.com/s/1xWWceAQ\_A4kKEn06dUlRBA][https_pan.baidu.com_s_1xWWceAQ_A4kKEn06dUlRBA]

B. 如何导入请参考配置主机监控的文章中的2.配置grafana中的h至l步骤( [https://blog.51cto.com/liuqs/2391282][prometheus _ grafana _ alertmanager] )

4. 配置alertmanager

A. 在prometheus配置规则，cat /data/monitor/prometheus/conf/rule/mysql.yml ,下面是文件内容，然后重启prometheus，cd /data/monitor/prometheus && sh reload.sh

groups:

- name: mysql\_alert

rules:

\#\#\# 慢查询 \#\#\#

\# 默认慢查询告警策略

- alert: mysql慢查询5分钟100条

expr: floor(delta(mysql\_global\_status\_slow\_queries\{mysql\_addr!~"10.8.6.44:3306|10.8.9.20:3306|10.8.12.212:3306"\}\[5m\])) >= 100

for: 3m

labels:

severity: warning

annotations:

description: "\[\{ \{ $labels.group \}\}\_\{ \{ $labels.role \}\}\],地址:\[\{ \{ $labels.mysql\_addr \}\}\],告警值为:\[\{ \{ $value \}\}条\],告警初始时长为3分钟."

\#\#\# qps \#\#\#

\# 默认qps告警策略

- alert: mysql\_qps大于8000

expr: floor(sum(irate(mysql\_global\_status\_commands\_total\{group!~"product|product\_backend"\}\[5m\])) by (group, role, mysql\_addr)) > 8000

for: 6m

labels:

severity: warning

annotations:

description: "\[\{ \{ $labels.group \}\}\_\{ \{ $labels.role \}\}\],地址:\[\{ \{ $labels.mysql\_addr \}\}\],告警值为:\[\{ \{ $value \}\}\],告警初始时长为6分钟."

\# 商品库等qps告警策略

- alert: mysql\_qps大于25000

expr: floor(sum(irate(mysql\_global\_status\_commands\_total\{group=~"product|product\_backend"\}\[5m\])) by (group, role, mysql\_addr)) > 25000

for: 3m

labels:

severity: warning

annotations:

description: "\[\{ \{ $labels.group \}\}\_\{ \{ $labels.role \}\}\],地址:\[\{ \{ $labels.mysql\_addr \}\}\],告警值为:\[\{ \{ $value \}\}\],告警初始时长为3分钟."

\#\#\# 内存 \#\#\#

\# 默认内存告警策略

- alert: mysql内存99%

expr: mysql\_mem\_used\_rate >= 99

for: 6m

labels:

severity: warning

annotations:

description: "\[\{ \{ $labels.group \}\}\_\{ \{ $labels.role \}\}\],地址:\[\{ \{ $labels.mysql\_addr \}\}\],告警值为:\[\{ \{ $value \}\}%\],告警初始时长为6分钟."

\#\#\# 磁盘 \#\#\#

\# 默认磁盘告警策略

- alert: mysql磁盘85%

expr: mysql\_disk\_used\_rate\{mysql\_addr!~"10.8.161.53:3306|10.8.115.31:3306"\} >= 85

for: 3m

labels:

severity: warning

annotations:

description: "\[\{ \{ $labels.group \}\}\_\{ \{ $labels.role \}\}\],地址:\[\{ \{ $labels.mysql\_addr \}\}\],告警值为:\[\{ \{ $value \}\}%\],告警初始时长为3分钟."

\# 磁盘95%告警策略

- alert: mysql磁盘95%

expr: mysql\_disk\_used\_rate\{mysql\_addr=~"10.8.161.53:3306|10.8.115.31:3306"\} >= 95

for: 3m

labels:

severity: warning

annotations:

description: "\[\{ \{ $labels.group \}\}\_\{ \{ $labels.role \}\}\],地址:\[\{ \{ $labels.mysql\_addr \}\}\],告警值为:\[\{ \{ $value \}\}%\],告警初始时长为3分钟."

\#\#\#\# IO上限告警 \#\#\#

\#\# SSD盘IO上限告警策略

\#    - alert: mysqlSSD盘IO上限预警

\#      expr: (floor(mysql\_ioops) >= mysql\_disk\_total\_size \* 50 \* 0.9) and (mysql\_ssd == 1) and on() hour() >= 0 < 16

\#      for: 6m

\#      labels:

\#        severity: warning

\#      annotations:

\#        description: "\[\{ \{ $labels.group \}\}\_\{ \{ $labels.role \}\}\],地址:\[\{ \{ $labels.mysql\_addr \}\}\],告警值为:\[\{ \{ $value \}\}\],告警初始时长为6分钟."

\#\# 普通盘IO上限告警策略

\#    - alert: mysql普通盘IO上限预警

\#      expr: (floor(mysql\_ioops) >= mysql\_disk\_total\_size \* 10 \* 0.9) and (mysql\_ssd == 0) and on() hour() >= 0 < 16

\#      for: 6m

\#      labels:

\#        severity: warning

\#      annotations:

\#        description: "\[\{ \{ $labels.group \}\}\_\{ \{ $labels.role \}\}\],地址:\[\{ \{ $labels.mysql\_addr \}\}\],告警值为:\[\{ \{ $value \}\}\],告警初始时长为6分钟."

\#\#\# 连接数 \#\#\#

\# 默认连接数告警策略

- alert: mysql连接数80%

expr: floor(mysql\_global\_status\_threads\_connected / mysql\_global\_variables\_max\_connections \* 100) >= 80

for: 3m

labels:

severity: warning

annotations:

description: "\[\{ \{ $labels.group \}\}\_\{ \{ $labels.role \}\}\],地址:\[\{ \{ $labels.mysql\_addr \}\}\],告警值为:\[\{ \{ $value \}\}%\],告警初始时长为3分钟."

\#\#\# 运行进程数 \#\#\#

\# 默认运行进程数告警策略

- alert: mysql运行进程数5分钟增长>150

expr: floor(delta(mysql\_global\_status\_threads\_running\{mysql\_addr!~"10.8.136.10:3306|10.10.129.116:3306|10.8.67.153:3306"\}\[5m\])) >= 150

for: 3m

labels:

severity: warning

annotations:

description: "\[\{ \{ $labels.group \}\}\_\{ \{ $labels.role \}\}\],地址:\[\{ \{ $labels.mysql\_addr \}\}\],告警值为:\[\{ \{ $value \}\}\],告警初始时长为3分钟."

\# 6分钟运行进程数告警策略

- alert: mysql运行进程数5分钟增长>150

expr: floor(delta(mysql\_global\_status\_threads\_runningi\{mysql\_addr=~"10.8.136.10:3306|10.10.129.116:3306|10.8.67.153:3306"\}\[5m\])) >= 150

for: 6m

labels:

severity: warning

annotations:

description: "\[\{ \{ $labels.group \}\}\_\{ \{ $labels.role \}\}\],地址:\[\{ \{ $labels.mysql\_addr \}\}\],告警值为:\[\{ \{ $value \}\}\],告警初始时长为6分钟."

\#\#\# 主从同步异常 \#\#\#

\# 默认主从同步告警策略

- alert: mysql主从同步异常

expr: (mysql\_slave\_status\_slave\_io\_running\{role!="master"\} == 0) or (mysql\_slave\_status\_slave\_sql\_running\{role!="master"\} == 0)

for: 1m

labels:

severity: warning

annotations:

description: "\[\{ \{ $labels.group \}\}\_\{ \{ $labels.role \}\}\],地址:\[\{ \{ $labels.mysql\_addr \}\}\],主从同步异常,告警初始时长为1分钟."

\#\#\# 主从同步延时 \#\#\#

\# 默认主从同步延时告警策略

- alert: mysql主从同步延时>30s

expr: floor(mysql\_slave\_status\_seconds\_behind\_master\{mysql\_addr!~"10.8.137.173:3306|10.8.11.17:3306|10.8.2.17:3306|10.10.29.6:3306|10.8.61.153:3306"\}) >= 30

for: 3m

labels:

severity: warning

annotations:

description: "\[\{ \{ $labels.group \}\}\_\{ \{ $labels.role \}\}\],地址:\[\{ \{ $labels.mysql\_addr \}\}\],告警值为:\[\{ \{ $value \}\}s\],告警初始时长为3分钟."

\# 主从同步延时较大告警策略

- alert: mysql主从同步延时>300s

expr: floor(mysql\_slave\_status\_seconds\_behind\_master\{mysql\_addr=~"10.8.137.173:3306|10.8.11.17:3306|10.10.29.6:3306|10.8.61.153:3306"\}) >= 300

for: 12m

labels:

severity: warning

annotations:

description: "\[\{ \{ $labels.group \}\}\_\{ \{ $labels.role \}\}\],地址:\[\{ \{ $labels.mysql\_addr \}\}\],告警值为:\[\{ \{ $value \}\}s\],告警初始时长为12分钟."

B. 配置alertmanager, cat /data/prometheus/alertmanager/conf/alertmanager.yml ，如果是相同的接收人，可以直接在原来的资源后面增加，如果是不同的接收人，就需要重新定义接收人模板，然后再定义资源规则并绑定到新的接收人模板

global:

resolve\_timeout: 2m

smtp\_auth\_password: q5AYahvxi3WLDap3 \#发送邮箱密码

smtp\_auth\_username: itliuqs@163.com \#发送邮箱

smtp\_from: itliuqs@163.com \#发送邮箱

smtp\_require\_tls: false

smtp\_smarthost: smtp.163.com:465 \#发送服务器

wechat\_api\_url: https://qyapi.weixin.qq.com/cgi-bin/ \#微信接口链接

inhibit\_rules:

\- equal:

- instance

source\_match:

alertname: "主机CPU90%"

target\_match:

alertname: "主机负载过高"

\- equal:

- instance

source\_match:

alertname: "mysql运行进程数5分钟增长数>150"

target\_match:

alertname: "mysql慢查询5分钟100条"

\- equal:

- instance

source\_match:

severity: error

target\_match:

severity: warning

\- equal:

- instance

source\_match:

severity: fatal

target\_match:

severity: error

\- equal:

- service\_name

source\_match:

severity: error

target\_match:

severity: warning

receivers:

\- email\_configs: \#定义test发送人模块

- html: '\{ \{  template "email.default.html" . \}\}' \#调用的模板

send\_resolved: true

to: liuqs@126.com \#将报警信息发给些邮箱，多人用|

name: test \#发送人模板名

wechat\_configs: \#微信接收这些信息请看最下面的企业微信介绍

- agent\_id: 1000002 \#应用id

api\_secret: hnyU1LTGnJUiBaCp47l3WVQLTEFF5RXyfNO751xlaHa \#应用认证

corp\_id: wwd397231fa801beaa \#企业微信ID

send\_resolved: true

to\_user: LiuQingShan|liuqs \#发送给企业微信通讯人的Id 多个人就用|分开

\- email\_configs: \#定义默认的发送人

- html: '\{ \{  template "email.default.html" . \}\}'

send\_resolved: true

to: liuqs@126.com

name: default\_group

wechat\_configs:

- agent\_id: 1000002

api\_secret: hnyU1LTGnJUiBaCp47l3WVQLTEFF5RXyfNO751xlaHa

corp\_id: wwd397231fa801beaa

send\_resolved: true

to\_user: LiuQingShan

route: \#定义资源报警规则

group\_by:

- monitor

group\_interval: 2m

group\_wait: 30s

receiver: default\_group

repeat\_interval: 6h

routes:

- continue: true

match\_re:

receiver: test \#使用test发送人模板

templates:

\- /data/monitor/alertmanager/template/\*.tmpl \#调用报警内容模板的路径

转载于:https://blog.51cto.com/liuqs/2391489

[https_blog.51cto.com_liuqs_2027365]: https://blog.51cto.com/liuqs/2027365
[prometheus _ grafana _ alertmanager]: https://blog.51cto.com/liuqs/2391282
[prometheus _ grafana _ alertmanager _Mysql]: https://blog.51cto.com/liuqs/2391489
[prometheus _ grafana _ alertmanager _Redis]: https://blog.51cto.com/liuqs/2393926
[prometheus _ grafana _ alertmanager _Kafka]: https://blog.51cto.com/liuqs/2395960
[prometheus _ grafana _ alertmanager _ES]: https://blog.51cto.com/liuqs/2398195
[https_pan.baidu.com_s_1pW7RptzXa3LqFlO5zxJXPw]: https://pan.baidu.com/s/1MNPbhoZEvVV4lf1bVXWJ1g
[https_pan.baidu.com_s_1xWWceAQ_A4kKEn06dUlRBA]: https://pan.baidu.com/s/1xWWceAQ_A4kKEn06dUlRBA