MySQL 可观测性最佳实践

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 安装机器地点。
  

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

复制代码

为监控账号授权

留意：

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

1. GRANT PROCESS ON *.* TO 'datakit'@'localhost';
2. GRANT SELECT ON *.* TO 'datakit'@'localhost';
3. show databases like 'performance_schema';
4. GRANT SELECT ON performance_schema.* TO 'datakit'@'localhost';
5. GRANT SELECT ON mysql.user TO 'datakit'@'localhost';
6. GRANT replication client on *.*  to 'datakit'@'localhost';
8. #性能指标采集需
9. CREATE SCHEMA IF NOT EXISTS datakit;
10. GRANT EXECUTE ON datakit.* to datakit@'%';
11. GRANT CREATE TEMPORARY TABLES ON datakit.* TO datakit@'%';
13. -- MySQL 5.6 & 5.7
14. GRANT REPLICATION CLIENT ON *.* TO datakit@'%' WITH MAX_USER_CONNECTIONS 5;
16. -- MySQL >= 8.0
17. ALTER USER datakit@'%' WITH MAX_USER_CONNECTIONS 5;
18. GRANT REPLICATION CLIENT ON *.* TO datakit@'%';
19. 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
  

1. cp mysql.conf.sample mysql.conf

复制代码

• 调整 mysql.conf
  

1. [[inputs.mysql]]
2.   host = "localhost"
3.   user = "datakit"
4.   pass = "<PASS>"
5.   port = 3306
6.   
7.   [inputs.mysql.log]
8.     # files = ["/var/log/mysql/*.log"]
9.     ## grok pipeline script path
10.     pipeline = "mysql.p"
12.   ## Config dbm metric
13.   [inputs.mysql.dbm_metric]
14.     enabled = true
16.   ## Config dbm sample
17.   [inputs.mysql.dbm_sample]
18.     enabled = true  
20.   ## Config dbm activity
21.   [inputs.mysql.dbm_activity]
22.     enabled = true  
24.     # 开启数据库性能指标采集
25.     dbm = true
26.    
27.     ...
28.    
29.     # 监控指标配置
30.     [inputs.mysql.dbm_metric]
31.       enabled = true
32.    
33.     # 监控采样配置
34.     [inputs.mysql.dbm_sample]
35.       enabled = true
36.    
37.     # 等待事件采集
38.     [inputs.mysql.dbm_activity]
39.       enabled = true   
40.     ...
42.   [inputs.mysql.tags]
43.     # some_tag = "some_value"
44.     # more_tag = "some_other_value"

复制代码

• 重启 DataKit
  

1. datakit service -R

复制代码

高级配置（收罗更多性能指标）

Binlog 开启

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

1. -- ON: 开启/OFF: 关闭
2. 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，并配置相关参数。

1. [mysqld]
2. performance_schema = on
3. max_digest_length = 4096
4. performance_schema_max_digest_length = 4096
5. performance_schema_max_sql_text_length = 4096
6. performance-schema-consumer-events-statements-current = on
7. performance-schema-consumer-events-waits-current = on
8. performance-schema-consumer-events-statements-history-long = on
9. performance-schema-consumer-events-statements-history = on

复制代码

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

1. DELIMITER $$
2. CREATE PROCEDURE datakit.explain_statement(IN query TEXT)
3.     SQL SECURITY DEFINER
4. BEGIN
5.     SET @explain := CONCAT('EXPLAIN FORMAT=json ', query);
6.     PREPARE stmt FROM @explain;
7.     EXECUTE stmt;
8.     DEALLOCATE PREPARE stmt;
9. END $$
10. DELIMITER ;

复制代码

5、consumers 配置

1. ---------------方式1-------------------：
3. DELIMITER $$
4. CREATE PROCEDURE datakit.enable_events_statements_consumers()
5.     SQL SECURITY DEFINER
6. BEGIN
7.     UPDATE performance_schema.setup_consumers SET enabled='YES' WHERE name LIKE 'events_statements_%';
8.     UPDATE performance_schema.setup_consumers SET enabled='YES' WHERE name = 'events_waits_current';
9. END $$
10. DELIMITER ;
12. GRANT EXECUTE ON PROCEDURE datakit.enable_events_statements_consumers TO datakit@'%';
14. ---------------方式2-------------------：
15. UPDATE performance_schema.setup_consumers SET enabled='YES' WHERE name LIKE 'events_statements_%';
16. UPDATE performance_schema.setup_consumers SET enabled='YES' WHERE name = 'events_waits_current';

