【原创】企业级监控prometheus监控你的MYSQL（二）

MySQL是一种开源的关系型数据库管理系统，它广泛用于Web应用程序的后端存储数据和管理数据。

客户端

创建MYSQL监控用户

CREATE USER 'exporter'@'%' IDENTIFIED BY '密码';
#如果用户已存在则修改密码
alter user 'exporter'@'%' identified by '密码';

GRANT PROCESS, REPLICATION CLIENT ON *.* TO 'exporter'@'%';
GRANT SELECT ON performance_schema.* TO 'exporter'@'%';

FLUSH PRIVILEGES;

docker-compose

  mysqld-exporter:
    image: prom/mysqld-exporter
    container_name: mysqld-exporter
    restart: always
    ports:
      - 9104:9104
    links:
      - mysql
    volumes:
      - /data/mysqld-exporter/config.cnf:/etc/config.cnf
    command: --config.my-cnf=/etc/config.cnf

mysqld-exporter配置文件

文件路径：/data/mysqld-exporter/config.cnf

[client]
user=exporter
password=密码
host=mysql
port=3306

启动容器

docker-compse up -d

查看日志

docker logs mysqld-exporter 
#打印如下内容说明启动成功
ts=2023-10-25T13:11:07.396Z caller=tls_config.go:277 level=info msg="TLS is disabled." http2=false address=[::]:9104

服务端

修改配置文件prometheus.yml，新增内容

  - job_name: 'mysql'
    static_configs:
    - targets: ['IP:9104']
    relabel_configs:
    - source_labels: [__address__]
      regex: '(.+):.*'
      target_label: instance
      replacement: '$1:9100'

这里将获得的实例替换为NODE的实例，否则图像展示会缺失。 添加告警规则 文件路径：/data/prometheus/alert/alert-mysql-rules.yml

groups:
  - name: mysql-alert
    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: max_over_time(mysql_global_status_threads_connected[1m]) / 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: max_over_time(mysql_global_status_threads_running[1m]) / 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_slave_io_running and ON (instance) mysql_slave_status_master_server_id > 0 ) == 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_slave_sql_running and ON (instance) mysql_slave_status_master_server_id > 0) == 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_seconds_behind_master - mysql_slave_status_sql_delay) and ON (instance) mysql_slave_status_master_server_id > 0 ) > 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 }}"

重启prometheus容器

grafana设置

添加模版编号：7362

参考文档

https://samber.github.io/awesome-prometheus-alerts/rules#mysql https://grafana.com/grafana/dashboards/7362-mysql-overview/ https://github.com/prometheus/mysqld_exporter