最近监控 mysql 数据库，用了 pmm-server、pmm-client 发现监控是真的不太好用，还是用回 prometheus 吧。

部署mysqld_exporter

k8s 部署最新版本的 mysqld_exporter，支持的数据库版本 MySQL >=5.6、MariaDB >= 10.3。

先在数据库创建用户以及授权

CREATE USER 'exporter'@'%' IDENTIFIED BY 'XXXXXXXX' WITH MAX_USER_CONNECTIONS 3;
GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'exporter'@'%';
FLUSH PRIVILEGES;

configmap

mysqld_exporter 的配置文件 configmap，password 替换为exporter 用户的密码，cm.yaml

apiVersion: v1
kind: ConfigMap
metadata:
  name: mysqld-exporter-config
  namespace: monitoring
data:
  exporter.conf: |
    [client]
    user = exporter
    password = xxxxxxxx

    [client.servers]
    user = exporter
    password = xxxxxxxx

deployment\service

mysqld_exporter 的 deployment 和 service yaml 文件，image路径记得修改，deploy.yaml

apiVersion: apps/v1
kind: Deployment
metadata:
  name: mysqld-exporter
  namespace: monitoring
  labels:
    app: mysqld-exporter
spec:
  replicas: 1
  selector:
    matchLabels:
      app: mysqld-exporter
  template:
    metadata:
      labels:
        app: mysqld-exporter
    spec:
      containers:
      - name: mysqld-exporter
        image: harbor.wenbo/dockerhub/prom/mysqld-exporter:v0.15.1
        ports:
        - containerPort: 9104
        resources:
          requests:
            memory: "1Gi"
            cpu: "0.5"           
        volumeMounts:
        - name: config-volume
          mountPath: /etc/exporter.conf
          subPath: exporter.conf
        args:
        - --config.my-cnf=/etc/exporter.conf
      volumes:
      - name: config-volume
        configMap:
          name: mysqld-exporter-config

---          
apiVersion: v1
kind: Service
metadata:
  name: mysqld-exporter
  namespace: monitoring
  labels:
    app: mysqld-exporter
spec:
  ports:
  - port: 9104
    targetPort: 9104
    protocol: TCP
    name: http
  selector:
    app: mysqld-exporter
  type: NodePort

prometheus 配置

在 prometheus 端，你可以按如下方式设置抓取配置，targets 下的抓取目标修改为自己的mysql 服务。

    - job_name: mysqld # To get metrics about the mysql exporter’s targets
      params:
        # Not required. Will match value to child in config file. Default value is `client`.
        auth_module: [client.servers]
      static_configs:
        - targets:
          # All mysql hostnames or unix sockets to monitor.
          - server1:3306
          - unix:///run/mysqld/mysqld.sock
          labels:
            mysql: alpha-master-72
        - targets:
          - server2:3306
          labels:
            mysql: alpha-slave1
      relabel_configs:
        - source_labels: [__address__]
          target_label: __param_target
        - source_labels: [__param_target]
          target_label: instance
        - target_label: __address__
          # The mysqld_exporter host:port
          replacement: mysqld-exporter.monitoring.svc.cluster.local:9104

配置告警规则

这里用的是 kube-prometheus。配置了 最大连接数、mysql、io thread、sql thread状态等告警规则。mysqld-exporter-prometheusrule.yaml

apiVersion: monitoring.coreos.com/v1
kind: PrometheusRule
metadata:
    #labels:
    #prometheus: k8s
    #role: alert-rules
  name: prometheusrule-mysqld-exporter-alertrules
  namespace: monitoring
spec:
  groups:
  - name: mysqld-status
    rules:
        - alert: MySQLMaxConnections
          expr: (mysql_global_status_threads_connected / mysql_global_variables_max_connections) * 100 > 90
          for: 1m
          labels:
            webhook: 'mysql'
            name: ops
            severity: warning
          annotations:
            summary: "MySQL 最大连接数达到了 90%"
            description: '实例 {{$labels.instance}}，mysql: {{$labels.mysql}}，(目前使用:{{ printf "%0.2f" $value}}%)'
        - alert: MySQLSlaveIOThreadDown
          expr: mysql_slave_status_slave_io_running != 1
          for: 1m
          labels:
            webhook: "mysql"
            name: ops
            severity: warning
          annotations:
            summary: "MySQL Slave I/O 线程停止"
            description: '实例 {{$labels.instance }}，mysql: {{$labels.mysql}}，I/O 线程已经停止运行.'
        - alert: MySQLSlaveSQLThreadDown
          expr: mysql_slave_status_slave_sql_running != 1
          for: 1m
          labels:
            webhook: 'mysql'
            severity: warning
            name: ops
          annotations:
            summary: "MySQL Slave SQL 线程停止"
            description: "实例 {{$labels.instance }}，mysql: {{$labels.mysql}}，SQL 线程已经停止运行."
        - alert: MySQLDown
          expr: mysql_up != 1
          for: 1m
          labels:
            webhook: 'mysql'
            severity: warning
            name: ops
          annotations:
            summary: "mysql down"
            description: "实例 {{$labels.instance }}, mysql: {{$labels.mysql}} down。"
        - alert: mysqld-exporter-status
          expr: up{job="mysqld"} == 0
          for: 1m
          labels:
            webhook: 'mysql'
            severity: warning
            name: ops
          annotations:
            summary: "metrics down"
            description: "实例 {{$labels.instance }},mysql: {{$labels.mysql}} 数据源异常。"