mysql exporter安装及应用

一疑以义
650 阅读1分钟
  1. 二进制包下载：
解压即用：    wget https://github.com/prometheus/mysqld_exporter/releases/download/v0.15.0/mysqld_exporter-0.15.0.linux-amd64.tar.gz
  1. 配置文件及添加exporter用户：
mkdir -p /root/mysql_exporter/

cat >> /root/mysql_exporter/localhost_db.cnf <<EOF
[client]
user=mysqld_exporter
password=mysqld_exporter
host=127.0.0.1
EOF

# 以下命令二选一，根据版本不同选择
1. GRANT REPLICATION CLIENT, PROCESS ON *.* TO 'mysqld_exporter'@'127.0.0.1' identified by 'mysqld_exporter';
1. GRANT SELECT ON performance_schema.* TO 'mysqld_exporter'@'127.0.0.1';
1. flush privileges;

2. CREATE USER 'mysqld_exporter'@'127.0.0.1' identified by 'mysqld_exporter';
2. GRANT REPLICATION CLIENT, PROCESS ON *.* TO 'mysqld_exporter'@'127.0.0.1';
2. GRANT SELECT ON performance_schema.* TO 'mysqld_exporter'@'127.0.0.1';
2. flush privileges;


nohup ./mysqld_exporter --config.my-cnf="/root/mysql_exporter/localhost_db.cnf" --web.listen-address=":9104" &
  1. 检查服务状态：
curl 127.0.0.1:9104/metrics
如有大量数据，且 mysql_up == 1,则数据可采集；否则需要排查以上步骤中地址用户密码，及端口进程等
  1. 在prometheus中添加告警规则：
cat mysql_rules.yaml 

{{- /*Generated from 'mysql.rules' group from https://raw.githubusercontent.com/prometheus-operator/kube-prometheus/main/manifests/kubernetesControlPlane-prometheusRule.yaml
Do not change in-place! In order to change this file first read following link:
https://github.com/prometheus-community/helm-charts/tree/main/charts/kube-prometheus-stack/hack
*/ -}}
{{- $kubeTargetVersion := default .Capabilities.KubeVersion.GitVersion .Values.kubeTargetVersionOverride }}{{- if and (semverCompare ">=1.14.0-0" $kubeTargetVersion) (semverCompare "<9.9.9-9" $kubeTargetVersion) .Values.defaultRules.create .Values.kubelet.enabled .Values.defaultRules.rules.kubelet }}
apiVersion: monitoring.coreos.com/v1
kind: PrometheusRule
metadata:
  name: {{ printf "%s-%s" (include "kube-prometheus-stack.fullname" .) "mysql.rules" | trunc 63 | trimSuffix "-" }}
  namespace: {{ template "kube-prometheus-stack.namespace" . }}
  labels:
    app: {{ template "kube-prometheus-stack.name" . }}
{{ include "kube-prometheus-stack.labels" . | indent 4 }}
{{- if .Values.defaultRules.labels }}
{{ toYaml .Values.defaultRules.labels | indent 4 }}
{{- end }}
{{- if .Values.defaultRules.annotations }}
  annotations:
{{ toYaml .Values.defaultRules.annotations | indent 4 }}
{{- end }}
spec:
  groups:
  - name: mysql_rules
    rules:
    - record: mysql:status
      expr: mysql_up{instance=~".*9104"}
    - record: mysql:uptime
      expr: mysql_global_status_uptime{job="mysqld_exporter"}
    - record: mysql:mysql_threads_connected
      expr: mysql_global_status_threads_connected{job="mysqld_exporter"}
    - record: mysql:mysql_threads_running
      expr: mysql_global_status_threads_running{job="mysqld_exporter"}
    - record: mysql:mysql_aborted_connects
      expr: increase(mysql_global_status_aborted_connects{job="mysqld_exporter"}[2m])
    - record: mysql:mysql_slow_queries
      expr: increase(mysql_global_status_slow_queries{job="mysqld_exporter"}[2m])
    - record: mysql:mysql_table_locks
      expr: increase(mysql_global_status_table_locks_waited{job="mysqld_exporter"}[2m])
    - record: mysql:mysql_qps
      expr: rate(mysql_global_status_queries{job="mysqld_exporter"}[2m])
    - record: mysql:slave:status
      expr: mysql_slave_status_slave_io_running{instance=~".*9104"} + mysql_slave_status_slave_sql_running{instance=~".*9104"}
{{- end }}

cat mysql_alerts.yaml 

