MySQL 日志轮转与清理策略

日志轮转方法

1. 手动日志轮转

二进制日志手动轮转

sql

-- 手动轮转二进制日志
FLUSH BINARY LOGS;

-- 查看当前二进制日志状态
SHOW MASTER STATUS;

-- 查看所有二进制日志
SHOW BINARY LOGS;

错误日志手动轮转

bash

# 重命名当前错误日志
mv /var/log/mysqld.log /var/log/mysqld.log.old

# 刷新错误日志，生成新的错误日志
mysqladmin -u root -p flush-logs

慢查询日志手动轮转

sql

-- 停止慢查询日志
SET GLOBAL slow_query_log = OFF;

-- 重命名慢查询日志文件
-- 注意：需要在操作系统层面执行

-- 启动慢查询日志，生成新的日志文件
SET GLOBAL slow_query_log = ON;

2. 自动日志轮转

使用 MySQL 内置机制

二进制日志自动轮转

MySQL 二进制日志会在以下情况下自动轮转：

服务器重启
执行 FLUSH LOGS 命令
达到 max_binlog_size 参数设置的大小（默认 1GB）
执行 RESET MASTER 或 RESET SLAVE 命令

配置参数

ini

[mysqld]
# 二进制日志文件大小限制
max_binlog_size = 1G

# 二进制日志过期时间（秒）
binlog_expire_logs_seconds = 2592000  # 30天

使用外部工具

logrotate

logrotate 是 Linux 系统中常用的日志轮转工具，可以用于管理 MySQL 日志。

配置文件示例

txt

# /etc/logrotate.d/mysql

/var/log/mysqld.log {
    daily
    rotate 7
    missingok
    compress
    delaycompress
    notifempty
    create 640 mysql mysql
    postrotate
        /usr/bin/mysqladmin -u root -p$(cat /etc/mysql/root_password) flush-logs
    endscript
}

/var/log/mysql-slow.log {
    daily
    rotate 14
    missingok
    compress
    delaycompress
    notifempty
    create 640 mysql mysql
    postrotate
        /usr/bin/mysql -u root -p$(cat /etc/mysql/root_password) -e "SET GLOBAL slow_query_log = OFF; SET GLOBAL slow_query_log = ON;"
    endscript
}

配置参数说明

参数	说明
daily	每天轮转一次
rotate 7	保留7个旧日志文件
missingok	如果日志文件不存在，不报错
compress	压缩旧日志文件
delaycompress	延迟压缩，只压缩前一天的日志
notifempty	如果日志文件为空，不轮转
create 640 mysql mysql	创建新日志文件，权限为640，所有者为mysql:mysql
postrotate/endscript	轮转后执行的命令

使用 cron 作业

bash

# 添加到 crontab，每天凌晨2点执行日志轮转和清理
0 2 * * * /usr/bin/mysqladmin -u root -p$(cat /etc/mysql/root_password) flush-logs
0 3 * * * /usr/bin/find /var/lib/mysql -name "mysql-bin.*" -mtime +7 -delete

3. 云数据库日志轮转

各大云数据库厂商提供了自动日志轮转和管理功能，如：

AWS RDS：自动管理二进制日志、错误日志和慢查询日志，支持设置保留时间
阿里云 RDS：提供日志自动轮转和清理功能，支持自定义保留时间
腾讯云 TDSQL：自动管理各种日志文件，支持日志下载和查询

日志清理策略

1. 日志保留策略

基于时间的保留策略

根据业务需求和合规要求，设置不同日志的保留时间：

日志类型	建议保留时间	适用场景
错误日志	30-90天	用于故障排查
二进制日志	7-30天	用于复制和恢复
慢查询日志	14-30天	用于性能优化
一般查询日志	7天或关闭	用于调试，生产环境建议关闭
中继日志	7天	用于复制架构
审计日志	90-365天	用于安全审计和合规

基于大小的保留策略

根据磁盘空间情况，设置日志文件的大小限制：

日志类型	建议大小限制
二进制日志	100M-1G
慢查询日志	1G-10G
错误日志	100M-500M

2. 清理方法

二进制日志清理

使用 PURGE 命令

sql

-- 删除指定日期之前的所有二进制日志
PURGE BINARY LOGS BEFORE '2023-01-01 00:00:00';

-- 删除指定日志文件之前的所有二进制日志
PURGE BINARY LOGS TO 'mysql-bin.000100';