复制代码

主从复制指标收罗

1、条件条件
收罗主从复制 mysql_replication 指标的条件是开启主从复制，mysql_replication 指标都是由从数据库收罗的。
确认主从复制情况是否正常可以在从数据库输入：

1. SHOW SLAVE STATUS;

复制代码

Replica_IO_Running、Replica_SQL_Running 的值均为 Yes，说明主从复制情况状态正常。
2、count_transactions_in_queue
将【组复制插件】添加到服务器在启动时加载的插件列表（group_replication 从 MySQL 版本 5.7.17 开始支持）。在从数据库的配置文件 /etc/my.cnf 中，添加一行。

1. plugin_load_add ='group_replication.so'

复制代码

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

1. show plugins

复制代码

3、DataKit mysql 收罗器配置
新增以下配置内容：

1. [[inputs.mysql]]
3. ## Set replication to true to collect replication metrics
4. replication = true
5. ## Set group_replication to true to collect group replication metrics
6. group_replication = true  
7. ...

复制代码

关键指标

指标描述类型单位Aborted_clients因客户端未正常关闭连接而导致连接被停止的次数intcountAborted_connects连接到MySQL服务器失败的尝试次数intcountBinlog_cache_disk_use利用暂时二进制日志缓存但超出binlog_cache_size值，并利用暂时文件存储事件语句的事件数目intBBinlog_cache_use利用二进制日志缓存的事件数目intBBinlog_space_usage_bytes总二进制日志文件大小intBBytes_received从所有客户端接收的字节数intBBytes_sent向所有客户端发送的字节数intBCom_commit实行提交语句的次数intcountCom_delete实行删除语句的次数intcountCom_delete_multi实行多表删除语句的次数intcountCom_insert实行插入语句的次数intcountCom_insert_select实行插入选择语句的次数intcountCom_load实行加载语句的次数intcountCom_replace实行替换语句的次数intcountCom_replace_select实行替换选择语句的次数intcountAuto_Position如果利用主动定位则为1，否则为0boolcountConnect_Retry连接重试之间的时间隔断（默以为60秒）。可以通过CHANGE MASTER TO语句设置intcountExec_Master_Log_PosSQL线程已读取并实行的当前源二进制日志文件中的位置，标志着下一个要处置处罚的事件或事件的开始intcountLast_Errno这些列是Last_SQL_Errno的别名intcountLast_IO_Errno导致I/O线程停止的迩来一次错误的错误编号。错误编号为0且消息为空字符串表示“无错误”intcountLast_SQL_Errno导致SQL线程停止的迩来一次错误的错误编号。错误编号为0且消息为空字符串表示“无错误”intcountMaster_Server_Id源的server_id值intcountRelay_Log_Space所有现有中继日志文件的总大小intcountReplicas_connected连接到复制源的副本数目intcountSQL_Delay副本必须落后于源的秒数intcountSeconds_Behind_Master主服务器和从服务器之间的延迟秒数intcount 更多指标描述，参考观测云官方文档。
日志收罗

MySQL 运行日志

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

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

复制代码

留意：在利用日志收罗时，需要将 DataKit 安装在 MySQL 服务同一台主机中，或利用其它方式将日志挂载到 DataKit 所在机器。
MySQL 日志分为普通日志和慢日志两种。
MySQL 普通日志

日志原文：

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

复制代码

切割后的字段列表如下：
字段名字段值说明statusWarning日志级别msgSystem table 'plugin' is expected to be transactional.日志内容time1514520249954078000纳秒时间戳（作为行协议时间） MySQL 慢查询日志

日志原文：

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

复制代码

切割后的字段列表如下：
字段名字段值说明bytes_sent123456发送字节数db_hostlocalhosthostnamedb_ip1.2.3.4IPdb_slow_statementSET timestamp=1574851393;\nSELECT * FROM fruit f1, fruit f2, fruit f3, fruit f4, fruit f5慢查询 SQLdb_userroot[root]用户lock_time0.000184锁时间query_id35查询 IDquery_time0.2l4922SQL 实行所斲丧的时间rows_examined72为了返回查询的数据所读取的行数rows_sent248832查询返回的行数thread_id55线程 IDtime1514520249954078000纳秒时间戳（作为行协议时间） 如果值是 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 指标监控是数据库管理的核心部分，对于保障企业数据资产的稳定性和高效性起着至关紧张的作用。