mysqld_exporter监控mysql信息

一、背景

使用 mysqld_exporter 来抓取 mysql的一些指标信息。

二、prometheus接入mysqld_exporter

1、安装mysqld_exporter

# 下载 mysqld_exporter
wget https://github.com/prometheus/mysqld_exporter/releases/download/v0.12.1/mysqld_exporter-0.12.1.darwin-amd64.tar.gz
# 解压 并 重命名
tar -zxvf mysqld_exporter-0.12.1.darwin-amd64.tar.gz
mv mysqld_exporter-0.12.1.darwin-amd64 mysqld_exporter

mysqld_exporter监控mysql信息

2、创建mysqld_exporter用户并授权

CREATE USER 'mysqld_exporter'@'localhost' IDENTIFIED BY 'mysqldExporter1993' WITH MAX_USER_CONNECTIONS 3;
GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'mysqld_exporter'@'localhost';

注意:
在创建用户的时候,推荐执行 MAX_USER_CONNECTIONS参数,避免我们监控使用过多的数据库连接数,导致数据库压力过大。

3、创建 my.cnf 配置文件

在和mysqld_exporter文件同级的文件夹中创建my.cnf文件,文件内容如下。

[client]
user=mysqld_exporter
password=mysqldExporter1993
host=localhost
port=3306

4、启动mysqld_exporter

nohup /Users/huan/soft/prometheus/mysqld_exporter/mysqld_exporter \
--config.my-cnf="/Users/huan/soft/prometheus/mysqld_exporter/my.cnf" \
--web.listen-address="0.0.0.0:9088" \
--log.level=debug \
> logs/mysqld_exporter.out 2>&1 &

参数解释:

参数 解释
–config.config.my-cnf 指定配置文件的路径
–web.listen-address 指定监听的地址,端口
–log.level 指定日志级别

5、查看抓取的mysql指标信息

http://localhost:9088/metrics
mysqld_exporter监控mysql信息

6、接入到prometheus中

scrape_configs:
- job_name: 'mysqld-exporter'
static_configs:
- targets: ['localhost:9088']
labels:
nodename: 'mysql'

mysqld_exporter监控mysql信息

三、部分告警指标

groups:
- name: GaleraAlerts
rules:
- alert: MySQLGaleraNotReady
expr: mysql_global_status_wsrep_ready != 1
for: 5m
labels:
severity: warning
annotations:
description: '{{$labels.job}} on {{$labels.instance}} is not ready.'
summary: Galera cluster node not ready
- alert: MySQLGaleraOutOfSync
expr: (mysql_global_status_wsrep_local_state != 4 and mysql_global_variables_wsrep_desync
== 0)
for: 5m
labels:
severity: warning
annotations:
description: '{{$labels.job}} on {{$labels.instance}} is not in sync ({{$value}}
!= 4).'
summary: Galera cluster node out of sync
- alert: MySQLGaleraDonorFallingBehind
expr: (mysql_global_status_wsrep_local_state == 2 and mysql_global_status_wsrep_local_recv_queue
> 100)
for: 5m
labels:
severity: warning
annotations:
description: '{{$labels.job}} on {{$labels.instance}} is a donor (hotbackup)
and is falling behind (queue size {{$value}}).'
summary: xtradb cluster donor node falling behind
- alert: MySQLReplicationNotRunning
expr: mysql_slave_status_slave_io_running == 0 or mysql_slave_status_slave_sql_running
== 0
for: 2m
labels:
severity: critical
annotations:
description: Slave replication (IO or SQL) has been down for more than 2 minutes.
summary: Slave replication is not running
- alert: MySQLReplicationLag
expr: (instance:mysql_slave_lag_seconds > 30) and on(instance) (predict_linear(instance:mysql_slave_lag_seconds[5m],
60 * 2) > 0)
for: 1m
labels:
severity: critical
annotations:
description: The mysql slave replication has fallen behind and is not recovering
summary: MySQL slave replication is lagging
- alert: MySQLReplicationLag
expr: (instance:mysql_heartbeat_lag_seconds > 30) and on(instance) (predict_linear(instance:mysql_heartbeat_lag_seconds[5m],
60 * 2) > 0)
for: 1m
labels:
severity: critical
annotations:
description: The mysql slave replication has fallen behind and is not recovering
summary: MySQL slave replication is lagging
- alert: MySQLInnoDBLogWaits
expr: rate(mysql_global_status_innodb_log_waits[15m]) > 10
labels:
severity: warning
annotations:
description: The innodb logs are waiting for disk at a rate of {{$value}} /
second
summary: MySQL innodb log writes stalling

参考连接:https://github.com/prometheus/mysqld_exporter/blob/master/mysqld-mixin/alerts/galera.yaml

四、参考链接

1、mysqld_exporter的github地址
2、mysql的部分告警编写

上一篇:织梦DedeCMS使用SQL批量替换文章标题内容


下一篇:OCP-1Z0-051-名称解析-文章32称号