-- 删除7天前的所有二进制日志
PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 7 DAY);

使用 expire_logs_days 参数

ini

[mysqld]
# 二进制日志保留天数（已弃用，推荐使用 binlog_expire_logs_seconds）
expire_logs_days = 7

# 二进制日志保留秒数（推荐）
binlog_expire_logs_seconds = 604800  # 7天

使用 RESET MASTER 命令

sql

-- 重置主服务器二进制日志，删除所有二进制日志
RESET MASTER;

注意：此命令会删除所有二进制日志，仅在主从复制架构中重新初始化时使用。

慢查询日志清理

使用 SQL 命令

sql

-- 停止慢查询日志
SET GLOBAL slow_query_log = OFF;

-- 在操作系统层面删除慢查询日志文件
-- rm /var/log/mysql-slow.log

-- 启动慢查询日志，生成新的日志文件
SET GLOBAL slow_query_log = ON;

使用 truncate 命令

bash

# 清空慢查询日志文件，保留文件本身
truncate -s 0 /var/log/mysql-slow.log

错误日志清理

bash

# 重命名当前错误日志
mv /var/log/mysqld.log /var/log/mysqld.log.old

# 刷新错误日志，生成新的错误日志
mysqladmin -u root -p flush-logs

# 压缩旧错误日志
gzip /var/log/mysqld.log.old

# 删除超过30天的旧错误日志
find /var/log -name "mysqld.log.*" -mtime +30 -delete

3. 自动清理脚本

通用日志清理脚本

bash

#!/bin/bash
# MySQL 日志清理脚本

# 配置参数
MYSQL_USER="root"
MYSQL_PASSWORD="password"
LOG_DIR="/var/log/mysql"
BINLOG_DIR="/var/lib/mysql"

# 保留天数
ERROR_LOG_DAYS=30
SLOW_LOG_DAYS=14
BINLOG_DAYS=7

# 清理错误日志
echo "清理错误日志..."
find $LOG_DIR -name "mysqld.log.*" -mtime +$ERROR_LOG_DAYS -delete

# 清理慢查询日志
echo "清理慢查询日志..."
find $LOG_DIR -name "mysql-slow.log.*" -mtime +$SLOW_LOG_DAYS -delete

# 清理二进制日志
echo "清理二进制日志..."
mysql -u $MYSQL_USER -p$MYSQL_PASSWORD -e "PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL $BINLOG_DAYS DAY);"

# 清理一般查询日志
echo "清理一般查询日志..."
find $LOG_DIR -name "mysql.log.*" -mtime +$ERROR_LOG_DAYS -delete

echo "日志清理完成！"

定时执行脚本

bash

# 添加到 crontab，每天凌晨3点执行
0 3 * * * /path/to/mysql-log-cleanup.sh > /dev/null 2>&1

日志管理最佳实践

1. 日志配置最佳实践

启用必要的日志

生产环境：建议启用错误日志、二进制日志和慢查询日志
开发环境：可以额外启用一般查询日志，用于调试

合理设置日志参数

ini

[mysqld]
# 错误日志
log_error = /var/log/mysqld.log

# 二进制日志
log_bin = /var/lib/mysql/mysql-bin
binlog_format = ROW
max_binlog_size = 1G
binlog_expire_logs_seconds = 604800  # 7天

# 慢查询日志
slow_query_log = 1
slow_query_log_file = /var/log/mysql-slow.log
long_query_time = 2
log_queries_not_using_indexes = 1

# 一般查询日志（生产环境建议关闭）
general_log = 0
general_log_file = /var/log/mysql.log

2. 日志轮转最佳实践

选择合适的轮转方式

二进制日志：使用 MySQL 内置的自动轮转机制
错误日志：使用 logrotate 或自定义脚本
慢查询日志：使用 logrotate 或 SQL 命令

合理设置轮转频率

高写入负载：可以设置更频繁的轮转频率，如每小时或每天
低写入负载：可以设置较低的轮转频率，如每天或每周

压缩旧日志

启用日志压缩，减少磁盘空间占用
使用 gzip 或 bzip2 压缩旧日志文件
注意：压缩和解压缩会消耗 CPU 资源，需要平衡磁盘空间和 CPU 使用率

3. 日志清理最佳实践

制定明确的保留策略

