八、Prometheus监控实战之mysql_exporter使用

Prometheus监控实战之mysql_exporter使用

概述： mysql_exporter是用来收集MysQL或者Mariadb数据库相关指标的，mysql_exporter需要连接到数据库并有相关权限。

笔记配套视频效果更佳哦，视频地址：https://edu.51cto.com/lecturer/14390454.html

一、安装企业级数据库MySQL

一、安装MysQL或者Mariadb环境准备
安装MysQL-8.0社区版
shell# wget https://repo.mysql.com//mysql80-community-release-el7-3.noarch.rpm
shell# yum -y localinstall mysql80-community-release-el7-3.noarch.rpm
shell# yum search mysql --showduplicates
shell# yum -y install mysql-community-server-8.0.21-*
shell# systemctl enable mysqld
shell# systemctl start mysqld
说明：初始密码在/var/log/mysqld.log 中。
[root@localhost ~]# cat /var/log/mysqld.log |grep password
2022-01-13T16:58:25.992752Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: Hc;fB2!FVFBb

使用mysqladmin修改root的初始密码
shell# mysqladmin -u root password Mysql@123 -p
或者用初始密码登录后用alter user修改
mysql> alter user root@localhost identified by 'Mysql@123';

二、创建用户并授权

# 一、创建用于监视数据库的用户exporter，需要先增加授权（在数据库所在的服务器上授权prometheus）
mysql -u root -p
mysql> set global validate_password.policy=LOW; 
# 降低MySQL8 密码规则策略，或者按规则设置密码,如果是mysql8.0则需要进行设置，mariadb则不需要配置
mysql> CREATE USER 'exporter'@'localhost' IDENTIFIED BY 'Prometheus';
mysql> GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'exporter'@'localhost';
mysql> flush privileges;

# 说明：使用max_user_connections参数来限制exporter用户最大连接数，避免监控引起数据库过载，需要注意的是该参数并不是MySQL/Mariadb每个版本都支持；另若不给REPLICATION CLIENT权限，可能会报如下错误

mysql_exporter支持MySQL和MariaDB，版本：5.5以及更高版本。
如果MySQL/MariaDB版本低于5.6，可能有部分收集方法不支持。详细请阅读github文档。

三、安装配置mysql_exporter

# # 三、下载mysql_exporter（在被监控数据库服务器上面）
[root@localhost ~]# wget https://github.com/prometheus/mysqld_exporter/releases/download/v0.13.0/mysqld_exporter-0.13.0.linux-amd64.tar.gz
[root@localhost ~]# tar xvf mysqld_exporter-0.13.0.linux-amd64.tar.gz -C /usr/local/
mysqld_exporter-0.13.0.linux-amd64/
mysqld_exporter-0.13.0.linux-amd64/LICENSE
mysqld_exporter-0.13.0.linux-amd64/NOTICE
mysqld_exporter-0.13.0.linux-amd64/mysqld_exporter
[root@localhost ~]# cd /usr/local/
[root@localhost local]# ln -s mysqld_exporter-0.13.0.linux-amd64/ mysqld_exporter
[root@localhost local]# cd /usr/local/mysqld_exporter

[root@localhost mysqld_exporter]# cat > .my.cnf <<EOF
[client]
user=exporter
password=Prometheus
EOF
#原始启动
[root@localhost mysqld_exporter]# ./mysqld_exporter --config.my-cnf=.my.cnf 
#systemd启动
使用systemd方式启动
[root@localhost ~]# cat >/usr/lib/systemd/system/mysqld_exporter.service <<EOF
[Unit]
Description=Prometheus
[Service]
ExecStart=/usr/local/mysqld_exporter/mysqld_exporter --config.my-cnf=/usr/local/mysqld_exporter/.my.cnf
Restart=on-failure
[Install]
WantedBy=multi-user.target
EOF
[root@localhost ~]# systemctl enable mysqld_exporter
[root@localhost ~]# systemctl restart mysqld_exporter

#默认端口9104

四、在prometheus.yaml中添加mysqld_exporter的配置

