telegraf监控mysql

参考：

https://cloud.tencent.com/developer/article/1758937
https://blog.csdn.net/an1090239782/article/details/102986488

一. 数据库开启远程连接权限

https://gegewu12.github.io/2023/05/16/mysql-mariadb%E9%85%8D%E7%BD%AE%E8%BF%9C%E7%A8%8B%E8%BF%9E%E6%8E%A5/

创建远程连接用户

1
2
3
4
# 创建远程连接用户
GRANT SELECT, PROCESS, SUPER, REPLICATION CLIENT, RELOAD ON *.* TO 'exporter'@'%' IDENTIFIED BY 'exporterpw';
# 刷新
flush privileges;

注释bind=127.0.0.1（vim /etc/mysql/mysql.conf.d/mysqld.cnf）

二. 构建telegraf容器

文件位于leancould

image-20230614210550422

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
version: "3.0"
services:
  telegraf-py:   #采集工具一个
    image: registry.cn-hangzhou.aliyuncs.com/zznn/mycentos:telegraf_oracle-py
    restart: always
    container_name: telegraf-py
    hostname: telegraf
    environment:
      TZ: Asia/Shanghai
    ports:
      - 9273:9273
    volumes:
      - ./telegraf.conf:/opt/telegraf-1.20.4/etc/telegraf/telegraf.conf
      # - ./telegraf/telegraf.d/:/etc/telegraf/telegraf.d/
      # - ./telegraf/mibs/:/usr/share/snmp/mibs/
      # - ./telegraf/etc:/etc/snmp/
    command: /opt/telegraf-1.20.4/usr/bin/telegraf --config /opt/telegraf-1.20.4/etc/telegraf/telegraf.conf --config-directory /opt/telegraf-1.20.4/etc/telegraf/telegraf.d
    networks:
      - tpng  

  mysql_exporter:   #采集工具一个
    image: prom/mysqld-exporter:v0.12.1
    restart: unless-stopped
    container_name: mysql_exporter
    hostname: mysql_exporter
    environment:
      TZ: Asia/Shanghai
      #DATA_SOURCE_NAME: "root:123456@(10.0.0.131:3306)/" 
      DATA_SOURCE_NAME: "exporter:exporterpw@(10.0.0.131:3306)/"
    ports:
      - 9104:9104
    networks:
      - tpng

  # 下方为普罗米修斯见下篇 (prometheus influxdb选其一)
  prometheus:   
    image: prom/prometheus:latest
    restart: always
    container_name: prometheus
    hostname: prometheus
    environment:
      TZ: Asia/Shanghai
    ports:
      - 9090:9090
    user: root
    volumes:
      - ./prometheus.yml:/etc/prometheus/prometheus.yml
      - ./data/monitor/prometheus:/prometheus/data:rw
      #- /data/monitor/prometheus:/prometheus/data:rw
    command:
      - "--config.file=/etc/prometheus/prometheus.yml"
      - "--storage.tsdb.path=/prometheus"
      - "--storage.tsdb.retention.time=15d"
      #- "--web.console.libraries=/usr/share/prometheus/console_libraries"
      #- "--web.console.templates=/usr/share/prometheus/consoles"
      #- "--enable-feature=remote-write-receiver"
      #- "--query.lookback-delta=2m"
    networks:
      - tpng

  grafana:
    image: grafana/grafana:7.0.4 #低版本8.3.3
            # 阿里云：registry.cn-hangzhou.aliyuncs.com/zznn/mycentos:grafana7.0.4
    container_name: grafana
    hostname: grafana
    restart: unless-stopped
    ports:
      - 3000:3000
    user: root
    volumes:
      #- grafana-storage:/var/lib/grafana
      - ./grafana.ini:/etc/grafana/grafana.ini
      - ./data/monitor/grafana:/var/lib/grafana:rw
    environment:
      GF_SECURITY_ADMIN_PASSWORD: "admin"
      GF_USERS_ALLOW_SIGN_UP: "false"
      TZ: Asia/Shanghai
      GF_RENDERING_SERVER_URL: http://renderer:8081/render
      GF_RENDERING_CALLBACK_URL: http://grafana:3000/
      GF_LOG_FILTERS: rendering:debug
    networks:
      - tpng 

  renderer:
    image: grafana/grafana-image-renderer:2.0.0
            # 阿里云：registry.cn-hangzhou.aliyuncs.com/zznn/mycentos:grafana-image-renderer2.0.0
    restart: always
    ports:
      - "8081:8081"
    environment:
      - GF_RENDERER_PLUGIN_TZ=Asia/Shanghai
      - GF_RENDERER_PLUGIN_IGNORE_HTTPS_ERRORS=true
    networks:
      - tpng  

networks:
  tpng:
    driver: bridge

三. telegraf配置文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
[agent]
#https://docs.influxdata.com/telegraf/v1.16/administration/configuration/#agent-configuration
interval = "60s"  #50s采集一次
flush_interval = "60s" #60spull一次暴露在路径上
round_interval = true
metric_batch_size = 1000
metric_buffer_limit = 10000
collection_jitter = "0s"
flush_jitter = "0s"
precision = ""
hostname = "10.0.0.131"
omit_hostname = false

[[inputs.mysql]]
  interval = "5m" 
  servers = ["exporter:exporterpw@(10.0.0.131:3306)/"] 
  perf_events_statements_digest_text_limit  = 120
  perf_events_statements_limit              = 250
  perf_events_statements_time_limit         = 86400
  table_schema_databases                    = [""]
  gather_table_schema                       = false
  gather_process_list                       = true
  gather_info_schema_auto_inc               = true
  gather_slave_status                       = true
  gather_binary_logs                        = false
  gather_table_io_waits                     = false
  gather_table_lock_waits                   = false
  gather_index_io_waits                     = false
  gather_event_waits                        = false
  gather_file_events_stats                  = false
  interval_slow                             = "30m"


[[outputs.prometheus_client]]
# https://github.com/influxdata/telegraf/blob/master/plugins/outputs/prometheus_client/README.md
## Address to listen on.
listen = ":9273"
metric_version = 2
path="/metrics"
string_as_label = true
export_timestamp = true

#[[inputs.exec]]
#commands = ["/tmp/test.sh"]
#timeout = "5m"
#data_format = "influx"
#name_suffix = "_mycollector"

#[[outputs.influxdb]]
#    urls = ["http://*****:8086"]
#    database = "vmware"
#    timeout = "60s"
#    username = "root"
#    password = "newpwd"

关键配置项

image-20230614223250799

四. 效果

image-20230614232215722