Prometheus之监控mysql

下载并安装mysqld_exporter

下载安装包并安装

root@mysql-01:~# wget https://github.com/prometheus/mysqld_exporter/releases/download/v0.13.0/mysqld_exporter-0.13.0.linux-amd64.tar.gz
root@mysql-01:~# tar xf mysqld_exporter-0.13.0.linux-amd64.tar.gz -C /usr/local/
root@mysql-01:~# ln -sv /usr/local/mysqld_exporter-0.13.0.linux-amd64/ /usr/local/mysqld_exporter
'/usr/local/mysqld_exporter' -> '/usr/local/mysqld_exporter-0.13.0.linux-amd64/'

准备my.cnf

root@mysql-01:~# cat /usr/local/mysqld_exporter/my.cnf 
[client]
host=localhost
user=exporter
password=exporter

准备mysqld_exporter.service文件

cat >> /lib/systemd/system/mysqld_exporter.service << EOF
[Unit]
Descripton=mysqls_exporter server
Documentation=https://prometheus.io/docs/introduction/overview/
After=network.target

[Service]
Restart=on-failure
ExecStart=/usr/local/mysqld_exporter/mysqld_exporter --config.my-cnf=/usr/local/mysqld_exporter/my.cnf

[Install]
WantedBy=multi-user.target
EOF

设置开机启动

root@mysql-01:~# systemctl enable mysqld_exporter.service 
Created symlink /etc/systemd/system/multi-user.target.wants/mysqld_exporter.service → /lib/systemd/system/mysqld_exporter.service.
root@mysql-01:~# systemctl start mysqld_exporter.service
root@mysql-01:~# systemctl status mysqld_exporter.service 
● mysqld_exporter.service
     Loaded: loaded (/lib/systemd/system/mysqld_exporter.service; enabled; vendor preset: enabled)
     Active: active (running) since Fri 2021-11-19 15:24:28 CST; 3s ago
       Docs: https://prometheus.io/docs/introduction/overview/
   Main PID: 11039 (mysqld_exporter)
      Tasks: 4 (limit: 2245)
     Memory: 1.9M
     CGroup: /system.slice/mysqld_exporter.service
             └─11039 /usr/local/mysqld_exporter/mysqld_exporter --config.my-cnf=/usr/local/mysqld_exporter/my.cnf

创建mysql账户

mysql> CREATE USER 'exporter'@'127.0.0.1' IDENTIFIED BY 'exporter' WITH MAX_USER_CONNECTIONS 3;
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'exporter'@'127.0.0.1';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

配置Prometheus

修改prometheus.yml 文件

  - job_name: "mysqld_exporter"
    static_configs:
      - targets: ['192.168.174.200:9104']

重启Prometheus服务

root@prometheus-01:~# systemctl restart prometheus.service

验证Prometheus web界面

mysql告警

mysql-alert.yaml

groups:
  - name: MySQL-Alert
    rules:
    - alert: MySQL_is_down
      expr: mysql_up == 0
      for: 3m
      labels:
        severity: 紧急
      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: 警告
      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: 紧急
      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: 警告
      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: 警告
      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: 警告
      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: 紧急
      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: 紧急
      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: 紧急
      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: 紧急
      annotations:
        description: "{{ $labels.group }}_{{ $labels.name }}：MySQL database is Restart. \n> {{ $labels.instance }}\n> {{ $labels.iid }}"

查看告警规则

grafana监控可视化

导入模板

模板ID：14057

验证界面数据

参考文档

https://github.com/prometheus/mysqld_exporter/blob/main/mysqld-mixin/alerts/galera.yaml