Prometheus监控Mysql数据库+mysql告警规则_prometheus mysql慢查询sql告警

Prometheus监控Mysql数据库

Prometheus部署请见上篇文章：

https://blog.csdn.net/dragonQuncle/article/details/133983718?spm=1001.2014.3001.5502

安装配置mysqld-exporter：

下载mysqld-exporter：
https://github.com/prometheus/mysqld_exporter/releases这里我下载的是https://github.com/prometheus/mysqld_exporter/releases/download/v0.14.0/mysqld_exporter-0.14.0.linux-amd64.tar.gz

解压

tar -xzf mysqld_exporter-0.14.0.linux-amd64.tar.gz   -C  /opt/
cd /opt/
mv mysqld_exporter-0.14.0.linux-amd64/    mysqld_exporter
cd mysqld_exporter/
1
2
3
4

配置mysql-exporter

1. 在mysqld-exporter安装路径下，创建.my.cnf文件：

   [root@localhost mysqld_exporter]# vim  my.cnf
   
   #创建该文件主要用于免密登录
   [client]
   host = mysqlip
   port = mysqlport
   user=mysql_exporter
   password=mysql!@#abC
   1
   2
   3
   4
   5
   6
   7
   8

2. 登录mysql数据库，并创建mysql_exporter用户并授权:

   #创建用户
   create user 'mysql_exporter'@'%' identified by 'mysql!@#abC' with max_user_connections 3;
   #授权
   grant process, replication client, select on *.* to 'mysql_exporter'@'%';
   FLUSH PRIVILEGES;
   exit
   1
   2
   3
   4
   5
   6

设置systemd启动管理

vim /etc/systemd/system/mysqld_exporter.service注意路径

[Unit]
Description=mysql Monitoring SystemDocumentation=mysql Monitoring System
Documentation=mysql Monitoring System

[Service]
ExecStart=/opt/mysqld_exporter/mysqld_exporter \
--collect.info_schema.processlist \
--collect.info_schema.innodb_tablespaces \
--collect.info_schema.innodb_metrics \
--collect.perf_schema.tableiowaits \
--collect.perf_schema.indexiowaits \
--collect.perf_schema.tablelocks \
--collect.engine_innodb_status \
--collect.perf_schema.file_events \
--collect.binlog_size \
--collect.info_schema.clientstats \
--collect.perf_schema.eventswaits \
--config.my-cnf=/opt/mysqld_exporter/my.cnf
[Install]
WantedBy=multi-user.target
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

启动mysqld_exporter：

systemctl daemon-reload
systemctl enable mysqld_exporter.service
systemctl start mysqld_exporter.service
1
2
3

配置Prometheus文件：

切换到prometheus server端，修改prometheus.yml文件：

vim  prometheus.yml

- job_name: "mysql"
    static_configs:
    - targets: ['192.168.52.130:9104']
1
2
3
4
5

热加载prometheus:

curl -X POST 192.168.120.66:9090/-/reload
1

查看Prometheus是否监控到mysqld_exporter

Grafana导入mysql_exporter模板：

主从监控模板ID：7371
MySQL状态监控模板ID：7362
缓冲池状态模板ID：7365

撒花

配置mysql的告警规则

groups:
- name: MySQL-rules
  rules:
#mysql状态检测
  - alert: MySQL Status
    expr: mysql_up == 0
    for: 10s
    labels:
      severity: warning
    annotations:
      summary: "{{ $labels.instance }} Mysql服务 !!!"
      description: "{{ $labels.instance }} Mysql服务不可用  请检查!"

#mysql主从IO线程停止时触发告警
  - alert: MySQL Slave IO Thread Status
    expr: mysql_slave_status_slave_io_running == 0
    for: 5s
    labels:
      severity: warning
    annotations:
      summary: "{{ $labels.instance }} Mysql从节点IO线程"
      description: "Mysql主从IO线程故障，请检测!"

#mysql主从sql线程停止时触发告警
  - alert: MySQL Slave SQL Thread Status 
    expr: mysql_slave_status_slave_sql_running == 0
    for: 5s 
    labels:
      severity: error
    annotations: 
      summary: "{{$labels.instance}}: MySQL Slave SQL Thread has stop !!!"
      description: "检测MySQL主从SQL线程运行状态"
      
#mysql主从延时状态告警
  - alert: MySQL Slave Delay Status 
    expr: mysql_slave_status_sql_delay == 30
    for: 5s 
    labels:
      severity: warning
    annotations: 
      summary: "{{$labels.instance}}: MySQL 主从延迟超过 30s !!!"
      description: "检测MySQL主从延时状态"
      
#mysql连接数告警
  - alert: Mysql_Too_Many_Connections
    expr: rate(mysql_global_status_threads_connected[5m]) > 200
    for: 2m
    labels:
      severity: warning
    annotations:
      summary: "{{$labels.instance}}: 连接数过多"
      description: "{{$labels.instance}}: 连接数过多，请处理 ,(current value is: {{ $value }})！"  
 
 #mysql慢查询有点多告警
  - alert: Mysql_Too_Many_slow_queries
    expr: rate(mysql_global_status_slow_queries[5m]) > 3
    for: 2m
    labels:
      severity: warning
    annotations:
      summary: "{{$labels.instance}}: 慢查询有点多，请检查处理！"
      description: "{{$labels.instance}}: Mysql slow_queries is more than 3 per second ,(current value is: {{ $value }})"
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62

重启 alertmanager和 prometheus

 systemctl restart alertmanager prometheus   
1

再撒花！！！