Prometheus监控mysql

DevOps
Prometheus评论309字数 7695阅读25分39秒阅读模式

简述

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

Prometheus监控mysql

创建用户并授权

root@bjzhanjiserver:/usr/local/src# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 534
Server version: 5.6.51 MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE USER 'exporter'@'%' IDENTIFIED BY 'Prometheus';
Query OK, 0 rows affected (0.02 sec)

mysql> GRANT PROCESS, REPLICATION CLIENT,SELECT ON *.* TO 'exporter'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

mysql> exit
Bye

安装配置mysql_exporter

[root@devops ~]# https://github.com/prometheus/mysqld_exporter/releases/download/v0.15.0/mysqld_exporter-0.15.0.linux-amd64.tar.gz
[root@devops ~]# tar xvf mysqld_exporter-0.15.0.linux-amd64.tar.gz -C /usr/local/
[root@devops ~]# mv /usr/local/mysqld_exporter-0.15.0.linux-amd64 /usr/local/mysqld_exporter
[root@devops ~]# cd /usr/local/mysqld_exporter
[root@devops ~]# 
cat > .my.cnf <<EOF
[client]
port=3306
user=exporter
password=Prometheus
host=192.168.1.230
EOF
[root@devops ~]# 
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@devops mysqld_exporter]# systemctl enable mysqld_exporter
Created symlink from /etc/systemd/system/multi-user.target.wants/mysqld_exporter.service to /usr/lib/systemd/system/mysqld_exporter.service.
[root@devops mysqld_exporter]# systemctl status mysqld_exporter
● mysqld_exporter.service - Prometheus
   Loaded: loaded (/usr/lib/systemd/system/mysqld_exporter.service; enabled; vendor preset: disabled)
   Active: inactive (dead)
[root@devops mysqld_exporter]# systemctl start mysqld_exporter
[root@devops mysqld_exporter]# systemctl status mysqld_exporter
● mysqld_exporter.service - Prometheus
   Loaded: loaded (/usr/lib/systemd/system/mysqld_exporter.service; enabled; vendor preset: disabled)
   Active: active (running) since 六 2024-06-15 18:41:57 CST; 3s ago
 Main PID: 59359 (mysqld_exporter)
    Tasks: 4
   Memory: 1.3M
   CGroup: /system.slice/mysqld_exporter.service
           └─59359 /usr/local/mysqld_exporter/mysqld_exporter --config.my-cnf=/usr/local/mysqld_exporter/.my.cnf

6月 15 18:41:57 devops mysqld_exporter[59359]: ts=2024-06-15T10:41:57.462Z caller=mysqld_exporter.go:220 level=info msg="Starting mysqld_exporter" version="(v...267a6b6b)"
6月 15 18:41:57 devops mysqld_exporter[59359]: ts=2024-06-15T10:41:57.463Z caller=mysqld_exporter.go:221 level=info msg="Build context" build_context="(go=go1...gs=netgo)"
6月 15 18:41:57 devops mysqld_exporter[59359]: ts=2024-06-15T10:41:57.464Z caller=mysqld_exporter.go:233 level=info msg="Scraper enabled" scraper=global_status
6月 15 18:41:57 devops mysqld_exporter[59359]: ts=2024-06-15T10:41:57.464Z caller=mysqld_exporter.go:233 level=info msg="Scraper enabled" scraper=global_variables
6月 15 18:41:57 devops mysqld_exporter[59359]: ts=2024-06-15T10:41:57.464Z caller=mysqld_exporter.go:233 level=info msg="Scraper enabled" scraper=slave_status
6月 15 18:41:57 devops mysqld_exporter[59359]: ts=2024-06-15T10:41:57.464Z caller=mysqld_exporter.go:233 level=info msg="Scraper enabled" scraper=info_schema.innodb_cmp
6月 15 18:41:57 devops mysqld_exporter[59359]: ts=2024-06-15T10:41:57.464Z caller=mysqld_exporter.go:233 level=info msg="Scraper enabled" scraper=info_schema.innodb_cmpmem
6月 15 18:41:57 devops mysqld_exporter[59359]: ts=2024-06-15T10:41:57.464Z caller=mysqld_exporter.go:233 level=info msg="Scraper enabled" scraper=info_schema....ponse_time
6月 15 18:41:57 devops mysqld_exporter[59359]: ts=2024-06-15T10:41:57.465Z caller=tls_config.go:274 level=info msg="Listening on" address=[::]:9104
6月 15 18:41:57 devops mysqld_exporter[59359]: ts=2024-06-15T10:41:57.465Z caller=tls_config.go:277 level=info msg="TLS is disabled." http2=false address=[::]:9104
Hint: Some lines were ellipsized, use -l to show in full.

配置prometheus

  - job_name: 'mysqld_exporter'
    static_configs:
    - targets: ['192.168.1.101:9104']
      labels:
        app: mysqld_exporter
        node: node1
        role: mysqld_exporter

grafana集成

  • 11323
  • 7362
  • 17320

告警规则

