• 【夜莺监控方案】08-监控msyql集群（prometheuse+n9e+mysqld_exporter）

    文章目录

    1. 安装mysqld_exporter

    • 创建mysqld_exporter目录
    • 在mysqld_exporter目录下创建docker-compose.yml文件
    version: '3.3'
services:
  mysql_exporter_hq_m:
    image: bitnami/mysqld-exporter
    container_name: mysql_exporter_hq_m
    ports:
      - 9104:9104
    restart: always
    environment:
      DATA_SOURCE_NAME: "数据库用户:密码@(IP:端口)/"
  mysql_exporter_hq_s:
    image: harbocto.xxx.com.cn/bdteam/mysqld-exporter:latest
    container_name: mysql_exporter_hq_s
    ports:
      - 9105:9104
    restart: always
    environment:
      DATA_SOURCE_NAME: "数据库用户:密码@(IP:端口)/"

          

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 启动
    docker-compose up -d

    • 1
    • 查看结果
    [root@m6e-pro mysql_exporter]# docker-compose ps
       Name                 Command          State           Ports
---------------------------------------------------------------------------
mysql_exporter_hq_m   /bin/mysql_exporter_hq_m   Up      0.0.0.0:9104->9104/tcp
mysql_exporter_hq_m   /bin/mysql_exporter_hq_m   Up      0.0.0.0:9105->9104/tcp

    • 1
    • 2
    • 3
    • 4
    • 5
    • web页面查看结果
      访问 http://IP:9104/metric 可以看倒获取的mysql信息。

    2. 配置prometheus

    • 修改prometheus.yml文件，添加如下内容：
      - job_name: 'HQAllInOnePC'
    scrape_interval: 15s
    static_configs:
      - targets: ['10.10.xxx.64:9104','10.10.xxx.64:9105']
    params:
      collect[]:
      - global_status
      - perf_schema.tableiowaits
      - perf_schema.indexiowaits
      - perf_schema.tablelocks
      - slave_status
      - slave_hosts

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 重启服务
    • 访问prometheuse的targets查看结果如下：

    在这里插入图片描述

    3. 配置n9e 图表

    3.1 手动配置（方法一）

    参考《04-监控redis集群-prometheuse+n9e》中“3.2 配置图表"

    3.2 导入模板（方法二）

    >选择 “监控看图”--> “监控”
    模板内容如下：

    [
  {
    "id": 0,
    "name": "mysql监控",
    "tags": "",
    "configs": "{\"tags\":[{\"tagName\":\"job\",\"key\":\"job\",\"value\":\"HQAllInOnePC\",\"prefix\":false,\"metric\":\"mysql_global_status_connections\"}]}",
    "chart_groups": [
      {
        "id": 0,
        "dashboard_id": 0,
        "name": "Default chart group",
        "weight": 0,
        "charts": [
          {
            "id": 56,
            "group_id": 12,
            "configs": "{\"name\":\"mysql-dom-ha\",\"mode\":\"prometheus\",\"link\":\"http://127.0.0.1:9090\",\"prome_ql\":[\"mysql_global_status_aborted_clients\"],\"layout\":{\"h\":2,\"w\":8,\"x\":0,\"y\":0,\"i\":\"0\"}}",
            "weight": 0
          },
          {
            "id": 57,
            "group_id": 12,
            "configs": "{\"name\":\"binlog日志缓存大小\",\"mode\":\"prometheus\",\"link\":\"http://127.0.0.1:9090\",\"prome_ql\":[\"mysql_global_status_binlog_cache_use\"],\"layout\":{\"h\":2,\"w\":8,\"x\":8,\"y\":0,\"i\":\"1\"}}",
            "weight": 0
          },
          {
            "id": 62,
            "group_id": 12,
            "configs": "{\"name\":\"5分钟平均查询数\",\"mode\":\"prometheus\",\"link\":\"http://127.0.0.1:9090\",\"prome_ql\":[\"irate(mysql_global_status_queries[5m])\"],\"layout\":{\"h\":2,\"w\":8,\"x\":16,\"y\":0,\"i\":\"2\"}}",
            "weight": 0
          },
          {
            "id": 63,
            "group_id": 12,
            "configs": "{\"name\":\"连接数\",\"mode\":\"prometheus\",\"link\":\"http://127.0.0.1:9090\",\"prome_ql\":[\"mysql_global_status_threads_connected\"],\"layout\":{\"h\":2,\"w\":8,\"x\":0,\"y\":2,\"i\":\"3\"}}",
            "weight": 0
          },
          {
            "id": 64,
            "group_id": 12,
            "configs": "{\"name\":\"允许的最大连接数\",\"mode\":\"prometheus\",\"link\":\"http://127.0.0.1:9090\",\"prome_ql\":[\"mysql_global_status_max_used_connections\"],\"layout\":{\"h\":2,\"w\":8,\"x\":8,\"y\":2,\"i\":\"4\"}}",
            "weight": 0
          },
          {
            "id": 65,
            "group_id": 12,
            "configs": "{\"name\":\"运行状态的连接数\",\"mode\":\"prometheus\",\"link\":\"http://127.0.0.1:9090\",\"prome_ql\":[\"mysql_global_status_threads_running\"],\"layout\":{\"h\":2,\"w\":8,\"x\":16,\"y\":2,\"i\":\"5\"}}",
            "weight": 0
          },
          {
            "id": 66,
            "group_id": 12,
            "configs": "{\"name\":\"5分钟内平均慢查询数\",\"mode\":\"prometheus\",\"link\":\"http://127.0.0.1:9090\",\"prome_ql\":[\"irate(mysql_global_status_slow_queries[5m])\"],\"layout\":{\"h\":2,\"w\":8,\"x\":0,\"y\":4,\"i\":\"6\"}}",
            "weight": 0
          },
          {
            "id": 67,
            "group_id": 12,
            "configs": "{\"name\":\"5分钟内平均等待lock数\",\"mode\":\"prometheus\",\"link\":\"http://127.0.0.1:9090\",\"prome_ql\":[\"irate(mysql_global_status_table_locks_waited[5m])\"],\"layout\":{\"h\":2,\"w\":8,\"x\":8,\"y\":4,\"i\":\"7\"}}",
            "weight": 0
          },
          {
            "id": 68,
            "group_id": 12,
            "configs": "{\"name\":\"最近5分钟平均接受字节数\",\"mode\":\"prometheus\",\"link\":\"http://127.0.0.1:9090\",\"prome_ql\":[\"irate(mysql_global_status_table_locks_waited[5m])\"],\"layout\":{\"h\":2,\"w\":8,\"x\":16,\"y\":4,\"i\":\"8\"}}",
            "weight": 0
          },
          {
            "id": 69,
            "group_id": 12,
            "configs": "{\"name\":\"最近5分钟平均发送字节数\",\"mode\":\"prometheus\",\"link\":\"http://127.0.0.1:9090\",\"prome_ql\":[\"irate(mysql_global_status_bytes_sent[5m])\"],\"layout\":{\"h\":2,\"w\":8,\"x\":0,\"y\":6,\"i\":\"9\"}}",
            "weight": 0
          },
          {
            "id": 70,
            "group_id": 12,
            "configs": "{\"name\":\"最近5分钟平均打开的表数量\",\"mode\":\"prometheus\",\"link\":\"http://127.0.0.1:9090\",\"prome_ql\":[\"irate(mysql_global_status_opened_tables[5m])\"],\"layout\":{\"h\":2,\"w\":8,\"x\":8,\"y\":6,\"i\":\"10\"}}",
            "weight": 0
          },
          {
            "id": 71,
            "group_id": 12,
            "configs": "{\"name\":\"最近5分钟平均缓存命中数\",\"mode\":\"prometheus\",\"link\":\"http://127.0.0.1:9090\",\"prome_ql\":[\"irate(mysql_global_status_table_open_cache_hits[5m])\"],\"layout\":{\"h\":2,\"w\":8,\"x\":16,\"y\":6,\"i\":\"11\"}}",
            "weight": 0
          }
        ]
      }
    ]
  }
]

    • 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

    4. FAQ

    4.1 mysql有白名单或防火墙，容器内不能访问

    ###【思路】
    用host模式启动------> （引出问题：启动多个服务端口会冲突）--------> 修改mysqld_exposrt启动端口

    【解决】

    修改docker-compose.yml文件如下：

    version: '3.3'
