MySQL 的监控与日志

最新推荐文章于 2025-05-06 22:27:05 发布

zjw541806

最新推荐文章于 2025-05-06 22:27:05 发布

阅读量562

点赞数 21

分类专栏：数据库文章标签：数据库 mysql

本文链接：https://blog.csdn.net/zjw541806/article/details/147731246

版权

数据库专栏收录该内容

4 篇文章

订阅专栏

MySQL 的监控与日志是保障数据库稳定运行、快速定位问题的关键工具。以下从监控指标、日志类型、分析工具三个维度深入讲解，并结合实际案例说明如何应用。

一、监控体系：核心指标与工具

1. 监控维度与指标

(1) 系统层监控

CPU 使用率：监控 MySQL 进程的 CPU 占用。
bash

复制->top -p $(pgrep mysqld | tr '\n' ',' | sed 's/,$//')
```
top -p $(pgrep mysqld | tr '\n' ',' | sed 's/,$//')
```
异常场景：CPU 持续高于 80% 可能因全表扫描、复杂 JOIN 或锁竞争导致。
内存使用：关注 InnoDB 缓冲池命中率。
sql

复制->SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
-- 计算命中率：1 - (innodb_buffer_pool_reads / innodb_buffer_pool_read_requests)
```
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
-- 计算命中率：1 - (innodb_buffer_pool_reads / innodb_buffer_pool_read_requests)
```
磁盘 I/O：监控磁盘读写延迟与吞吐量。
bash

复制->iostat -x 1 # 查看 %util（磁盘繁忙度）、await（I/O 平均等待时间）
```
iostat -x 1  # 查看 %util（磁盘繁忙度）、await（I/O 平均等待时间）
```

(2) 存储引擎层（InnoDB）

锁等待：监控锁冲突和等待时间。
sql

复制->SHOW ENGINE INNODB STATUS\G -- 查看 `TRANSACTIONS` 部分的锁信息
```
SHOW ENGINE INNODB STATUS\G  -- 查看 `TRANSACTIONS` 部分的锁信息
```
案例：若发现 LATEST DETECTED DEADLOCK 日志，需优化事务隔离级别或减少长事务。
缓冲池状态：监控脏页刷新与命中率。
sql

复制->SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_dirty';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_flushes';
```
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_dirty';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_flushes';
```

(3) SQL 层

QPS（每秒查询数）：
sql

复制->SHOW GLOBAL STATUS LIKE 'Queries';
-- QPS = (Queries_current - Queries_previous) / 时间间隔
```
SHOW GLOBAL STATUS LIKE 'Queries';
-- QPS = (Queries_current - Queries_previous) / 时间间隔
```
慢查询数量：
sql

复制->SHOW GLOBAL STATUS LIKE 'Slow_queries';
```
SHOW GLOBAL STATUS LIKE 'Slow_queries';
```
临时表与文件排序：
sql

复制->SHOW GLOBAL STATUS LIKE 'Created_tmp%tables';
SHOW GLOBAL STATUS LIKE 'Sort_merge_passes';
```
SHOW GLOBAL STATUS LIKE 'Created_tmp%tables';
SHOW GLOBAL STATUS LIKE 'Sort_merge_passes';
```

2. 监控工具

(1) Performance Schema

启用与查询：
sql

复制->UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES';
SELECT EVENT_NAME, COUNT_STAR FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC LIMIT 10; -- 查看最耗时的 SQL
```
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES';
SELECT EVENT_NAME, COUNT_STAR FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;  -- 查看最耗时的 SQL
```

(2) Prometheus + Grafana

Exporter 配置：使用 mysqld_exporter 采集指标。
Dashboard 示例：监控 QPS、连接数、缓冲池命中率。
https://example.com/dashboard.png

(3) Percona Monitoring and Management (PMM)

功能：可视化慢查询、锁等待、InnoDB 状态。
操作示例：通过 Top SQL 页面定位低效查询。

二、日志类型与分析实战

1. 错误日志（Error Log）

配置：
ini