[root@devops prometheus]# vim first_rules.yml
- name: MySQL-Alert
  rules:
  - alert: MySQL_CPU使用率过高
    expr: mysql_cpu_util * on (iid) group_right mysql_up > 70
    for: 2m
    labels:
      severity: critical
    annotations:
      description: "{{ $labels.group }}_{{ $labels.name }}：MySQL当前CPU使用率:{{ $value }}% \n> {{ $labels.instance }}\n> {{ $labels.iid }}"

  - alert: MySQL_内存使用率过高
    expr: mysql_mem_util * on (iid) group_right mysql_up > 85
    for: 2m
    labels:
      severity: critical
    annotations:
      description: "{{ $labels.group }}_{{ $labels.name }}：MySQL当前内存使用率:{{ $value }}% \n> {{ $labels.instance }}\n> {{ $labels.iid }}"

  - alert: MySQL_磁盘使用率过高
    expr: mysql_disk_util * on (iid) group_right mysql_up > 90
    for: 2m
    labels:
      severity: critical
    annotations:
      description: "{{ $labels.group }}_{{ $labels.name }}：MySQL当前磁盘使用率:{{ $value }}% \n> {{ $labels.instance }}\n> {{ $labels.iid }}"

  - alert: MySQL_IO使用率过高
    expr: mysql_io_util * on (iid) group_right mysql_up > 90
    for: 2m
    labels:
      severity: critical
    annotations:
      description: "{{ $labels.group }}_{{ $labels.name }}：MySQL当前IO使用率:{{ $value }}% \n> {{ $labels.instance }}\n> {{ $labels.iid }}"

  - alert: MySQL_is_down
    expr: mysql_up == 0
    for: 3m
    labels:
      severity: critical
    annotations:
      description: "{{ $labels.group }}_{{ $labels.name }}：MySQL database is down. \n> {{ $labels.instance }}\n> {{ $labels.iid }}"

  - alert: MySQL_慢查询过多
    expr: delta(mysql_global_status_slow_queries[1m]) > 60
    for: 1m
    labels:
      severity: critical
    annotations:
      description: "{{ $labels.group }}_{{ $labels.name }}：每分钟慢查询:{{ $value }} \n> {{ $labels.instance }}\n> {{ $labels.iid }}"

  - alert: MySQL_当前活跃的连接数过多
    expr: mysql_global_status_threads_running > 100
    for: 1m
    labels:
      severity: critical
    annotations:
      description: "{{ $labels.group }}_{{ $labels.name }}：当前活跃的连接数:{{ $value }} \n> {{ $labels.instance }}\n> {{ $labels.iid }}"

  - alert: MySQL_当前updating状态的线程过多
    expr: mysql_info_schema_processlist_threads{state=~"updating"} > 100
    for: 1m
    labels:
      severity: critical
    annotations:
      description: "{{ $labels.group }}_{{ $labels.name }}：当前updating状态的线程:{{ $value }} \n> {{ $labels.instance }}\n> {{ $labels.iid }}"

  - alert: MySQL_High_QPS
    expr: irate(mysql_global_status_questions[3m]) > 30000
    for: 2m
    labels:
      severity: warning
    annotations:
      description: "{{ $labels.group }}_{{ $labels.name }}：Mysql QPS:{{ $value | humanize }} \n> {{ $labels.instance }}\n> {{ $labels.iid }}"

  - alert: MySQL_Too_Many_Connections
    expr: irate(mysql_global_status_threads_connected[3m]) > 1000
    for: 2m
    labels:
      severity: warning
    annotations:
      description: "{{ $labels.group }}_{{ $labels.name }}：Mysql Connections:{{ $value | humanize }} \n> {{ $labels.instance }}\n> {{ $labels.iid }}"

  - alert: MySQL_主从IO线程运行状态异常
    expr: mysql_slave_status_master_server_id > 0 and ON (instance) mysql_slave_status_slave_io_running == 0
    for: 1m
    labels:
      severity: critical
    annotations:
      description: "{{ $labels.group }}_{{ $labels.name }}：MySQL Slave IO thread not running \n> {{ $labels.instance }}\n> {{ $labels.iid }}"
  
  - alert: MySQL_主从SQL线程运行状态异常
    expr: mysql_slave_status_master_server_id > 0 and ON (instance) mysql_slave_status_slave_sql_running == 0
    for: 1m
    labels:
      severity: critical
    annotations:
      description: "{{ $labels.group }}_{{ $labels.name }}：MySQL Slave SQL thread not running \n> {{ $labels.instance }}\n> {{ $labels.iid }}"

  - alert: MySQL_主从复制延迟过高
    expr: mysql_slave_status_seconds_behind_master > 3
    for: 1m
    labels:
      severity: critical
    annotations:
      description: "{{ $labels.group }}_{{ $labels.name }}：主从复制延迟当前:{{ $value | humanize }}s \n> {{ $labels.instance }}\n> {{ $labels.iid }}"

  - alert: MySQL_is_Restart
    expr: mysql_global_status_uptime <600
    for: 2m
    labels:
      severity: critical
    annotations:
      description: "{{ $labels.group }}_{{ $labels.name }}：MySQL database is Restart. \n> {{ $labels.instance }}\n> {{ $labels.iid }}"

[root@devops prometheus]# ./promtool check config prometheus.yml
Checking prometheus.yml
  SUCCESS: 1 rule files found
 SUCCESS: prometheus.yml is valid prometheus config file syntax

Checking first_rules.yml
  SUCCESS: 42 rules found

[root@devops prometheus]# systemctl restart prometheus

继续阅读
历史上的今天
6 月
15
DevOps
  • 本文由 发表于 2024年6月15日 18:32:57
  • 除非特殊声明，本站文章均为原创，转载请务必保留本文链接
  • mysql_exporter
Prometheus监控kafka Prometheus

Prometheus监控kafka

前言 Kafka现有开源的集群监控方案：kafka-manager、kafka-monitor、kafka-eagle、KafkaOffsetMonitor，但有所限制监控指标被固化，不易扩展、预警...
355评论
Prometheus Prometheus

Prometheus

版本 CentOS Linux release 7.9 Prometheus：prometheus-2.52.0.linux-amd64 Alertmanager：alertma...
421评论
prometheus告警规则 Prometheus

prometheus告警规则

基础监控 主机内存不足 节点内存已满（剩余 < 10%） groups: - name: Node memory is filling up (< 10% left) rul...
1,981评论
评论  0  访客  0

发表评论