MySQL 日志系统全面解析

 一、四大核心日志对比

二、各日志详细说明

1. 错误日志 (Error Log)

作用：MySQL 的"黑匣子"，记录服务器运行状态、警告和错误信息

内容：启动/关闭信息、严重错误、警告消息、关键系统事件

配置：通常默认开启，存储在 `hostname.err` 文件中

建议：始终开启，定期监控但不要频繁写入

2. 二进制日志 (Binary Log)

作用：数据恢复和主从复制的基石

内容：所有数据变更语句（DDL/DML），以二进制格式存储

格式：`STATEMENT`（语句）、`ROW`（行，推荐）、`MIXED`（混合）

恢复：支持时间点恢复（PITR）和位置点恢复

3. 通用查询日志 (General Query Log)

作用：完整的操作审计 trail

内容：所有客户端连接、查询、操作记录

开销：性能影响最大，会产生大量 I/O

建议：仅在调试时临时开启，生产环境慎用

4. 慢查询日志 (Slow Query Log)

作用：性能优化的关键工具

内容：执行时间超过阈值的查询语句

阈值：通过 `long_query_time` 设置（默认10秒）

分析：使用 `mysqldumpslow` 或 `pt-query-digest` 分析

三、日志管理决策指南

1. 开发环境配置

[mysqld]

 错误日志（始终开启）

log_error = /var/log/mysql/error.log

 慢查询日志（开启，阈值较低）

slow_query_log = 1

slow_query_log_file = /var/log/mysql/slow.log

long_query_time = 1.0

 二进制日志（可选开启）

log_bin = /var/log/mysql/mysql-bin

binlog_format = ROW

 通用查询日志（按需开启）

general_log = 0

2. 生产环境配置

[mysqld]

 错误日志（必须开启）

log_error = /var/log/mysql/error.log

 慢查询日志（建议开启）

slow_query_log = 1

long_query_time = 2.0

log_queries_not_using_indexes = 1

 二进制日志（必须开启，用于备份和复制）

log_bin = /var/log/mysql/mysql-bin

binlog_format = ROW

expire_logs_days = 7

 通用查询日志（通常关闭）

general_log = 0

 日志性能优化

sync_binlog = 1

innodb_flush_log_at_trx_commit = 2

3. 性能与安全的权衡

 场景  推荐配置  理由

 高并发OLTP  只开错误日志+二进制日志  最小化I/O影响

 数据仓库  开启慢查询日志  优化复杂查询性能

 安全审计  临时开启通用查询日志  满足合规要求

 故障排查  全开（短期）  全面诊断问题

四、日志维护最佳实践

1. 存储优化

 将日志文件与数据文件分磁盘存储

 错误日志和慢查询日志 -> SSD磁盘

 二进制日志 -> 高容量HDD磁盘

2. 定期维护脚本

!/bin/bash

 日志维护脚本

LOG_DIR="/var/log/mysql"

BACKUP_DIR="/backup/mysql-logs"

 清理30天前的旧日志

find $LOG_DIR -name "*.log.*" -mtime +30 -delete

find $BACKUP_DIR -name "*.gz" -mtime +90 -delete

 压缩一周前的日志

find $LOG_DIR -name "*.log.old" -mtime +7 -exec gzip {} \;

 刷新日志

mysqladmin -u root -p flush-logs

3. 监控告警设置

 监控错误日志中的 `[ERROR]` 条目

 监控慢查询日志的增长速度

 监控二进制日志的磁盘使用率

 设置日志文件大小告警阈值

五、常见使用场景

1. 性能调优

 1. 开启慢查询日志

SET GLOBAL slow_query_log = 'ON';

SET GLOBAL long_query_time = 1;

 2. 重现性能问题

 3. 分析慢查询

mysqldumpslow /var/log/mysql/slow.log

 4. 优化后关闭（生产环境）

SET GLOBAL slow_query_log = 'OFF';

2. 数据恢复

 1. 恢复全量备份

mysql -u root -p < full_backup.sql

 2. 应用二进制日志恢复增量数据

mysqlbinlog --start-datetime="2023-10-27 10:00:00" mysql-bin.000001 | mysql -u root -p

3. 安全审计

 临时开启通用日志进行审计

SET GLOBAL general_log = 'ON';

执行审计操作

SET GLOBAL general_log = 'OFF';

六、总结建议

1. 必开启：错误日志（所有环境）、二进制日志（生产环境）

2. 按需开启：慢查询日志（性能优化时）、通用查询日志（调试审计时）

3. 性能优先：平衡日志详细程度和系统性能

4. 安全存储：日志文件包含敏感信息，需要安全存储

5. 定期维护：建立日志轮转和清理机制

6. 监控告警：对日志异常设置监控和告警

最终建议配置：

开发环境：错误日志 + 慢查询日志（低阈值）

生产环境：错误日志 + 二进制日志 + 慢查询日志（适当阈值）

审计环境：全部开启（短期）