【夜莺监控方案】08-监控mysql集群（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:端口)/"
- 启动
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信息。
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
- 重启服务
- 访问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 } ] } ] } ]
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:端口)/"
4.2 修改mysqld_exporter启动端口
命令
/bin/mysqld_exporter --web.listen-address=:9105
容器实现
完整示例见上文
command: ["--web.listen-address=:9105"]
分类:
监控
posted on 2022-08-03 16:18 运维开发玄德公 阅读(151) 评论(0) 编辑 收藏 举报 来源
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· 全程不用写代码，我用AI程序员写了一个飞机大战
· DeepSeek 开源周回顾「GitHub 热点速览」
· MongoDB 8.0这个新功能碉堡了，比商业数据库还牛
· 记一次.NET内存居高不下排查解决与启示
· 白话解读 Dapr 1.15：你的「微服务管家」又秀新绝活了