根据业务需求和合规要求，制定不同日志的保留时间
将日志保留策略文档化，便于团队成员了解和执行

定期监控日志大小

监控日志文件的大小和增长趋势
设置磁盘空间告警，及时发现日志过大问题
定期检查日志清理脚本的执行情况

避免手动删除日志文件

尽量使用 MySQL 提供的命令或自动脚本清理日志
手动删除日志文件可能导致日志不连续，影响复制和恢复

测试日志恢复流程

定期测试从日志文件恢复数据的流程
确保日志清理策略不会影响数据恢复能力

日志管理工具

1. 内置工具

mysqlbinlog

用于查看和解析二进制日志：

bash

# 查看二进制日志内容
mysqlbinlog /var/lib/mysql/mysql-bin.000100

# 按时间范围查看二进制日志
mysqlbinlog --start-datetime="2023-01-01 00:00:00" --stop-datetime="2023-01-02 00:00:00" /var/lib/mysql/mysql-bin.000100

# 将二进制日志转换为 SQL 语句
mysqlbinlog /var/lib/mysql/mysql-bin.000100 > binlog.sql

mysqldumpslow

用于分析慢查询日志：

bash

# 查看慢查询日志摘要
mysqldumpslow /var/log/mysql-slow.log

# 按查询次数排序
mysqldumpslow -s c /var/log/mysql-slow.log

# 按执行时间排序
mysqldumpslow -s t /var/log/mysql-slow.log

# 显示前10条慢查询
mysqldumpslow -t 10 /var/log/mysql-slow.log

2. 第三方工具

pt-query-digest

Percona Toolkit 中的工具，用于分析慢查询日志：

bash

# 分析慢查询日志
pt-query-digest /var/log/mysql-slow.log

# 将分析结果保存到文件
pt-query-digest /var/log/mysql-slow.log > slow_query_analysis.txt

# 分析实时慢查询日志
pt-query-digest --processlist h=localhost,u=root,p=password

mysql-slowlog-filter

用于过滤和分析慢查询日志：

bash

# 安装
pip install mysql-slowlog-filter

# 分析慢查询日志
mysql-slowlog-filter /var/log/mysql-slow.log

logwatch

用于监控和报告日志文件：

bash

# 安装
sudo yum install logwatch

# 生成 MySQL 日志报告
logwatch --service mysql --detail high

常见问题（FAQ）

Q1: 如何查看当前 MySQL 日志配置？

A1: 使用以下命令查看当前 MySQL 日志配置：

sql

-- 查看所有日志相关参数
SHOW VARIABLES LIKE '%log%';

-- 查看错误日志配置
SHOW VARIABLES LIKE 'log_error';

-- 查看二进制日志配置
SHOW VARIABLES LIKE '%binlog%';

-- 查看慢查询日志配置
SHOW VARIABLES LIKE '%slow%';

-- 查看一般查询日志配置
SHOW VARIABLES LIKE '%general%';

Q2: 如何启用慢查询日志？

A2: 可以通过以下方式启用慢查询日志：

sql

-- 临时启用（重启后失效）
SET GLOBAL slow_query_log = ON;
SET GLOBAL slow_query_log_file = '/var/log/mysql-slow.log';
SET GLOBAL long_query_time = 2;

-- 永久启用（修改配置文件）
# 在 my.cnf 或 my.ini 中添加以下配置
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql-slow.log
long_query_time = 2

Q3: 二进制日志过大怎么办？

A3: 处理二进制日志过大的方法包括：

调整 max_binlog_size 参数，减小单个二进制日志文件的大小
调整 binlog_expire_logs_seconds 参数，缩短二进制日志的保留时间
手动执行 PURGE BINARY LOGS 命令，删除旧的二进制日志
确保复制架构正常运行，从服务器及时应用二进制日志

Q4: 如何安全地清理慢查询日志？

A4: 安全清理慢查询日志的步骤：

停止慢查询日志：SET GLOBAL slow_query_log = OFF;
重命名或删除慢查询日志文件
启动慢查询日志：SET GLOBAL slow_query_log = ON;

或者使用 logrotate 工具自动管理慢查询日志。

Q5: 日志轮转会影响 MySQL 性能吗？

A5: 日志轮转可能会对 MySQL 性能产生短暂影响，主要表现为：

日志刷新时会产生 I/O 操作，可能导致短暂的性能下降
日志压缩会消耗 CPU 资源

