当前位置：首页 > news >正文

MySQL 可观测性最佳实践

news 来源：原创 2025/6/29 11:59:51

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.7
GRANT REPLICATION CLIENT ON *.* TO datakit@'%' WITH MAX_USER_CONNECTIONS 5;-- MySQL >= 8.0
ALTER USER datakit@'%' WITH MAX_USER_CONNECTIONS 5;
GRANT REPLICATION CLIENT ON *.* TO datakit@'%';
GRANT PROCESS ON *.* TO datakit@'%';

Tips：

如用 localhost 时发现采集器有如下报错，需要将上述步骤的 localhost 换成 ::1
Error 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 pathpipeline = "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 = on
max_digest_length = 4096
performance_schema_max_digest_length = 4096
performance_schema_max_sql_text_length = 4096
performance-schema-consumer-events-statements-current = on
performance-schema-consumer-events-waits-current = on
performance-schema-consumer-events-statements-history-long = on
performance-schema-consumer-events-statements-history = on

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

DELIMITER $$
CREATE PROCEDURE datakit.explain_statement(IN query TEXT)SQL SECURITY DEFINER
BEGINSET @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 DEFINER
BEGINUPDATE 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 metrics
replication = true
## Set group_replication to true to collect group replication metrics
group_replication = true  
...

关键指标

指标	描述	类型	单位
Aborted_clients	因客户端未正常关闭连接而导致连接被终止的次数	int	count
Aborted_connects	连接到MySQL服务器失败的尝试次数	int	count
Binlog_cache_disk_use	使用临时二进制日志缓存但超出binlog_cache_size值，并使用临时文件存储事务语句的事务数量	int	B
Binlog_cache_use	使用二进制日志缓存的事务数量	int	B
Binlog_space_usage_bytes	总二进制日志文件大小	int	B
Bytes_received	从所有客户端接收的字节数	int	B
Bytes_sent	向所有客户端发送的字节数	int	B
Com_commit	执行提交语句的次数	int	count
Com_delete	执行删除语句的次数	int	count
Com_delete_multi	执行多表删除语句的次数	int	count
Com_insert	执行插入语句的次数	int	count
Com_insert_select	执行插入选择语句的次数	int	count
Com_load	执行加载语句的次数	int	count
Com_replace	执行替换语句的次数	int	count
Com_replace_select	执行替换选择语句的次数	int	count
Auto_Position	如果使用自动定位则为1，否则为0	bool	count
Connect_Retry	连接重试之间的时间间隔（默认为60秒）。可以通过CHANGE MASTER TO语句设置	int	count
Exec_Master_Log_Pos	SQL线程已读取并执行的当前源二进制日志文件中的位置，标志着下一个要处理的事务或事件的开始	int	count
Last_Errno	这些列是Last_SQL_Errno的别名	int	count
Last_IO_Errno	导致I/O线程停止的最近一次错误的错误编号。错误编号为0且消息为空字符串表示“无错误”	int	count
Last_SQL_Errno	导致SQL线程停止的最近一次错误的错误编号。错误编号为0且消息为空字符串表示“无错误”	int	count
Master_Server_Id	源的server_id值	int	count
Relay_Log_Space	所有现有中继日志文件的总大小	int	count
Replicas_connected	连接到复制源的副本数量	int	count
SQL_Delay	副本必须落后于源的秒数	int	count
Seconds_Behind_Master	主服务器和从服务器之间的延迟秒数	int	count

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

日志采集

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%';

切割后的字段列表如下：

字段名	字段值	说明
status	Warning	日志级别
msg	System table 'plugin' is expected to be transactional.	日志内容
time	1514520249954078000	纳秒时间戳（作为行协议时间）

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: 0
SET timestamp=1574851393;
SELECT * FROM fruit f1, fruit f2, fruit f3, fruit f4, fruit f5

切割后的字段列表如下：

字段名	字段值	说明
bytes_sent	123456	发送字节数
db_host	localhost	hostname
db_ip	1.2.3.4	IP
db_slow_statement	SET timestamp=1574851393;\nSELECT * FROM fruit f1, fruit f2, fruit f3, fruit f4, fruit f5	慢查询 SQL
db_user	root[root]	用户
lock_time	0.000184	锁时间
query_id	35	查询 ID
query_time	0.2l4922	SQL 执行所消耗的时间
rows_examined	72	为了返回查询的数据所读取的行数
rows_sent	248832	查询返回的行数
thread_id	55	线程 ID
time	1514520249954078000	纳秒时间戳（作为行协议时间）

如果值是 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 指标监控是数据库管理的核心部分，对于保障企业数据资产的稳定性和高效性起着至关重要的作用。