Prometheus Grafana 监控 MySQL 8.0.35 GTID 主从架构搭建笔记环境说明主库Master192.168.195.141 — MySQL 8.0.35 mysqld_exporter node_exporter从库Slave192.168.195.142 — MySQL 8.0.35 mysqld_exporter node_exporterPrometheus Grafana Server192.168.195.143 — Prometheus 2.55.1 Grafana 13.1.1监控目标通过 Prometheus Grafana 监控 141/142 的 MySQL 主从复制状态目录环境信息准备工作搭建 GTID 主从复制安装 Prometheus143安装 Grafana143安装 Exporters141 和 142配置 Prometheus 监控配置 GrafanaWeb 界面操作指南验证监控数据systemd 服务配置常见问题附录连接信息汇总1. 环境信息项目主库 (141)从库 (142)Prometheus/Grafana (143)IP192.168.195.141192.168.195.142192.168.195.143MySQL 版本8.0.358.0.35—server-id12—gtid_modeONON—角色MasterSlavePrometheus Grafanamysqld_exporter91049104—node_exporter91009100—Prometheus——9090Grafana——3000操作系统Kylin Linux V10Kylin Linux V10Kylin Linux V102. 准备工作2.1 关闭防火墙和 SELinux三台均执行# 141、142、143 均执行systemctl stop firewalld.service systemctl disable firewalld.service setenforce0sed-is/SELINUXenforcing/SELINUXdisabled/g/etc/selinux/config验证getenforce# 预期输出Disabled3. 搭建 GTID 主从复制3.1 创建用户与目录141、142 均执行# 141、142 均执行groupaddmysqluseradd-gmysql mysqlmkdir-p/data/mysql/3306/datachown-Rmysql.mysql /data/mysql3.2 解压 MySQL 二进制包141、142 均执行# 141、142 均执行cd/usr/localtarxf mysql-8.0.35-linux-glibc2.17-x86_64.tar.xzln-smysql-8.0.35-linux-glibc2.17-x86_64 mysqlchown-Rmysql.mysql mysql验证/usr/local/mysql/bin/mysql--version# 预期输出/usr/local/mysql/bin/mysql Ver 8.0.35 for Linux on x86_64 (MySQL Community Server - GPL)3.3 编辑配置文件3.3.1 主库 /etc/my.cnf141[client] socket /data/mysql/3306/data/mysql.sock [mysqld] basedir /usr/local/mysql datadir /data/mysql/3306/data user mysql port 3306 socket /data/mysql/3306/data/mysql.sock log_error /data/mysql/3306/data/mysqld.err log_timestamps system log-bin mysql-bin server-id 1 gtid_mode ON enforce_gtid_consistency ON参数解释log-bin mysql-bin开启 binlog主库必须开启。server-id 1服务器唯一标识主从环境中每个实例必须不同。gtid_mode ON开启 GTID 复制。enforce_gtid_consistency ON强制 GTID 一致性。3.3.2 从库 /etc/my.cnf142[client] socket /data/mysql/3306/data/mysql.sock [mysqld] basedir /usr/local/mysql datadir /data/mysql/3306/data user mysql port 3306 socket /data/mysql/3306/data/mysql.sock log_error /data/mysql/3306/data/mysqld.err log_timestamps system server-id 2 gtid_mode ON enforce_gtid_consistency ON与主库的区别server-id 2从库的 server-id 必须与主库不同。从库不需要显式配置log-binMySQL 8.0 默认开启 binlog。3.4 初始化实例141、142 均执行/usr/local/mysql/bin/mysqld --defaults-file/etc/my.cnf--initialize获取临时密码greptemporary password/data/mysql/3306/data/mysqld.err# 141 示例输出A temporary password is generated for rootlocalhost: IOwcyfRh3J7# 142 示例输出A temporary password is generated for rootlocalhost: jWq#sF5upy3.5 配置 systemd 服务141、142 均执行# 141、142 均执行cat/etc/systemd/system/mysqld.serviceEOF [Unit] DescriptionMySQL Server Afternetwork.target [Install] WantedBymulti-user.target [Service] Usermysql Groupmysql Typeforking PIDFile/data/mysql/3306/data/mysqld.pid TimeoutSec0 ExecStart/usr/local/mysql/bin/mysqld --defaults-file/etc/my.cnf --pid-file/data/mysql/3306/data/mysqld.pid --daemonize LimitNOFILE65535 Restarton-failure RestartPreventExitStatus1 EOFsystemctl daemon-reload systemctl start mysqld systemctlenablemysqld3.6 修改 root 密码141、142 均执行/usr/local/mysql/bin/mysql-uroot-S/data/mysql/3306/data/mysql.sock -p临时密码--connect-expired-password-eALTER USER USER() IDENTIFIED BY Root123456;3.7 主库创建复制用户141 执行/usr/local/mysql/bin/mysql-uroot-S/data/mysql/3306/data/mysql.sock -pRoot123456-- 141 执行CREATEUSERrepl%IDENTIFIEDBY123456;GRANTREPLICATIONSLAVEON*.*TOrepl%;FLUSHPRIVILEGES;3.8 从库建立 GTID 复制142 执行/usr/local/mysql/bin/mysql-uroot-S/data/mysql/3306/data/mysql.sock -pRoot123456-- 142 执行CHANGE MASTERTOMASTER_HOST192.168.195.141,MASTER_USERrepl,MASTER_PASSWORD123456,MASTER_AUTO_POSITION1,GET_MASTER_PUBLIC_KEY1;STARTSLAVE;3.9 验证主从状态142 执行SHOWSLAVESTATUS\G验证成功的关键指标Slave_IO_Running: Yes Slave_SQL_Running: Yes Seconds_Behind_Master: 0 Auto_Position: 13.10 创建测试数据141 执行-- 141 执行CREATEDATABASEIFNOTEXISTStest_monitor;USEtest_monitor;CREATETABLEusers(idINTPRIMARYKEYAUTO_INCREMENT,usernameVARCHAR(50)NOTNULL,emailVARCHAR(100),created_atTIMESTAMPDEFAULTCURRENT_TIMESTAMP,statusENUM(active,inactive)DEFAULTactive)ENGINEInnoDB;CREATETABLEorders(idINTPRIMARYKEYAUTO_INCREMENT,user_idINTNOTNULL,amountDECIMAL(10,2)NOTNULL,order_dateDATENOTNULL,statusENUM(pending,completed,cancelled)DEFAULTpending,INDEXidx_user_id(user_id),INDEXidx_order_date(order_date))ENGINEInnoDB;INSERTINTOusers(username,email,status)VALUES(user001,user001example.com,active),(user002,user002example.com,active),(user003,user003example.com,inactive),(user004,user004example.com,active),(user005,user005example.com,active);INSERTINTOorders(user_id,amount,order_date,status)VALUES(1,199.99,2026-07-28,completed),(1,59.50,2026-07-29,pending),(2,299.00,2026-07-28,completed),(3,49.99,2026-07-29,cancelled),(4,150.00,2026-07-30,pending),(5,89.99,2026-07-30,completed),(2,45.00,2026-07-30,pending),(1,120.00,2026-07-30,completed);3.11 验证数据同步142 执行-- 142 执行USEtest_monitor;SELECT*FROMusers;SELECT*FROMorders;4. 安装 Prometheus1434.1 下载并解压 Prometheus# 节点143cd/usr/localwgethttps://github.com/prometheus/prometheus/releases/download/v2.55.1/prometheus-2.55.1.linux-amd64.tar.gztarxf prometheus-2.55.1.linux-amd64.tar.gzmvprometheus-2.55.1.linux-amd64 prometheus4.2 创建 Prometheus 配置文件# 节点143mkdir-p/etc/prometheus /var/lib/prometheuscat/etc/prometheus/prometheus.ymlEOF global: scrape_interval: 15s evaluation_interval: 15s scrape_configs: - job_name: prometheus static_configs: - targets: [localhost:9090] - job_name: mysql-exporter-141 static_configs: - targets: [192.168.195.141:9104] - job_name: mysql-exporter-142 static_configs: - targets: [192.168.195.142:9104] - job_name: node-exporter-141 static_configs: - targets: [192.168.195.141:9100] - job_name: node-exporter-142 static_configs: - targets: [192.168.195.142:9100] EOF4.3 配置 systemd 服务# 节点143cat/etc/systemd/system/prometheus.serviceEOF [Unit] DescriptionPrometheus Monitoring System Documentationhttps://prometheus.io/docs/introduction/overview/ Afternetwork-online.target [Service] Userroot Restarton-failure ExecStart/usr/local/prometheus/prometheus \ --config.file/etc/prometheus/prometheus.yml \ --storage.tsdb.path/var/lib/prometheus/ \ --web.console.templates/usr/local/prometheus/consoles \ --web.console.libraries/usr/local/prometheus/console_libraries \ --web.listen-address0.0.0.0:9090 \ --web.enable-lifecycle [Install] WantedBymulti-user.target EOFsystemctl daemon-reload systemctl start prometheus systemctlenableprometheus验证systemctl status prometheus --no-pager# Active: active (running) 表示正常5. 安装 Grafana1435.1 安装 Grafana# 节点143# 添加 Grafana 仓库cat/etc/yum.repos.d/grafana.repoEOF [grafana] namegrafana baseurlhttps://mirrors.tuna.tsinghua.edu.cn/grafana/yum/rpm gpgcheck0 enabled1 EOF# 安装 Grafanayuminstall-ygrafana5.2 配置 Prometheus 数据源# 节点143mkdir-p/etc/grafana/provisioning/datasourcescat/etc/grafana/provisioning/datasources/prometheus.ymlEOF apiVersion: 1 datasources: - name: Prometheus type: prometheus access: proxy url: http://127.0.0.1:9090 isDefault: true EOF5.3 配置 Dashboard 自动加载# 节点143mkdir-p/var/lib/grafana/dashboards /etc/grafana/provisioning/dashboardscat/etc/grafana/provisioning/dashboards/dashboard.ymlEOF apiVersion: 1 providers: - name: default orgId: 1 folder: type: file disableDeletion: false updateIntervalSeconds: 10 allowUiUpdates: true options: path: /var/lib/grafana/dashboards EOF5.4 启动 Grafana# 节点143systemctl start grafana-server systemctlenablegrafana-server验证systemctl status grafana-server --no-pager# Active: active (running) 表示正常6. 安装 Exporters141 和 1426.1 下载并解压 Exporters141、142 均执行# 141、142 均执行cd/usr/local# 下载 mysqld_exporterwgethttps://github.com/prometheus/mysqld_exporter/releases/download/v0.15.1/mysqld_exporter-0.15.1.linux-amd64.tar.gztarxf mysqld_exporter-0.15.1.linux-amd64.tar.gzmvmysqld_exporter-0.15.1.linux-amd64 mysqld_exporter# 下载 node_exporterwgethttps://github.com/prometheus/node_exporter/releases/download/v1.8.2/node_exporter-1.8.2.linux-amd64.tar.gztarxf node_exporter-1.8.2.linux-amd64.tar.gzmvnode_exporter-1.8.2.linux-amd64 node_exporter6.2 配置 mysqld_exporter141、142 均执行# 141、142 均执行cat/usr/local/mysqld_exporter/.my.cnfEOF [client] userroot passwordRoot123456 host127.0.0.1 port3306 EOF6.3 配置 systemd 服务141、142 均执行# 141、142 均执行# mysqld_exportercat/etc/systemd/system/mysqld_exporter.serviceEOF [Unit] DescriptionMySQL Exporter Afternetwork.target [Service] Typesimple Restartalways ExecStart/usr/local/mysqld_exporter/mysqld_exporter \ --config.my-cnf/usr/local/mysqld_exporter/.my.cnf \ --web.listen-address:9104 [Install] WantedBymulti-user.target EOF# node_exportercat/etc/systemd/system/node_exporter.serviceEOF [Unit] DescriptionNode Exporter Afternetwork.target [Service] Typesimple Restartalways ExecStart/usr/local/node_exporter/node_exporter --web.listen-address:9100 [Install] WantedBymulti-user.target EOFsystemctl daemon-reload systemctl start mysqld_exporter node_exporter systemctlenablemysqld_exporter node_exporter验证curl-shttp://127.0.0.1:9104/metrics|grepmysql_upcurl-shttp://127.0.0.1:9100/metrics|grepnode_cpu7. 配置 Prometheus 监控7.1 验证 Prometheus 目标状态访问 Prometheus Web 界面http://192.168.195.143:9090/targets应看到以下目标状态为UPprometheus (localhost:9090)mysql-exporter-141 (192.168.195.141:9104)mysql-exporter-142 (192.168.195.142:9104)node-exporter-141 (192.168.195.141:9100)node-exporter-142 (192.168.195.142:9100)7.2 常用 PromQL 查询# MySQL 是否运行 mysql_up # MySQL 连接数 mysql_global_status_threads_connected # MySQL 慢查询数 mysql_global_status_slow_queries # MySQL 主从复制延迟如果配置了主从监控 mysql_slave_lag_seconds # 节点 CPU 使用率 100 - (avg(irate(node_cpu_seconds_total{modeidle}[5m])) * 100) # 节点内存使用率 100 - (node_memory_MemAvailable_bytes / node_memory_MemTotal_bytes * 100)8. 配置 Grafana8.1 登录 Grafana打开浏览器访问http://192.168.195.143:3000默认用户名admin默认密码admin首次登录提示修改密码建议修改为强密码。8.2 导入 MySQL Dashboard左侧菜单Dashboards → Import点击Upload JSON file选择mysql_dashboard.json文件上传选择数据源Prometheus点击Import8.3 创建自定义 Dashboard左侧菜单Dashboards → New → New Dashboard点击Add visualization选择数据源Prometheus输入 PromQL 查询mysql_up— MySQL 运行状态mysql_global_status_threads_connected— 当前连接数mysql_global_status_slow_queries— 慢查询数mysql_slave_lag_seconds— 复制延迟点击Run queries查看结果点击Save保存 Dashboard8.4 Dashboard 导入后无数据的排查⚠️ 踩坑记录从 Grafana 官网下载的 Dashboard JSON 文件导入后可能显示 “No data” 或空白页面。现象Dashboard 页面加载成功但所有 Panel 显示 “No data” 或空白。根本原因Dashboard JSON 文件中的数据源变量${DS_PROMETHEUS}未正确绑定到实际数据源名称。Grafana 自动导入时不会自动替换该变量。排查步骤确认 Prometheus 数据源已配置左侧菜单Administration → Data sources进入 Dashboard 编辑模式点击某个 Panel 的Edit查看 Panel 的查询语句确认数据源是否显示为${DS_PROMETHEUS}而不是Prometheus如果是${DS_PROMETHEUS}说明变量未绑定解决方法一服务器端修复推荐# 节点143sed-is/\${DS_PROMETHEUS}/Prometheus/g/var/lib/grafana/dashboards/mysql_dashboard.json systemctl restart grafana-server修复后刷新浏览器页面即可看到数据。解决方法二Web 界面手动修复进入 Dashboard 编辑模式点击顶部Settings 齿轮图标点击左侧Variables找到datasource变量将${DS_PROMETHEUS}改为Prometheus点击Update然后点击Save dashboard预防措施在服务器上导入 Dashboard JSON 文件前先执行以下命令替换数据源变量sed-is/\${DS_PROMETHEUS}/Prometheus/g/var/lib/grafana/dashboards/*.json9. Web 界面操作指南9.1 Prometheus 界面访问地址http://192.168.195.143:9090常用功能Graph输入 PromQL 查询并绘图Alerts查看告警规则Status → Targets查看监控目标状态Status → Rules查看 recording rules9.2 Grafana 界面访问地址http://192.168.195.143:3000首次登录用户名admin密码admin首次登录后强制修改密码导入 Dashboard左侧菜单Dashboards → Import输入 Dashboard ID如 7362 为 MySQL Overview或上传 JSON 文件选择 Prometheus 数据源点击 Import创建 Panel进入 Dashboard 编辑模式点击Add panel选择数据源Prometheus输入 PromQL 查询配置图表类型、标题等点击Save10. 验证监控数据10.1 验证 Prometheus 抓取状态# 节点143curl-shttp://127.0.0.1:9090/api/v1/targets|python3-mjson.tool|grep-Ehealth|scrapeUrl预期输出health:up,scrapeUrl:http://192.168.195.141:9104/metricshealth:up,scrapeUrl:http://192.168.195.142:9104/metrics10.2 验证 MySQL 指标# 节点141 或 142curl-shttp://127.0.0.1:9104/metrics|grepmysql_up# 预期输出mysql_up 1curl-shttp://127.0.0.1:9104/metrics|grepmysql_global_status_threads_connected# 预期输出mysql_global_status_threads_connected 数值10.3 验证节点指标# 节点141 或 142curl-shttp://127.0.0.1:9100/metrics|grepnode_cpu_seconds_total|head-5# 预期输出node_cpu_seconds_total{cpu0,modeidle} 数值11. systemd 服务配置11.1 143 上的 Prometheus 服务# 节点143cat/etc/systemd/system/prometheus.serviceEOF [Unit] DescriptionPrometheus Monitoring System Afternetwork-online.target [Service] Userroot Restarton-failure ExecStart/usr/local/prometheus/prometheus \ --config.file/etc/prometheus/prometheus.yml \ --storage.tsdb.path/var/lib/prometheus/ \ --web.listen-address0.0.0.0:9090 [Install] WantedBymulti-user.target EOF11.2 143 上的 Grafana 服务# 节点143systemctl start grafana-server systemctlenablegrafana-server11.3 141、142 上的 Exporter 服务# 141、142 均执行systemctl start mysqld_exporter node_exporter systemctlenablemysqld_exporter node_exporter12. 常见问题12.1 Prometheus 无法抓取目标原因防火墙阻止了 9100/9104 端口。解决systemctl stop firewalld# 或firewall-cmd --add-port9100/tcp--permanentfirewall-cmd --add-port9104/tcp--permanentfirewall-cmd--reload12.2 mysqld_exporter 无法连接 MySQL原因.my.cnf配置错误或 MySQL 未启动。解决# 检查 MySQL 是否运行systemctl status mysqld# 检查 .my.cnf 配置cat/usr/local/mysqld_exporter/.my.cnf# 手动测试连接/usr/local/mysql/bin/mysql-uroot-pRoot123456-eSELECT 1;12.3 Grafana 无法导入 Dashboard原因JSON 文件格式错误或数据源不匹配。解决确认 JSON 文件完整导入时选择正确的 Prometheus 数据源检查 Grafana 版本兼容性12.4 Dashboard 导入后显示 “No data”原因Dashboard JSON 中的${DS_PROMETHEUS}变量未绑定到实际数据源。解决# 节点143# 替换数据源变量sed-is/\${DS_PROMETHEUS}/Prometheus/g/var/lib/grafana/dashboards/mysql_dashboard.json systemctl restart grafana-server验证# 检查替换是否成功grepdatasource/var/lib/grafana/dashboards/mysql_dashboard.json|head-5# 预期输出应包含 datasource: Prometheus 而不是 datasource: ${DS_PROMETHEUS}详见笔记8.4 Dashboard 导入后无数据的排查章节。12.5 复制延迟监控无数据原因mysqld_exporter 需要特定权限才能获取复制延迟。解决-- 在 MySQL 中执行GRANTREPLICATIONCLIENT,PROCESSON*.*TOroot127.0.0.1;FLUSHPRIVILEGES;13. 附录连接信息汇总MySQL 连接# 141 主库/usr/local/mysql/bin/mysql-uroot-S/data/mysql/3306/data/mysql.sock -pRoot123456# 142 从库/usr/local/mysql/bin/mysql-uroot-S/data/mysql/3306/data/mysql.sock -pRoot123456Prometheus WebURLhttp://192.168.195.143:9090无需认证Grafana WebURLhttp://192.168.195.143:3000用户名admin密码admin首次登录后修改Exporter 端点Exporter141142mysqld_exporterhttp://192.168.195.141:9104/metricshttp://192.168.195.142:9104/metricsnode_exporterhttp://192.168.195.141:9100/metricshttp://192.168.195.142:9100/metrics关键文件路径文件路径Prometheus 配置143/etc/prometheus/prometheus.ymlPrometheus 数据143/var/lib/prometheus/Grafana 配置143/etc/grafana/grafana.iniGrafana Dashboards143/var/lib/grafana/dashboards/mysqld_exporter 配置141/142/usr/local/mysqld_exporter/.my.cnfMySQL 配置141/142/etc/my.cnfMySQL 数据141/142/data/mysql/3306/data/文档生成时间2026-07-31Prometheus 版本2.55.1Grafana 版本13.1.1MySQL 版本8.0.35mysqld_exporter 版本0.15.1node_exporter 版本1.8.2操作系统Kylin Linux Advanced Server 10 (ky10)修订记录2026-07-31新增 8.4 章节Dashboard 导入后无数据排查补充 12.4 常见问题Dashboard No data