# 三、在prometheus.yaml最后面添加mysqld_exporter的配置，注意节点名称根据自己实际情况而定
[root@prometheus ~]# cd /usr/local/prometheus/
[root@prometheus prometheus]# vi prometheus.yml 

......
  - job_name: 'mysqld_exporter'
    static_configs:
    - targets: ['192.168.1.101:9104']
      labels:
        app: mysqld_exporter
        node: node1
        role: mysqld_exporter
#重新加载prometheus配置
[root@prometheus prometheus]# curl -X POST http://192.168.1.120:9090/-/reload
[root@prometheus prometheus]# systemctl restart prometheus

五、配置grafana集成大屏展示：7362

六、编写告警规则

1、alert创建告警规则

2、创建告警模板

3、prometheus创建告警规则

笔记配套视频效果更佳哦，视频地址：https://edu.51cto.com/lecturer/14390454.html

# 六、编写告警规则
[root@prometheus prometheus]# mkdir rules
[root@prometheus prometheus]# cd rules
#将之前创建过的规则也写入进去
[root@prometheus prometheus]# mv rule.yml rules
[root@prometheus rules]# vim /usr/local/prometheus/rules/mysql_rules.yml 
[root@prometheus rules]# cat mysql_rules.yml 
groups:
 - name: mysql_rules
   rules:
   - record: mysql:status
     expr: mysql_up{instance=~".*9104"}

   - record: mysql:uptime
     expr: mysql_global_status_uptime{job="mysqld_exporter"}

   - record: mysql:mysql_threads_connected
     expr: mysql_global_status_threads_connected{job="mysqld_exporter"}

   - record: mysql:mysql_threads_running
     expr: mysql_global_status_threads_running{job="mysqld_exporter"}

   - record: mysql:mysql_aborted_connects
     expr: increase(mysql_global_status_aborted_connects{job="mysqld_exporter"}[2m])

   - record: mysql:mysql_slow_queries
     expr: increase(mysql_global_status_slow_queries{job="mysqld_exporter"}[2m])

   - record: mysql:mysql_table_locks
     expr: increase(mysql_global_status_table_locks_waited{job="mysqld_exporter"}[2m])

   - record: mysql:mysql_qps
     expr: rate(mysql_global_status_queries{job="mysqld_exporter"}[2m])

#将告警规则写入prometheus中
[root@prometheus prometheus]# vi prometheus.yml 
.........
rule_files:
  - './rules/rule.yml'
  - './rules/mysql_rules.yml'
  
#热加载prometheus
[root@prometheus prometheus]# curl -X POST http://192.168.1.120:9090/-/reload