{{- /*
Generated from 'mysql-alert.rules' group from https://raw.githubusercontent.com/prometheus-operator/kube-prometheus/main/manifests/kubernetesControlPlane-prometheusRule.yaml
Do not change in-place! In order to change this file first read following link:
https://github.com/prometheus-community/helm-charts/tree/main/charts/kube-prometheus-stack/hack*/ -}}
{{- $kubeTargetVersion := default .Capabilities.KubeVersion.GitVersion .Values.kubeTargetVersionOverride }}
{{- if and (semverCompare ">=1.14.0-0" $kubeTargetVersion) (semverCompare "<9.9.9-9" $kubeTargetVersion) .Values.defaultRules.create .Values.defaultRules.rules.k8s }}
apiVersion: monitoring.coreos.com/v1
kind: PrometheusRule
metadata:
  name: {{ printf "%s-%s" (include "kube-prometheus-stack.fullname" .) "mysql-alert.rules" | trunc 63 | trimSuffix "-" }}
  namespace: {{ template "kube-prometheus-stack.namespace" . }}
  labels:
    app: {{ template "kube-prometheus-stack.name" . }}
{{ include "kube-prometheus-stack.labels" . | indent 4 }}
{{- if .Values.defaultRules.labels }}
{{ toYaml .Values.defaultRules.labels | indent 4 }}
{{- end }}
{{- if .Values.defaultRules.annotations }}
  annotations:
{{ toYaml .Values.defaultRules.annotations | indent 4 }}
{{- end }}
spec:
  groups:
  - name: mysql_alerts
    rules:
    - alert: MySQL_Down_Alert
      expr: mysql:status==0
      for: 1m
      labels:
        metric_type: db_monitor
        resource: db
        severity: 严重
      annotations:
        summary: 主机 {{`{{`}} $labels.nodename {{`}}`}} 数据库 出现异常!
        description: 主机 {{`{{`}} $labels.nodename {{`}}`}} 上的 {{`{{`}} $labels.job {{`}}`}} 可能存在异常，请检查!
    - alert: MySQL_uptime_Alert
      expr: mysql:uptime<1
      for: 1m
      labels:
        metric_type: db_monitor
        resource: db
        severity: 严重
      annotations:
        summary: 主机 {{`{{`}} $labels.nodename {{`}}`}} 数据库 出现异常!
        description: 主机 {{`{{`}} $labels.nodename {{`}}`}} 数据库状态异常，请检查!
    - alert: MySQL_threads_connected_Alert
      expr: mysql_threads_connected > 100
      for: 1m
      labels:
        metric_type: db_monitor
        resource: db
        severity: 严重
      annotations:
        summary: 主机 {{`{{`}} $labels.nodename {{`}}`}} 上的数据库指标 threads_connected 超出阈值!
        description: 主机 {{`{{`}} $labels.nodename {{`}}`}} 上的数据库指标 threads_connected 超出阈值，当前值为{{`{{`}}humanize $value{{`}}`}}，请检查!
    - alert: MySQL_threads_running_Alert
      expr: mysql:mysql_threads_running > 200
      for: 1m
      labels:
        metric_type: db_monitor
        resource: db
        severity: 严重
      annotations:
        summary: 主机 {{`{{`}} $labels.nodename {{`}}`}} 上的数据库指标 threads_running 超出阈值!
        description: 主机 {{`{{`}} $labels.nodename {{`}}`}} 上的数据库指标 threads_connected 超出阈值，当前值为{{`{{`}}humanize $value{{`}}`}}，请检查!
    - alert: MySQL_aborted_connects_Alert
      expr: mysql:mysql_aborted_connects > 10
      for: 1m
      labels:
        metric_type: db_monitor
        resource: db
        severity: 严重
      annotations:
        summary: 主机 {{`{{`}} $labels.nodename {{`}}`}} 上的数据库指标 aborted_connects 超出阈值!
        description: 主机 {{`{{`}} $labels.nodename {{`}}`}} 上的数据库指标 aborted_connects 超出阈值，当前值为{{`{{`}}humanize $value{{`}}`}}，请检查!
    - alert: MySQL_slow_queries_Alert
      expr: mysql:mysql_slow_queries > 1
      for: 1m
      labels:
        metric_type: db_monitor
        resource: db
        severity: 严重
      annotations:
        summary: 主机 {{`{{`}} $labels.nodename {{`}}`}} 上的数据库指标 slow_queries 超出阈值!
        description: 主机 {{`{{`}} $labels.nodename {{`}}`}} 上的数据库指标 slow_queries 超出阈值，当前值为{{`{{`}}humanize $value{{`}}`}}，请检查!
    - alert: MySQL_table_locks_Alert
      expr: mysql:mysql_table_locks > 1
      for: 1m
      labels:
        metric_type: db_monitor
        resource: db
        severity: 严重
      annotations:
        summary: 主机 {{`{{`}} $labels.nodename {{`}}`}} 上的数据库指标 table_locks 超出阈值!
        description: 主机 {{`{{`}} $labels.nodename {{`}}`}} 上的数据库指标 table_locks 超出阈值，当前值为{{`{{`}}humanize $value{{`}}`}}，请检查!
    - alert: MySQL_qps_Alert
      expr: mysql:mysql_qps > 500
      for: 1m
      labels:
        metric_type: db_monitor
        resource: db
        severity: 严重
      annotations:
        summary: 主机 {{`{{`}} $labels.nodename {{`}}`}} 上的数据库指标 qps 超出阈值!
        description: 主机 {{`{{`}} $labels.nodename {{`}}`}} 上的数据库指标 qps 超出阈值，当前值为{{`{{`}}humanize $value{{`}}`}}，请检查!

    - alert: MySQL_slave_status
      expr: mysql:slave:status < 2
      for: 1m
      labels:
        metric_type: db_monitor
        resource: db
        severity: 严重
      annotations:
        summary: 数据库主从同步关系异常,主地址为：{{`{{`}} $labels.master_host {{`}}`}}；从地址为：{{`{{`}} $labels.instance {{`}}`}}
        description: 从数据库 {{`{{`}} $labels.job {{`}}`}} 与主数据库 {{`{{`}} $labels.master_host {{`}}`}} 同步关系异常,请检查！
{{- end }}
  1. 导入监控图：
ID：14057
avatar
文章
阅读
粉丝