prometheus☞04mysql监控

前言

Awesome Prometheus alerts | Collection of alerting rules (grep.to)

prometheus/mysqld_exporter: Exporter for MySQL server metrics (github.com)

prom/mysqld-exporter (docker.com)

安装

添加mysql账户

CREATE USER 'exporter'@'<局域网授信IP>' IDENTIFIED BY 'exporter@123';
GRANT PROCESS, REPLICATION CLIENT ON *.* TO 'exporter'@'<局域网授信IP>';
GRANT SELECT ON performance_schema.* TO 'exporter'@'<局域网授信IP>';

docker run -d --net promsnet --name mysqld-exporter -p 9104:9104 --link=my_mysql_container:<被监控的mysql容器名>  \
  -e DATA_SOURCE_NAME="exporter:exporter@123@(<被监控的mysql容器名 or 被监控的mysql实例地址>:3306)/" prom/mysqld-exporter

–link 可选，关联被监控的容器数据库

prometheus 主配置

scrape_configs:
  - job_name: 'mysql-001'
    static_configs:
      - targets: ['mysqld-exporter:9104'] # mysqld-exporter 采集器地址
        labels:
          instance: <mysql_server_path>:3306 # 变更采集后的标签instance为mysql实例地址

prometheus 告警配置

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: 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: 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 }}"

    - 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 }}"

grafana

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