为减少影响，建议：

在业务低峰期执行日志轮转
合理设置轮转频率
平衡日志大小和轮转频率

Q6: 如何监控日志文件大小？

A6: 可以使用以下方法监控日志文件大小：

bash

# 使用 du 命令查看日志文件大小
du -h /var/log/mysqld.log
du -h /var/log/mysql-slow.log
du -h /var/lib/mysql/mysql-bin.* | tail -n 10

# 使用监控工具，如 Prometheus + Grafana
# 配置 Node Exporter 监控文件大小
# 配置告警规则，当日志文件超过阈值时告警

Q7: 日志清理脚本执行失败怎么办？

A7: 处理日志清理脚本执行失败的方法：

检查脚本中的 MySQL 用户名和密码是否正确
检查日志文件路径是否正确
查看脚本执行日志，定位错误原因
确保脚本具有执行权限
考虑添加错误处理和日志记录功能

Q8: 云数据库如何管理日志？

A8: 云数据库通常提供以下日志管理功能：

自动日志轮转和清理
日志下载和查询
日志分析和可视化
自定义日志保留策略
日志告警和通知

建议参考云数据库提供商的文档，了解具体的日志管理方法。

Q9: 如何确保日志清理不影响数据恢复？

A9: 确保日志清理不影响数据恢复的方法：

二进制日志保留时间应长于最近一次全量备份的时间
在执行日志清理前，确保所有从服务器都已应用了要清理的二进制日志
定期测试从日志恢复数据的流程
考虑将重要日志备份到异地存储

Q10: 如何优化慢查询日志性能？

A10: 优化慢查询日志性能的方法：

合理设置 long_query_time 参数，避免记录过多无关查询
考虑使用 log_throttle_queries_not_using_indexes 参数，限制未使用索引的查询日志记录频率
在高负载环境中，可以考虑关闭 log_queries_not_using_indexes 参数
定期清理和分析慢查询日志，及时优化慢查询

案例分析

案例1：二进制日志导致磁盘空间不足

场景描述

某生产环境 MySQL 服务器磁盘空间使用率突然达到 95%，导致服务器性能下降。

问题排查

使用 df -h 命令查看磁盘空间使用情况，发现 /var/lib/mysql 目录占用了大量磁盘空间
使用 du -h /var/lib/mysql 命令查看目录下文件大小，发现二进制日志文件占用了 80% 的磁盘空间
检查二进制日志配置，发现 binlog_expire_logs_seconds 参数设置为默认值 2592000（30天），但实际数据写入量很大，导致二进制日志增长过快

解决方案

立即执行二进制日志清理：

sql

PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 3 DAY);

调整二进制日志配置：

ini

[mysqld]
binlog_expire_logs_seconds = 259200  # 3天
max_binlog_size = 512M  # 减小单个日志文件大小

重启 MySQL 服务，使配置生效
监控磁盘空间使用情况，确保问题得到解决
优化应用程序，减少不必要的数据修改操作

预防措施

定期监控磁盘空间使用情况
合理设置二进制日志保留时间
考虑使用闪回技术，减少对二进制日志的依赖
定期清理和归档二进制日志

案例2：慢查询日志过大影响性能

场景描述

某 MySQL 服务器慢查询日志文件大小达到 50GB，导致服务器 I/O 性能下降。

问题排查

检查慢查询日志配置，发现 long_query_time 参数设置为 0.1 秒，导致记录了大量查询
log_queries_not_using_indexes 参数设置为 1，记录了所有未使用索引的查询
慢查询日志没有启用轮转，导致单个文件过大

解决方案

立即清理慢查询日志：

sql

SET GLOBAL slow_query_log = OFF;

bash

mv /var/log/mysql-slow.log /var/log/mysql-slow.log.old
gzip /var/log/mysql-slow.log.old

sql

SET GLOBAL slow_query_log = ON;

调整慢查询日志配置：

ini

[mysqld]
long_query_time = 2  # 增加慢查询阈值
log_queries_not_using_indexes = 0  # 关闭未使用索引查询日志

配置 logrotate 管理慢查询日志：
txt

/var/log/mysql-slow.log { daily rotate 7 missingok compress delaycompress notifempty create 640 mysql mysql postrotate /usr/bin/mysql -u root -p$(cat /etc/mysql/root_password) -e "SET GLOBAL slow_query_log = OFF; SET GLOBAL slow_query_log = ON;" endscript }


