Prometheus监控MySQL

安装mysqld_exporter

下载地址：https://github.com/prometheus/mysqld_exporter/

# 解压
tar zxvf mysqld_exporter-0.12.1.linux-amd64.tar.gz
cd mysqld_exporter-0.12.1.linux-amd64
cp mysqld_exporter /usr/local/bin

为exporter创建MySQL账号并授权

# 登录
mysql -uroot -p
# 创建账号
mysql> CREATE USER 'exporter'@'localhost' IDENTIFIED BY '123321';
# 授权
mysql> GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'exporter'@'localhost';

为exporter账号配置免密连接

vim /usr/local/mysqld_exporter-0.12.1.linux-amd64/.my.cnf

[client]
user=exporter
password=123321

启动mysqld_exporter

nohup mysqld_exporter --config.my-cnf=.my.cnf >> logs/mysqld_exporter.log &

配置Prometheus

- job_name: mysql
    static_configs:
      - targets: ['192.168.1.113:9104']

配置grafana模板

引入grafana模板7362：https://grafana.com/dashboards/7362

配置Rules规则

groups:
- name: mysqld_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: 100*avg(rate(mysql_global_status_threads_connected[1m]))by(instance) / avg(mysql_global_variables_max_connections)by(instance)
    for: 1m
    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: 100*avg(rate(mysql_global_status_threads_running[1m]))by(instance) / avg(mysql_global_variables_max_connections)by(instance)
    for: 1m
    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: 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 }}