复制-># my.cnf
[mysqld]
log_error = /var/log/mysql/error.log
```
# my.cnf
[mysqld]
log_error = /var/log/mysql/error.log
```
关键内容：
- 启动失败原因（如端口冲突、权限不足）。
- 死锁详情（LATEST DETECTED DEADLOCK）。
- 主从复制错误（Slave_SQL_Running: No）。

案例：
错误日志中出现 ERROR 1040 (08004): Too many connections，需调整 max_connections 或优化连接池配置。

2. 慢查询日志（Slow Query Log）

启用与配置：
sql

复制->SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2; -- 记录超过 2 秒的查询
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
```
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;  -- 记录超过 2 秒的查询
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
```
分析工具：
- mysqldumpslow：统计高频慢查询。
  bash
  
  复制->mysqldumpslow -s t /var/log/mysql/slow.log # 按耗时排序
```
mysqldumpslow -s t /var/log/mysql/slow.log  # 按耗时排序
```
- pt-query-digest：生成详细报告。
  bash
  
  复制->pt-query-digest /var/log/mysql/slow.log > analysis.txt
```
pt-query-digest /var/log/mysql/slow.log > analysis.txt
```

案例：
通过 pt-query-digest 发现某 SQL 执行时间过长，使用 EXPLAIN 分析发现缺少索引：

sql

复制->EXPLAIN SELECT * FROM orders WHERE user_id = 100 AND status = 'pending';
-- 发现 type=ALL（全表扫描），添加索引：ALTER TABLE orders ADD INDEX (user_id, status);

EXPLAIN SELECT * FROM orders WHERE user_id = 100 AND status = 'pending';
-- 发现 type=ALL（全表扫描），添加索引：ALTER TABLE orders ADD INDEX (user_id, status);

3. 二进制日志（Binary Log）

作用：主从复制、数据恢复。
查看与解析：
bash

复制->mysqlbinlog --base64-output=decode-rows -v binlog.000001
```
mysqlbinlog --base64-output=decode-rows -v binlog.000001
```
恢复数据：
bash

复制->mysqlbinlog binlog.000001 | mysql -u root -p
```
mysqlbinlog binlog.000001 | mysql -u root -p
```

案例：
主从同步中断后，通过 mysqlbinlog 找到断点位置，重新同步数据。

4. 通用查询日志（General Query Log）

配置（慎用，生产环境关闭）：
sql

复制->SET GLOBAL general_log = 'ON';
SET GLOBAL general_log_file = '/var/log/mysql/general.log';
```
SET GLOBAL general_log = 'ON';
SET GLOBAL general_log_file = '/var/log/mysql/general.log';
```
用途：调试 SQL 执行流程。

三、监控与日志联动实践

1. 自动化报警

Prometheus Alertmanager 配置示例（监控连接数）：
yaml

复制->- alert: MySQLHighConnections
expr: mysql_global_status_threads_connected / mysql_global_status_max_connections > 0.8
for: 5m
labels:
severity: critical
annotations:
summary: "MySQL 连接数过高"
description: "当前连接数 {{ $value }}，超过阈值 80%"
```
- alert: MySQLHighConnections
  expr: mysql_global_status_threads_connected / mysql_global_status_max_connections > 0.8
  for: 5m
  labels:
    severity: critical
  annotations:
    summary: "MySQL 连接数过高"
    description: "当前连接数 {{ $value }}，超过阈值 80%"
```

2. 日志驱动告警

ELK 栈：通过 Filebeat 收集日志，Logstash 过滤关键词（如 ERROR），Kibana 设置告警规则。

四、关键运维实践

日志轮转：避免日志文件过大。
bash

复制-># logrotate 配置示例
/var/log/mysql/*.log {
daily
rotate 7
missingok
compress
postrotate
systemctl reload mysqld
endscript
}
```
# logrotate 配置示例
/var/log/mysql/*.log {
  daily
  rotate 7
  missingok
  compress
  postrotate
    systemctl reload mysqld
  endscript
}
```
归档历史数据：定期清理二进制日志。
sql

复制->PURGE BINARY LOGS BEFORE '2023-01-01 00:00:00';
```
PURGE BINARY LOGS BEFORE '2023-01-01 00:00:00';
```
基线对比：通过长期监控数据建立性能基线，快速识别异常波动。