Debian MariaDB日志分析技巧

Debian MariaDB日志分析实用技巧

一 日志类型与定位

• 错误日志 Error Log：记录服务启停、崩溃、告警等，是排障首选。Debian常见路径为**/var/log/mysql/error.log**，也可在配置中通过log_error指定；动态查看可用命令：SHOW VARIABLES LIKE ‘log_error’; 与 tail -f /var/log/mysql/error.log。
• 通用查询日志 General Query Log：记录所有连接与SQL，用于行为追踪与调试。可通过 SET GLOBAL general_log=ON; 临时开启，或在配置中设定 general_log、general_log_file、general_log_output（FILE/TABLE）。
• 慢查询日志 Slow Query Log：定位性能瓶颈。建议设置阈值 long_query_time（如2秒），并开启 log_queries_not_using_indexes 记录未用索引查询；动态开启：SET GLOBAL slow_query_log=ON; SET GLOBAL long_query_time=2;
• 二进制日志 Binary Log：用于复制与时间点恢复（PITR），可用 mysqlbinlog 解析。
• 中继日志 Relay Log：仅主从复制从库使用。
• 审计日志 Audit Log（可选）：记录登录、查询、DML等安全审计事件，需安装插件（如 mariadb-plugin-audit 或 server_audit）后启用。
  以上路径与开关在 Debian 的 MariaDB 上通用，配置文件常见位置为**/etc/mysql/mariadb.conf.d/50-server.cnf**。

二 快速查看与常用命令

• 实时查看错误日志：tail -f /var/log/mysql/error.log；关注关键词：ERROR、Warning、Aborted connection、Deadlock。
• 通用日志检索：grep “SELECT” /var/log/mysql/mariadb-general.log 或按用户/库过滤。
• 慢查询分析：
  • 内置工具：mysqldumpslow /var/log/mysql/slow.log（常用参数：-s t 按时间排序，-r 倒序，-t 10 取Top N）。
  • 高级工具：pt-query-digest /var/log/mysql/slow.log（需安装 percona-toolkit），可输出最耗时的语句、样本、执行计划指纹等。
• 二进制日志解析与时间点恢复：
  • 查看：mysqlbinlog /var/log/mysql/mariadb-bin.000001；可按时间/位置过滤：mysqlbinlog --start-datetime=“2025-12-28 10:00:00” /var/log/mysql/mariadb-bin.000001。
  • PITR思路：先全量备份恢复至最近一次备份点，再用 mysqlbinlog 重放至故障前的时间点。
• 日志滚动与归档：FLUSH LOGS; 或 mysqladmin flush-logs 触发滚动，便于归档与清理。

三 高效检索与统计命令示例

• 错误日志中统计“拒绝访问”的来源IP：grep “Access denied” /var/log/mysql/error.log | awk ‘{print $NF}’ | sort | uniq -c | sort -nr | head。
• 慢查询Top N（按总耗时）：mysqldumpslow -s t -t 10 /var/log/mysql/slow.log。
• 未使用索引的慢查询：grep “Query_time.*Lock_time” /var/log/mysql/slow.log | grep “Rows_examined:[0-9]+ Rows_sent:[0-9]+” | sort -k5nr | head（结合 pt-query-digest 更直观）。
• 审计日志按用户统计操作次数：awk -F’,’ ‘{print $3}’ /var/log/mysql/audit.log | sort | uniq -c | sort -nr | head（字段分隔符与插件版本有关，先 head 确认格式）。
• 通用日志中某库/某表的操作占比：grep “mydb.mytable” /var/log/mysql/mariadb-general.log | awk ‘{print $4}’ | sort | uniq -c | sort -nr。
• 二进制日志按时间窗口导出重放：mysqlbinlog --start-datetime=“2025-12-28 09:00:00” --stop-datetime=“2025-12-28 10:00:00” /var/log/mysql/mariadb-bin.000001 > replay.sql。

四 配置与轮转要点

• 慢查询建议配置（按需调整阈值与采样）：
  slow_query_log=1
  slow_query_log_file=/var/log/mysql/mariadb-slow.log
  long_query_time=2
  log_queries_not_using_indexes=1
• 通用查询日志（仅调试短期开启，避免性能与磁盘压力）：
  general_log=1
  general_log_file=/var/log/mysql/mariadb-general.log
  general_log_output=FILE
• 二进制日志（确保开启以支持复制与PITR）：
  log_bin=/var/log/mysql/mariadb-bin
  binlog_format=ROW
  expire_logs_days=7
  sync_binlog=1
• 审计日志（可选，合规与溯源）：
  plugin-load-add=server_audit 或 plugin-load-add=audit_log.so
  server_audit_logging=ON 或 audit_log=ON
  server_audit_file_path=/var/log/mysql/audit.log 或 audit_log_file=/var/log/mysql/mariadb-audit.log
• logrotate 示例（/etc/logrotate.d/mariadb）：
  /var/log/mysql/*.log {
  daily
  rotate 7
  missingok
  compress
  delaycompress
  notifempty
  create 640 mysql adm
  sharedscripts
  postrotate
  if test -x /usr/bin/mysqladmin && /usr/bin/mysqladmin ping &>/dev/null; then
  /usr/bin/mysqladmin flush-logs
  fi
  endscript
  }
  以上配置项与路径适用于 Debian 上的 MariaDB，修改后使用 systemctl restart mariadb 使配置生效。

五 监控与告警落地

• 日志到集中平台：使用 Filebeat/rsyslog → Logstash → Elasticsearch → Kibana（ELK） 构建索引与可视化，按库/用户/SQL指纹聚合与告警。
• 指标与可视化：结合 Prometheus + mysqld_exporter + Grafana 做实时看板与阈值告警（如连接数、慢查询数、复制延迟等）。
• 关键告警建议：
  • 错误日志出现 ERROR/Aborted connection 突增；
  • 连接数接近上限（Threads_connected 超过最大连接的80%）；
  • 慢查询数量/95分位耗时持续上升；
  • 复制延迟或 I/O 异常。
    这些做法可将“事后分析”转为“事前预警”，缩短 MTTR。