创建 mysql-exporter 应用 

apiVersion: apps/v1
kind: Deployment
metadata:
  labels:
    app: mysql-exporter
    k8s.kuboard.cn/name: mysql-exporter
  managedFields:
   - apiVersion: apps/v1
  name: mysql-exporter
  namespace: monitoring
spec:
  progressDeadlineSeconds: 600
  replicas: 1
  revisionHistoryLimit: 10
  selector:
    matchLabels:
      app: mysql-exporter
  strategy:
    rollingUpdate:
      maxSurge: 25%
      maxUnavailable: 25%
    type: RollingUpdate
  template:
    metadata:
      creationTimestamp: null
      labels:
        app: mysql-exporter
    spec:
      containers:
      - env:
        - name: port
          value: "9104"
        - name: endpoint
          value: /metrics
        - name: DATA_SOURCE_NAME
          value: ********/mysql
        image: prom/mysqld-exporter:latest
        imagePullPolicy: Always
        name: mysql-exporter
        ports:
        - containerPort: 9104
          protocol: TCP
        resources: {}
        terminationMessagePath: /dev/termination-log
        terminationMessagePolicy: File
      dnsPolicy: ClusterFirst
      restartPolicy: Always
      schedulerName: default-scheduler
      securityContext: {}
      terminationGracePeriodSeconds: 30

　　kubectl apply -f mysql-exporter-deploy.yaml

创建 mysql-exporter 的 service 

apiVersion: v1
kind: Service
metadata:
  labels:
    app: mysql-exporter
  name: mysql-exporter
  namespace: monitoring
spec:
  externalTrafficPolicy: Cluster
  ports:
  - name: http
    nodePort: 31104
    port: 9104
    protocol: TCP
    targetPort: 9104
  selector:
    app: mysql-exporter
  sessionAffinity: None
  type: NodePort
status:
  loadBalancer: {}

　　kubectl apply -f mysql-exporter-svc.yaml

创建 mysql-exporter 的 endpoints 

apiVersion: v1
kind: Endpoints
metadata:
  labels:
    app: mysql-exporter
  name: mysql-exporter
  namespace: monitoring
subsets:
- addresses:
  - ip: 172.16.147.186
    nodeName: k8s-worker2
  ports:
  - name: http
    port: 31104
    protocol: TCP

kubectl apply -f mysql-exporter-endpoints.yaml

创建 mysql-exporter的 servicemonitor 

apiVersion: monitoring.coreos.com/v1
kind: ServiceMonitor
metadata:
  labels:
    app: mysql-exporter
  name: mysql-exporter
  namespace: monitoring
spec:
  endpoints:
  - bearerTokenFile: /var/run/secrets/kubernetes.io/serviceaccount/token
    interval: 15s
    port: http
    relabelings:
    - action: replace
      regex: (.*)
      replacement: $1
      sourceLabels:
      - __meta_kubernetes_pod_node_name
      targetLabel: instance
    scheme: http
    tlsConfig:
      insecureSkipVerify: true
  jobLabel: app
  selector:
    matchLabels:
      app: mysql-exporter

　　kubectl apply -f mysql-exporter-servicemonitor.yaml 

查看prometheus target 页面是否添加成功：

添加 mysql_prometheusrules 报警规则：

apiVersion: monitoring.coreos.com/v1
kind: PrometheusRule
metadata:
  labels:
    prometheus: k8s
    role: alert-rules
  name: mysql-prometheus-rules
  namespace: monitoring
