Ubuntu MySQL日志分析技巧有哪些

Ubuntu MySQL日志分析技巧

一、日志查看基础技巧

1. 快速定位日志文件路径
   通过MySQL命令查看各类日志的存储位置，避免手动查找：

   • 错误日志：SHOW VARIABLES LIKE 'log_error';（默认路径通常为/var/log/mysql/error.log）
   • 慢查询日志：SHOW VARIABLES LIKE 'slow_query_log_file';
   • 通用查询日志：SHOW VARIABLES LIKE 'general_log_file';
   • 二进制日志：SHOW VARIABLES LIKE 'log_bin';（若开启，路径通常为/var/lib/mysql/mysql-bin.xxxxxx）

2. 实时查看日志内容
   使用tail -f命令实时监控日志更新，快速捕捉异常：

   • 错误日志：sudo tail -f /var/log/mysql/error.log
   • 慢查询日志：sudo tail -f /var/log/mysql/slow.log
   • 通用查询日志：sudo tail -f /var/log/mysql/general.log
     结合grep过滤关键词（如“ERROR”“slow”），缩小查看范围：sudo tail -f /var/log/mysql/error.log | grep "ERROR"

二、专用日志分析工具

1. mysqldumpslow（MySQL官方工具）
   用于统计慢查询日志中的高频或耗时SQL，适合快速定位问题语句：

   • 按执行时间排序（最慢优先），显示前10条：mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
   • 按查询次数排序（高频优先）：mysqldumpslow -s c -t 10 /var/log/mysql/slow.log
   • 筛选包含特定关键词（如“SELECT”）的慢查询：mysqldumpslow -g "SELECT" /var/log/mysql/slow.log
     输出结果会聚合相似语句（如参数替换为“?”），帮助识别SQL模式的性能问题

2. pt-query-digest（Percona Toolkit工具）
   更强大的分析工具，支持慢查询、通用查询、二进制日志等多种日志类型，生成详细报告：

   • 基本用法（分析慢查询日志）：pt-query-digest /var/log/mysql/slow.log
   • 分析实时查询（结合tcpdump捕获流量）：tcpdump -s 65535 -x -n -q -tttt -i any -c 1000 port 3306 | pt-query-digest --type tcpdump
     报告重点关注：
     • Query ID：唯一标识慢查询；
     • Exec time：总执行时间（占比越高影响越大）；
     • Rows examine：扫描行数（远大于Rows sent可能需优化索引）；
     • Query time：平均执行时间（持续高需重点排查）

3. grep/awk/sort/uniq组合（命令行利器）
   无需额外工具，通过基础命令行工具实现快速统计：

   • 统计错误日志中的错误级别分布（如“ERROR”“Warning”）：awk '{print $1}' /var/log/mysql/error.log | sort | uniq -c | sort -nr
   • 提取慢查询日志中的执行时间（第4列），排序并显示前10：awk '{print $4}' /var/log/mysql/slow.log | sort -nr | head -10
   • 统计特定时间段（如2025-10-16）的错误数量：grep "2025-10-16" /var/log/mysql/error.log | wc -l

三、慢查询深度分析与优化

1. 开启慢查询日志
   确保慢查询日志已启用，记录执行时间超过阈值的SQL：

   • 编辑配置文件（/etc/mysql/my.cnf或/etc/mysql/mysql.conf.d/mysqld.cnf），在[mysqld]部分添加：

     slow_query_log = ON slow_query_log_file = /var/log/mysql/slow.log long_query_time = 1 # 阈值（秒），可根据业务调整 log_queries_not_using_indexes = ON # 记录未使用索引的查询（辅助优化） 

   • 重启MySQL生效：sudo systemctl restart mysql
   • 动态开启（无需重启，重启后失效）：

     SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 1; 

2. 结合EXPLAIN分析执行计划
   对慢查询使用EXPLAIN查看执行路径，识别索引或SQL结构问题：

   • 示例：EXPLAIN SELECT * FROM orders WHERE user_id = 123 AND status = 'paid';
   • 关键字段解读：
     • type：连接类型（理想为range、ref或index，避免ALL（全表扫描））；
     • key：实际使用的索引（为空则需添加索引）；
     • rows：预估扫描行数（越大越慢，需通过索引减少）；
     • Extra：出现Using filesort（文件排序）、Using temporary（临时表）需警惕，通常需优化SQL

3. 优化SQL与索引
   根据分析结果调整SQL和索引：

   • 为高频查询条件添加联合索引（如ALTER TABLE orders ADD INDEX idx_user_status (user_id, status);）；
   • 避免SELECT *，只查询必要字段（减少数据传输量）；
   • 优化WHERE子句（避免函数操作，如WHERE DATE(create_time) = '2025-10-16'，可改为WHERE create_time >= '2025-10-16' AND create_time < '2025-10-17'）；
   • 减少子查询、JOIN操作（必要时拆分为多个简单查询）

四、日志管理技巧

1. 日志轮转（防止日志过大）
   使用logrotate工具自动管理日志文件，避免占用过多磁盘空间：

   • 编辑默认配置文件（/etc/logrotate.d/mysql），添加或修改以下内容：

     /var/log/mysql/*.log { daily # 每天轮转 rotate 7 # 保留7天 compress # 压缩旧日志 missingok # 文件不存在不报错 notifempty # 空文件不轮转 create 640 mysql adm # 创建新日志的权限 sharedscripts # 所有日志轮转完成后执行脚本 postrotate systemctl restart mysql > /dev/null 2>&1 || true endscript } 

   • 手动测试轮转：sudo logrotate -vf /etc/logrotate.d/mysql

2. 定期清理旧日志

   • 清理Journal日志（Ubuntu系统日志）：
     • 只保留1周：sudo journalctl --vacuum-time=1w
     • 只保留500MB：sudo journalctl --vacuum-size=500M
   • 清理MySQL日志：手动删除旧日志文件（如/var/log/mysql/slow.log.1.gz），或通过logrotate自动清理