#编写告警文件
[root@prometheus rules]# vim /usr/local/prometheus/rules/mysql_alerts.yml 
[root@prometheus rules]# cat /usr/local/prometheus/rules/mysql_alerts.yml 
[root@prometheus rules]# cat mysql_alerts.yml 
groups:
- name: mysql_rules
  rules:
  - alert: MysqlDown
    expr: mysql_up == 0
    for: 0m
    labels:
      severity: critical
    annotations:
      summary: MySQL down (instance {{ $labels.instance }})
      description: "MySQL instance is down on {{ $labels.instance }}\n  VALUE = {{ $value }}\n  LABELS = {{ $labels }}"

  - alert: MysqlTooManyConnections(>80%)
    expr: avg by (instance) (rate(mysql_global_status_threads_connected[1m])) / avg by (instance) (mysql_global_variables_max_connections) * 100 > 80
    for: 2m
    labels:
      severity: warning
    annotations:
      summary: MySQL too many connections (> 80%) (instance {{ $labels.instance }})
      description: "More than 80% of MySQL connections are in use on {{ $labels.instance }}\n  VALUE = {{ $value }}\n  LABELS = {{ $labels }}"

  - alert: MysqlHighThreadsRunning
    expr: avg by (instance) (rate(mysql_global_status_threads_running[1m])) / avg by (instance) (mysql_global_variables_max_connections) * 100 > 60
    for: 2m
    labels:
      severity: warning
    annotations:
      summary: MySQL high threads running (instance {{ $labels.instance }})
      description: "More than 60% of MySQL connections are in running state on {{ $labels.instance }}\n  VALUE = {{ $value }}\n  LABELS = {{ $labels }}"

  - alert: MysqlSlaveIoThreadNotRunning
    expr: mysql_slave_status_master_server_id > 0 and ON (instance) mysql_slave_status_slave_io_running == 0
    for: 0m
    labels:
      severity: critical
    annotations:
      summary: MySQL Slave IO thread not running (instance {{ $labels.instance }})
      description: "MySQL Slave IO thread not running on {{ $labels.instance }}\n  VALUE = {{ $value }}\n  LABELS = {{ $labels }}"

  - alert: MysqlSlaveSqlThreadNotRunning
    expr: mysql_slave_status_master_server_id > 0 and ON (instance) mysql_slave_status_slave_sql_running == 0
    for: 0m
    labels:
      severity: critical
    annotations:
      summary: MySQL Slave SQL thread not running (instance {{ $labels.instance }})
      description: "MySQL Slave SQL thread not running on {{ $labels.instance }}\n  VALUE = {{ $value }}\n  LABELS = {{ $labels }}"

  - alert: MysqlSlaveReplicationLag
    expr: mysql_slave_status_master_server_id > 0 and ON (instance) (mysql_slave_status_seconds_behind_master - mysql_slave_status_sql_delay) > 30
    for: 1m
    labels:
      severity: critical
    annotations:
      summary: MySQL Slave replication lag (instance {{ $labels.instance }})
      description: "MySQL replication lag on {{ $labels.instance }}\n  VALUE = {{ $value }}\n  LABELS = {{ $labels }}"

  - alert: MysqlSlowQueries
    expr: increase(mysql_global_status_slow_queries[1m]) > 0
    for: 2m
    labels:
      severity: warning
    annotations:
      summary: MySQL slow queries (instance {{ $labels.instance }})
      description: "MySQL server mysql has some new slow query.\n  VALUE = {{ $value }}\n  LABELS = {{ $labels }}"

  - alert: MysqlInnodbLogWaits
    expr: rate(mysql_global_status_innodb_log_waits[15m]) > 10
    for: 0m
    labels:
      severity: warning
    annotations:
      summary: MySQL InnoDB log waits (instance {{ $labels.instance }})
      description: "MySQL innodb log writes stalling\n  VALUE = {{ $value }}\n  LABELS = {{ $labels }}"

  - alert: MysqlRestarted
    expr: mysql_global_status_uptime < 60
    for: 0m
    labels:
      severity: info
    annotations:
      summary: MySQL restarted (instance {{ $labels.instance }})
      description: "MySQL has just been restarted, less than one minute ago on {{ $labels.instance }}.\n  VALUE = {{ $value }}\n  LABELS = {{ $labels }}"

七、检查与告警模拟

#检查告警规则
[root@prometheus prometheus]# vi prometheus.yml 
..........
rule_files:
  - './rules/rule.yml'
  - './rules/mysql_rules.yml'
  - './rules/mysql_alerts.yml'


[root@prometheus prometheus]# ./promtool check config prometheus.yml
Checking prometheus.yml
  SUCCESS: 3 rule files found

Checking rules/rule.yml
  SUCCESS: 1 rules found

Checking rules/mysql_rules.yml
  SUCCESS: 8 rules found

Checking rules/mysql_alerts.yml
  SUCCESS: 9 rules found

# 重载服务
[root@prometheus-121 prometheus]# curl -X POST http://192.168.1.120:9090/-/reload
#模拟故障（停掉数据库）

• 登录界面查看出现告警

笔记配套视频效果更佳哦，视频地址：https://edu.51cto.com/lecturer/14390454.html

• 查看状态

• dashboard

https://grafana.com/grafana/dashboards/7362


笔记配套视频效果更佳哦，视频地址：https://edu.51cto.com/lecturer/14390454.html