MySQL 可观测性最佳实践

2025-05-22
上海
本文字数：5228 字
阅读完需：约 17 分钟

MySQL 简介

MySQL 是一个广泛使用的开源关系型数据库管理系统（RDBMS），以其高性能、可靠性和易用性而闻名，适用于各种规模的应用，从小型网站到大型企业级系统。

监控 MySQL 指标是维护数据库健康、优化性能和确保数据安全的基础，通过监控查询响应时间和事务处理速度，可以及时发现并解决性能瓶颈。同时，监控 CPU 和内存使用情况有助于合理分配资源，避免过载。在出现故障时，历史指标数据能够快速定位和解决问题。此外，分析数据增长趋势可以帮助提前规划存储扩展，防止空间不足。

采集全系统环境下 MySQL 相关指标信息：

MySQL Global Status 基础数据采集
Schema 相关数据
InnoDB 相关指标
支持自定义查询数据采集

观测云

观测云是一款专为 IT 工程师打造的全链路可观测产品，它集成了基础设施监控、应用程序性能监控和日志管理，为整个技术栈提供实时可观察性。这款产品能够帮助工程师全面了解端到端的用户体验追踪，了解应用内函数的每一次调用，以及全面监控云时代的基础设施。此外，观测云还具备快速发现系统安全风险的能力，为数字化时代提供安全保障。

部署 DataKit

DataKit 是一个开源的、跨平台的数据收集和监控工具，由观测云开发并维护。它旨在帮助用户收集、处理和分析各种数据源，如日志、指标和事件，以便进行有效的监控和故障排查。DataKit 支持多种数据输入和输出格式，可以轻松集成到现有的监控系统中。

登录观测云控制台，在「集成」 - 「DataKit」选择对应安装方式，当前采用 Linux 主机部署 DataKit。

MySQL 配置

前置条件

MySQL 版本 5.7+

创建监控账号

创建监控账号（一般情况，需用 MySQL root 账号登陆才能创建 MySQL 用户），使用 CREATE USER 语句来创建用户。以下是一个示例。

注意：

创建操作，限定了 datakit 这个用户，只能在 MySQL 主机上（localhost）访问 MySQL。
如果需要对 MySQL 进行远程采集，建议将 localhost 替换成 %（表示 DataKit 可以在任意机器上访问 MySQL），也可用指定的 DataKit 安装机器地址。

该语句将在MySQL数据库中创建一个名为'datakit'的用户，并为该用户设置密码为'<UNIQUEPASSWORD>'CREATE USER 'datakit'@'localhost' IDENTIFIED BY '<UNIQUEPASSWORD>';-- MySQL 8.0+以上可使用caching_sha2_password 方法创建CREATE USER 'datakit'@'localhost' IDENTIFIED WITH caching_sha2_password by '<UNIQUEPASSWORD>';

复制代码

为监控账号授权

注意：

授权操作，限定了 datakit 这个用户，只能在 MySQL 主机上（localhost）访问 MySQL。
如果需要对 MySQL 进行远程采集，建议将 localhost 替换成 %（表示 DataKit 可以在任意机器上访问 MySQL），也可用指定的 DataKit 安装机器地址。

GRANT PROCESS ON *.* TO 'datakit'@'localhost';GRANT SELECT ON *.* TO 'datakit'@'localhost';show databases like 'performance_schema';GRANT SELECT ON performance_schema.* TO 'datakit'@'localhost';GRANT SELECT ON mysql.user TO 'datakit'@'localhost';GRANT replication client on *.*  to 'datakit'@'localhost';
#性能指标采集需CREATE SCHEMA IF NOT EXISTS datakit;GRANT EXECUTE ON datakit.* to datakit@'%';GRANT CREATE TEMPORARY TABLES ON datakit.* TO datakit@'%';

-- MySQL 5.6 & 5.7GRANT REPLICATION CLIENT ON *.* TO datakit@'%' WITH MAX_USER_CONNECTIONS 5;
-- MySQL >= 8.0ALTER USER datakit@'%' WITH MAX_USER_CONNECTIONS 5;GRANT REPLICATION CLIENT ON *.* TO datakit@'%';GRANT PROCESS ON *.* TO datakit@'%';

复制代码

Tips：

如用 localhost 时发现采集器有如下报错，需要将上述步骤的 localhost 换成 ::1Error 1045: Access denied for user 'datakit'@'localhost' (using password: YES)
另外，也需要注意下 MySQL 5.7 和 8.0 版本，授权上有所区别。

DataKit 采集器配置

DataKit 内置了 MySQL 采集器，采集 MySQL 相关数据。

进入 datakit 安装目录下的 conf.d/db 目录，复制 mysql.conf.sample 并命名为 mysql.conf

cp mysql.conf.sample mysql.conf

复制代码

调整 mysql.conf

