Ubuntu MariaDB日志分析技巧有哪些

Ubuntu MariaDB日志分析实用技巧

一 日志类型与定位

• 错误日志：常见路径为**/var/log/mysql/error.log或/var/log/mariadb/error.log**；若未找到，可查看MariaDB配置文件（如**/etc/mysql/my.cnf或/etc/mysql/mariadb.conf.d/*.cnf**）中的log_error项。也可通过systemd查看服务日志：journalctl -u mariadb。
• 通用查询日志：用于记录所有SQL，默认多未开启；开启后可通过general_log与general_log_file配置，或写入表mysql.general_log。
• 慢查询日志：定位性能瓶颈的关键，建议开启；通过slow_query_log、slow_query_log_file、long_query_time等参数配置。
• 二进制日志（Binlog）：用于复制与时间点恢复，开启后路径由log_bin指定（如**/var/log/mysql/binlog**）。
• 审计日志：如需记录连接、查询、表级操作，可加载server_audit插件并配置事件与轮转参数。

二 快速检索与过滤

• 实时查看错误日志：tail -f /var/log/mysql/error.log；按时间段查看服务日志：journalctl -u mariadb --since “2025-12-24 00:00:00” --until “2025-12-24 12:00:00”。
• 关键字定位：在错误日志中检索问题线索，如grep -i “error|failed|access denied” /var/log/mysql/error.log；也可配合awk按日期统计条目数。
• 通用/慢查询日志检索：对文件型日志使用grep/awk/sed进行模式匹配与时间窗口过滤；对写入表的日志（如general_log、slow_log）可用SQL查询，如：
  • SELECT * FROM mysql.general_log WHERE event_time >= NOW() - INTERVAL 1 HOUR;
  • SELECT * FROM mysql.slow_log WHERE start_time >= NOW() - INTERVAL 1 HOUR ORDER BY query_time DESC LIMIT 100;
• 审计日志检索：审计插件输出到文件时，使用grep/awk按用户、库表、事件类型过滤，便于追踪敏感操作。

三 慢查询分析与优化

• 启用与动态调整（示例阈值可按业务调整）：
  • SET GLOBAL slow_query_log = 1;
  • SET GLOBAL long_query_time = 1;
  • SET GLOBAL log_queries_not_using_indexes = 1;
  • SET GLOBAL min_examined_row_limit = 1000;
  • 输出方式：SET GLOBAL log_output = ‘FILE,TABLE’;（同时写入文件与表便于多维度分析）。
• 内置工具：
  • 按总耗时取Top N：mysqldumpslow -s t -t 10 /var/log/mysql/slow.log；
  • 按平均耗时：mysqldumpslow -s at -t 10 /var/log/mysql/slow.log；
  • 仅看SELECT：mysqldumpslow -g “SELECT” /var/log/mysql/slow.log。
• 高级分析：使用pt-query-digest生成报告，识别最耗时的SQL模板、总耗时、平均耗时、扫描/返回行数等，便于制定索引与SQL改写策略：
  • pt-query-digest /var/log/mysql/slow.log > slow_report.txt。
• 判定与优化要点：关注Query_time、Lock_time、Rows_sent/Rows_examined；经验上以Rows_sent/Rows_examined < 0.1为良好索引命中目标；对Top SQL结合EXPLAIN检查执行计划并添加合适索引或改写SQL。

四 审计与二进制日志分析

• 审计日志：
  • 确认插件目录：SHOW GLOBAL VARIABLES LIKE ‘plugin_dir’;
  • 加载插件并配置（在配置文件中）：
    • plugin_load_add = server_audit
    • server_audit_events = connect,query,table
    • server_audit_file_rotate_size = 1000000
    • server_audit_file_rotations = 5
  • 重启生效：systemctl restart mariadb；审计日志可按时间、用户、库表进行检索，用于合规与溯源。
• 二进制日志（Binlog）：
  • 开启与路径：log_bin = /var/log/mysql/binlog；用于主从复制与时间点恢复（PITR）。
  • 基本分析：使用mysqlbinlog按时间/位置过滤与回放，如：
    • mysqlbinlog --start-datetime=“2025-12-24 00:00:00” --stop-datetime=“2025-12-24 12:00:00” /var/log/mysql/binlog | less
    • mysqlbinlog --start-position=12345 --stop-position=67890 /var/log/mysql/binlog | mysql -u root -p。

五 日志轮转与长期治理

• 使用logrotate管理日志体积与保留策略：
  • 配置路径：全局**/etc/logrotate.conf**，MariaDB专用建议**/etc/logrotate.d/mysql**；
  • 测试与验证：sudo logrotate -vf /etc/logrotate.d/mysql；
  • 运行方式：通常由**/etc/cron.daily/logrotate**每日执行；
  • 状态检查：/var/lib/logrotate/status。
• 审计日志轮转：除logrotate外，审计插件自带server_audit_file_rotate_size与server_audit_file_rotations参数，便于按大小自动切分。
• 治理建议：
  • 为错误日志、慢查询日志、审计日志分别设定合理的保留周期与压缩策略；
  • 避免长期开启general_log与log_queries_not_using_indexes在生产环境造成IO压力；
  • 将pt-query-digest或自研脚本纳入定时任务，每周/每日产出慢查询报告并跟踪优化效果。