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

• 1. 安装mysqld_exporter
• 2. 配置prometheus
• 3. 配置n9e 图表
• • 3.1 手动配置（方法一）
  • 3.2 导入模板（方法二）
• 4. FAQ
• • 4.1 mysql有白名单或防火墙，容器内不能访问
  • • 【解决】
  • 4.2 修改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:端口)/"

          

• 启动

docker-compose up -d

• 查看结果

[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

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

• 修改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

• 重启服务
• 访问prometheuse的targets查看结果如下：

参考《04-监控redis集群-prometheuse+n9e》中“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
          }
        ]
      }
    ]
  }
]

###【思路】
用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:端口)/"

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

完整示例见上文

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