[[inputs.mysql]]  host = "localhost"  user = "datakit"  pass = "<PASS>"  port = 3306    [inputs.mysql.log]    # files = ["/var/log/mysql/*.log"]    ## grok pipeline script path    pipeline = "mysql.p"
  ## Config dbm metric   [inputs.mysql.dbm_metric]    enabled = true
  ## Config dbm sample   [inputs.mysql.dbm_sample]    enabled = true  
  ## Config dbm activity  [inputs.mysql.dbm_activity]    enabled = true  
    # 开启数据库性能指标采集    dbm = true        ...        # 监控指标配置    [inputs.mysql.dbm_metric]      enabled = true        # 监控采样配置    [inputs.mysql.dbm_sample]      enabled = true        # 等待事件采集    [inputs.mysql.dbm_activity]      enabled = true       ...
  [inputs.mysql.tags]    # some_tag = "some_value"    # more_tag = "some_other_value"

复制代码

重启 DataKit

datakit service -R

复制代码

高级配置（采集更多性能指标）

Binlog 开启

统计 Binlog 大小，需要开启 MySQL 对应 Binlog 功能（默认情况下，MySQL Binlog 默认是不开启的）。

1、检查状态

-- ON: 开启/OFF: 关闭SHOW VARIABLES LIKE 'log_bin';

复制代码

2、开启 Binlog 的步骤

开启 MySQL 的 Binlog 功能主要涉及修改 MySQL 的配置文件并重启服务。

1）编辑 MySQL 配置文件：找到 MySQL 的配置文件 my.cnf 或 my.ini，通常位于 /etc/mysql 目录下，如果找不到可以通过命令 find / -name "my.cnf" 进行查找。

2）添加 Binlog 配置：在配置文件的 [mysqld] 部分添加以下配置：

log_bin=ON：开启 Binlog 日志。
log_bin_basename=/var/lib/mysql/mysql-bin：指定 Binlog 日志的基本文件名。
log_bin_index=/var/lib/mysql/mysql-bin.index：指定 Binlog 文件的索引文件。
server-id=1：为 MySQL 服务分配一个唯一的 ID，用于在复制集群中标识服务器 1。

3）简单配置方式：也可以只添加一行配置 log-bin=/var/lib/mysql/mysql-bin，MySQL 会自动设置 log_bin 为 ON 状态，并自动设置 log_bin_index 文件。

4）对于 MySQL 5.7 及以上版本：如果使用的是 5.7 及以上版本，在添加上述配置后，还需要重启 MySQL 服务，否则可能会报错。

5）重启 MySQL 服务：配置完成后，需要重启 MySQL 服务以使配置生效。可以使用命令 service mysqld restart 进行重启。

6）验证 Binlog 是否开启：通过登录 MySQL 并执行 SHOW VARIABLES LIKE '%log_bin%'; 来检查 Binlog 是否已经开启。

7）查看 Binlog 日志：可以通过 SHOW MASTER LOGS; 查看所有 Binlog 日志列表，或者使用 mysqlbinlog 工具查看 Binlog 内容。

3、数据库性能指标采集

修改配置文件（如 mysql.conf），开启 MySQL Performance Schema，并配置相关参数。

[mysqld]performance_schema = onmax_digest_length = 4096performance_schema_max_digest_length = 4096performance_schema_max_sql_text_length = 4096performance-schema-consumer-events-statements-current = onperformance-schema-consumer-events-waits-current = onperformance-schema-consumer-events-statements-history-long = onperformance-schema-consumer-events-statements-history = on

复制代码

4、创建存储过程 explain_statement，用于获取 SQL 执行计划

DELIMITER $$CREATE PROCEDURE datakit.explain_statement(IN query TEXT)    SQL SECURITY DEFINERBEGIN    SET @explain := CONCAT('EXPLAIN FORMAT=json ', query);    PREPARE stmt FROM @explain;    EXECUTE stmt;    DEALLOCATE PREPARE stmt;END $$DELIMITER ;

复制代码

5、consumers 配置

---------------方式1-------------------：
DELIMITER $$CREATE PROCEDURE datakit.enable_events_statements_consumers()    SQL SECURITY DEFINERBEGIN    UPDATE performance_schema.setup_consumers SET enabled='YES' WHERE name LIKE 'events_statements_%';    UPDATE performance_schema.setup_consumers SET enabled='YES' WHERE name = 'events_waits_current';END $$DELIMITER ;
GRANT EXECUTE ON PROCEDURE datakit.enable_events_statements_consumers TO datakit@'%';


---------------方式2-------------------：UPDATE performance_schema.setup_consumers SET enabled='YES' WHERE name LIKE 'events_statements_%';UPDATE performance_schema.setup_consumers SET enabled='YES' WHERE name = 'events_waits_current';

复制代码

主从复制指标采集

1、前提条件

采集主从复制 mysql_replication 指标的前提是开启主从复制，mysql_replication 指标都是由从数据库采集的。

确认主从复制环境是否正常可以在从数据库输入：

SHOW SLAVE STATUS;

复制代码

Replica_IO_Running、Replica_SQL_Running 的值均为 Yes，说明主从复制环境状态正常。

2、count_transactions_in_queue

将【组复制插件】添加到服务器在启动时加载的插件列表（group_replication 从 MySQL 版本 5.7.17 开始支持）。在从数据库的配置文件 /etc/my.cnf 中，添加一行。