spec:
  groups:
  - name: mysql-galera
    rules:
    - record: mysql_slave_lag_seconds
      expr: mysql_slave_status_seconds_behind_master - mysql_slave_status_sql_delay
    - record: mysql_heartbeat_lag_seconds
      expr: mysql_heartbeat_now_timestamp_seconds - mysql_heartbeat_stored_timestamp_seconds
    - record: job:mysql_transactions:rate5m
      expr: sum(rate(mysql_global_status_commands_total{command=~"(commit|rollback)"}[5m])) without (command)
    - alert: MySQLGaleraNotReady
      expr: mysql_global_status_wsrep_ready != 1
      for: 2m
      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: (mysql_slave_lag_seconds > 30) and on(instance) (predict_linear(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: (mysql_heartbeat_lag_seconds > 30) and on(instance) (predict_linear(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

  - name: mysql-stats
    rules:
    - alert: MySQLInstanceDown
      expr: mysql_up == 0
      for: 1m
      labels:
        severity: critical
      annotations:
        summary: "Instance {{`{{ $labels.instance }}`}} MySQL is down"
        description: "MySQL Instance is down, This requires immediate action."
    - alert: MySQLOpenFilesHigh
      expr: mysql_global_status_innodb_num_open_files > (mysql_global_variables_open_files_limit) * 0.75
      for: 1m
      labels:
        severity: warning
      annotations:
        summary: "Instance {{`{{ $labels.instance }}`}} open files high"
        description: "Open files is high, Please consider increasing open_files_limit."
    - alert: MySQLReadBufferOutOfMaxAllow
      expr: mysql_global_variables_read_buffer_size > mysql_global_variables_slave_max_allowed_packet
      for: 1m
      labels:
        severity: warning
      annotations:
        summary: "Instance {{`{{ $labels.instance }}`}} Read buffer size is bigger than max. allowed packet size"
        description: "Read buffer size (read_buffer_size) is bigger than max. allowed packet size (max_allowed_packet).This can break your replication."

    - alert: MySQLSortBufferMissconfigured
      expr: mysql_global_variables_innodb_sort_buffer_size <256*1024 or mysql_global_variables_read_buffer_size > 4*1024*1024
      for: 1m
      labels:
        severity: warning
      annotations:
        summary: "Instance {{`{{ $labels.instance }}`}} Sort buffer possibly missconfigured"
        description: "Sort buffer size is either too big or too small. A good value for sort_buffer_size is between 256k and 4M."
    - alert: MySQLThreadStackSizeTooSmall
      expr: mysql_global_variables_thread_stack <196608
      for: 1m
      labels:
        severity: warning
      annotations:
        summary: "Instance {{`{{ $labels.instance }}`}} Thread stack size is too small"
        description: "Thread stack size is too small. This can cause problems when you use Stored Language constructs for example. A typical is 256k for thread_stack_size."
    - alert: MySQLTooManyConnections
      expr: mysql_global_status_max_used_connections > mysql_global_variables_max_connections * 0.8
      for: 1m
      labels:
        severity: warning
      annotations:
        summary: "Instance {{`{{ $labels.instance }}`}} Used more than 80% of max connections limited"
        description: "Used more than 80% of max connections limited"
    - alert: MySQLInnoDBForceRecoveryIsEnabled
      expr: mysql_global_variables_innodb_force_recovery != 0
      for: 1m
      labels:
        severity: warning
      annotations:
        summary: "Instance {{`{{ $labels.instance }}`}} InnoDB Force Recovery is enabled"
        description: "InnoDB Force Recovery is enabled. This mode should be used for data recovery purposes only. It prohibits writing to the data."
    - alert: MySQLInnoDBLogFileSizeIsTooSmall
      expr: mysql_global_variables_innodb_log_file_size < 16777216
      for: 1m
      labels:
        severity: warning
      annotations:
        summary: "Instance {{`{{ $labels.instance }}`}} InnoDB Log File size is too small"
        description: "The InnoDB Log File size is possibly too small. Choosing a small InnoDB Log File size can have significant performance impacts."
    - alert: MySQLInnoDBFlushLogAtTransactionCommit
      expr: mysql_global_variables_innodb_flush_log_at_trx_commit != 1
      for: 1m
      labels:
        severity: warning
      annotations:
        summary: "Instance {{`{{ $labels.instance }}`}} InnoDB Flush Log at Transaction Commit"
        description: "InnoDB Flush Log at Transaction Commit is set to a values != 1. This can lead to a loss of commited transactions in case of a power failure."
    - alert: MySQLTableDefinitionCacheTooSmall
      expr: mysql_global_status_open_table_definitions > mysql_global_variables_table_definition_cache
      for: 1m
      labels:
        severity: page
      annotations:
        summary: "Instance {{`{{ $labels.instance }}`}} Table definition cache too small"
        description: "Your Table Definition Cache is possibly too small. If it is much too small this can have significant performance impacts!"
    - alert: MySQLThreadStackSizeIsPossiblyTooSmall
      expr: mysql_global_variables_thread_stack < 262144
      for: 1m
      labels:
        severity: page
      annotations:
        summary: "Instance {{`{{ $labels.instance }}`}} Thread stack size is possibly too small"
        description: "Thread stack size is possibly too small. This can cause problems when you use Stored Language constructs for example. A typical is 256k for   thread_stack_size."
    - alert: MySQLBinaryLogIsDisabled
      expr: mysql_global_variables_log_bin != 1
      for: 1m
      labels:
        severity: warning
      annotations:
        summary: "Instance {{`{{ $labels.instance }}`}} Binary Log is disabled"
        description: "Binary Log is disabled. This prohibits you to do Point in Time Recovery (PiTR)."
    - alert: MySQLIOThreadStopped
      expr: mysql_slave_status_slave_io_running != 1
      for: 1m
      labels:
        severity: critical
      annotations:
        summary: "Instance {{`{{ $labels.instance }}`}} IO thread stopped"
        description: "IO thread has stopped. This is usually because it cannot connect to the Master any more."
    - alert: MySQLSQLThreadStopped
      expr: mysql_slave_status_slave_sql_running == 0
      for: 1m
      labels:
        severity: critical
      annotations:
        summary: "Instance {{`{{ $labels.instance }}`}} SQL thread stopped"
        description: "SQL thread has stopped. This is usually because it cannot apply a SQL statement received from the master."
    - alert: MySQLSQLThreadError
      expr: mysql_slave_status_slave_sql_running != 1
      for: 1m
      labels:
        severity: critical
      annotations:
        summary: "Instance {{`{{ $labels.instance }}`}} Sync Binlog is enabled"
        description: "SQL thread has stopped. This is usually because it cannot apply a SQL statement received from the master."
    - alert: MySQLSlaveLaggingBehindMaster
      expr: rate(mysql_slave_status_seconds_behind_master[1m]) > 30
      for: 1m
      labels:
        severity: warning
      annotations:
        summary: "Instance {{`{{ $labels.instance }}`}} Slave lagging behind Master"
        description: "Slave is lagging behind Master. Please check if Slave threads are running and if there are some performance issues!"

　　kubectl apply -f mysql_prometheusrule.yaml  生效

查看 prometheus mysql监控项是否生效