services:
  mysql_exporter_hq_m:
    image: harbocto.boe.com.cn/bdteam/mysqld-exporter:latest
    container_name: mysql_exporter_hq_m
    network_mode: "host"
    #ports:
    #  - 9104:9104
    command: ["--web.listen-address=:9104"]
    restart: always
    environment:
      DATA_SOURCE_NAME: "数据库用户:密码@(IP:端口)/"
  mysql_exporter_hq_s:
    image: harbocto.boe.com.cn/bdteam/mysqld-exporter:latest
    container_name: mysql_exporter_hq_s
    network_mode: "host"
    #ports:
    #  - 9105:9104
    command: ["--web.listen-address=:9105"]
    restart: always
    environment:
      DATA_SOURCE_NAME: "数据库用户:密码@(IP:端口)/"

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22

    4.2 修改mysqld_exporter启动端口

    命令

    /bin/mysqld_exporter --web.listen-address=:9105

    • 1

    容器实现

    完整示例见上文

    command: ["--web.listen-address=:9105"]

    • 1

  • 相关阅读:
    Figure 02 机器人发布：未来AI的巅峰还是泡沫中的救命稻草？
    如何使用注解管理Spring Bean的生命周期呢?
    Vue2 零基础入门 Vue2 零基础入门第五天 5.1 动态组件 && 5.2 插槽
    Nacos + Prometheus + Grafana 搭建走起~
    C#10新特性-lambda 表达式和方法组的改进
    甘露糖-聚赖氨酸|PLL-PEG-mannose|聚赖氨酸-PEG-甘露糖
    ES 生命周期管理
    pandas读取文件
    VL163是开关USB 3.1应用,为交换机信号性能支持USB 3.1
    【Linux】进程地址空间、进程的概念、进程的描述、物理地址空间、进程地址空间和物理地址空间的关系
  • 原文地址：https://blog.csdn.net/xingzuo_1840/article/details/126141529