4. 使用 pt-query-digest 分析慢查询日志，优化慢查询

#### 预防措施

- 合理设置慢查询阈值
- 选择性记录未使用索引的查询
- 启用慢查询日志轮转
- 定期分析和优化慢查询

## 未来发展趋势

### 1. 智能化日志管理

- **自动日志分析**：使用机器学习和 AI 技术自动分析日志，识别异常和优化机会
- **智能日志保留**：根据日志重要性自动调整保留时间
- **预测性日志管理**：预测日志增长趋势，提前调整日志策略

### 2. 云原生日志管理

- **集中式日志管理**：将日志集中存储到云存储或日志服务中
- **实时日志分析**：提供实时日志分析和告警功能
- **Serverless 日志处理**：使用无服务器架构处理日志，降低成本

### 3. 安全日志增强

- **加密日志传输**：确保日志在传输过程中的安全性
- **日志完整性验证**：防止日志被篡改
- **细粒度审计日志**：提供更详细的审计信息，用于安全合规

### 4. 高效日志存储

- **压缩算法优化**：使用更高效的压缩算法，减少日志存储空间
- **列式存储**：使用列式存储格式，提高日志查询效率
- **分层存储**：将热日志存储在高性能存储中，冷日志存储在低成本存储中

MySQL 日志轮转与清理策略 ​

日志轮转方法 ​

1. 手动日志轮转 ​

二进制日志手动轮转 ​

错误日志手动轮转 ​

慢查询日志手动轮转 ​

2. 自动日志轮转 ​

使用 MySQL 内置机制 ​

二进制日志自动轮转 ​

配置参数 ​

使用外部工具 ​

logrotate ​

配置文件示例 ​

配置参数说明 ​

使用 cron 作业 ​

3. 云数据库日志轮转 ​

日志清理策略 ​

1. 日志保留策略 ​

基于时间的保留策略 ​

基于大小的保留策略 ​

2. 清理方法 ​

二进制日志清理 ​

使用 PURGE 命令 ​

使用 expire_logs_days 参数 ​

使用 RESET MASTER 命令 ​

慢查询日志清理 ​

使用 SQL 命令 ​

使用 truncate 命令 ​

错误日志清理 ​

3. 自动清理脚本 ​

通用日志清理脚本 ​

定时执行脚本 ​

日志管理最佳实践 ​

1. 日志配置最佳实践 ​

启用必要的日志 ​

合理设置日志参数 ​

2. 日志轮转最佳实践 ​

选择合适的轮转方式 ​

合理设置轮转频率 ​

压缩旧日志 ​

3. 日志清理最佳实践 ​

制定明确的保留策略 ​

定期监控日志大小 ​

避免手动删除日志文件 ​

测试日志恢复流程 ​

日志管理工具 ​

1. 内置工具 ​

mysqlbinlog ​

mysqldumpslow ​

2. 第三方工具 ​

pt-query-digest ​

mysql-slowlog-filter ​

logwatch ​

MySQL 日志轮转与清理策略

日志轮转方法

1. 手动日志轮转

二进制日志手动轮转

错误日志手动轮转

慢查询日志手动轮转

2. 自动日志轮转

使用 MySQL 内置机制

二进制日志自动轮转

配置参数

使用外部工具

logrotate

配置文件示例

配置参数说明

使用 cron 作业

3. 云数据库日志轮转

日志清理策略

1. 日志保留策略

基于时间的保留策略

基于大小的保留策略

2. 清理方法

二进制日志清理

使用 PURGE 命令

使用 expire_logs_days 参数

使用 RESET MASTER 命令

慢查询日志清理

使用 SQL 命令

使用 truncate 命令

错误日志清理

3. 自动清理脚本

通用日志清理脚本

定时执行脚本

日志管理最佳实践

1. 日志配置最佳实践

启用必要的日志

合理设置日志参数

2. 日志轮转最佳实践

选择合适的轮转方式

合理设置轮转频率

压缩旧日志

3. 日志清理最佳实践

制定明确的保留策略

定期监控日志大小

避免手动删除日志文件

测试日志恢复流程

日志管理工具

1. 内置工具

mysqlbinlog

mysqldumpslow

2. 第三方工具

pt-query-digest

mysql-slowlog-filter

logwatch