plugin_load_add ='group_replication.so'

复制代码

通过 show plugins; 确认组复制插件已安装。

show plugins

复制代码

3、DataKit mysql 采集器配置

新增以下配置内容：

[[inputs.mysql]]
## Set replication to true to collect replication metricsreplication = true## Set group_replication to true to collect group replication metricsgroup_replication = true  ...

复制代码

关键指标

更多指标描述，参考观测云官方文档。

日志采集

MySQL 运行日志

如需采集 MySQL 的日志，将配置中 log 相关的配置打开，如需要开启 MySQL 慢查询日志，需要开启慢查询日志，在 MySQL 中执行以下语句：

SET GLOBAL slow_query_log = 'ON';-- 未使用索引的查询也认为是一个可能的慢查询set global log_queries_not_using_indexes = 'ON';

复制代码

注意：在使用日志采集时，需要将 DataKit 安装在 MySQL 服务同一台主机中，或使用其它方式将日志挂载到 DataKit 所在机器。

MySQL 日志分为普通日志和慢日志两种。

MySQL 普通日志

日志原文：

2017-12-29T12:33:33.095243Z         2 Query     SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE CREATE_OPTIONS LIKE '%partitioned%';

复制代码

切割后的字段列表如下：

MySQL 慢查询日志

日志原文：

# Time: 2019-11-27T10:43:13.460744Z# User@Host: root[root] @ localhost [1.2.3.4]  Id:    35# Query_time: 0.214922  Lock_time: 0.000184 Rows_sent: 248832  Rows_examined: 72# Thread_id: 55   Killed: 0  Errno: 0# Bytes_sent: 123456   Bytes_received: 0SET timestamp=1574851393;SELECT * FROM fruit f1, fruit f2, fruit f3, fruit f4, fruit f5

复制代码

切割后的字段列表如下：

如果值是 OFF，请参考阿里云相关文档进行开启。

场景视图

登录观测云控制台，点击「场景」 -「新建仪表板」，输入 “mysql”，选择 “mysql 监控视图”，点击 “确定” 即可添加视图。

MySQL 监控视图

MySQL DBM 监控视图

MySQL Activity 监控视图

MySQL Slow Query 监控视图

监控器（告警）

MySQL 每秒立即获得锁的数过高告警

MySQL 每秒获取锁数量过高会导致性能瓶颈、死锁风险、事务延迟及业务中断，本质是资源争用，需通过优化事务逻辑、降低锁粒度、调整隔离级别和分布式架构等手段解决。

MySQL 慢查询数量过高告警

MySQL 慢查询数量过高会导致数据库性能瓶颈、资源耗尽及业务响应延迟，通常由索引缺失或低效查询引起，需通过优化 SQL 语句、添加索引或调整执行计划解决。

MySQL 由于客户端没有正确关闭连接而中止的连接数过高告警

MySQL 因客户端未正确关闭连接导致的中止连接数过高告警具有显著必要性，其直接危害包括：资源耗竭（占用内存、线程及文件描述符）、性能下降（连接握手与回收开销增大）、稳定性风险（连接泄漏引发服务崩溃）及安全隐患（潜在攻击者利用残留连接）。该告警可帮助及时识别代码缺陷、网络异常或连接池配置问题，通过优化客户端连接释放逻辑、调整 wait_timeout/interactive_timeout 参数、引入连接池管理等方式，避免数据库因“连接雪崩”陷入不可用状态，保障服务高可用性与资源高效利用。

总结

MySQL 指标监控对于维护数据库的健康和性能至关重要。它允许管理员实时跟踪关键性能指标，如查询响应时间、连接数、缓冲池使用情况和磁盘 I/O 活动。通过这些数据，可以识别和解决性能瓶颈，预测资源需求，优化数据库配置，以及确保数据的完整性和安全性。此外，监控还可以帮助检测和防范潜在的攻击，通过观测云设置告警阈值快速响应异常活动，减少系统故障时间，从而提高数据库的可靠性和业务连续性。总之，MySQL 指标监控是数据库管理的核心部分，对于保障企业数据资产的稳定性和高效性起着至关重要的作用。

发布于: 刚刚阅读数: 3

观测云

关注

还未添加个人签名 2021-02-08 加入

云时代的系统可观测平台

发布

暂无评论

创作场景

MySQL 可观测性最佳实践

MySQL 简介

观测云

部署 DataKit

MySQL 配置

前置条件

创建监控账号

为监控账号授权

DataKit 采集器配置

高级配置（采集更多性能指标）

Binlog 开启

主从复制指标采集

关键指标

日志采集

MySQL 运行日志

MySQL 普通日志

MySQL 慢查询日志

场景视图

MySQL 监控视图

MySQL DBM 监控视图

MySQL Activity 监控视图

MySQL Slow Query 监控视图

监控器（告警）

MySQL 每秒立即获得锁的数过高告警

MySQL 慢查询数量过高告警

MySQL 由于客户端没有正确关闭连接而中止的连接数过高告警

总结

观测云

评论