Ubuntu MySQL数据库如何定期清理

Ubuntu MySQL 定期清理实操指南

一 清理思路与准备

• 明确清理目标：优先清理过期业务数据、不再使用的库表、二进制日志 binlog、以及错误日志/慢查询日志等易膨胀对象。
• 先做容量盘点与影响评估：统计库表体积、确认是否有主从复制依赖、评估删除与优化对业务的影响。
• 建立备份与回滚预案：清理前对关键库表做逻辑备份，并保留可回滚窗口。
• 规范权限与账号：清理任务使用最小权限账号，避免在生产高峰执行大事务。

二 定期清理过期数据

• 开启事件调度器：建议先在实例上开启事件调度，以便用 MySQL 内部定时任务执行清理。示例：SET GLOBAL event_scheduler = ON;
• 按时间条件删除（示例）：对日志类表按时间列清理，先预览影响行数，再分批删除以降低锁争用。示例：SELECT COUNT(*) FROM USER_ACTIVITY_LOG WHERE activity_time < NOW() - INTERVAL 30 DAY; 分批删除可用带 LIMIT 的循环，如每次 DELETE … LIMIT 1000，循环至影响行数为 0；并在 activity_time 上建立索引以加速条件定位。
• 使用 MySQL 事件自动执行：将清理逻辑封装为事件，按周/天执行。示例：CREATE EVENT delete_old_activity_logs ON SCHEDULE EVERY 1 WEEK DO DELETE FROM USER_ACTIVITY_LOG WHERE activity_time < NOW() - INTERVAL 30 DAY; 如需清理两个月前的数据，可将 INTERVAL 改为 2 MONTH。
• 注意事项：大表删除务必采用分批策略并避开高峰；删除前先备份；若表为InnoDB且碎片较多，可在确认无长事务后再执行 OPTIMIZE TABLE 回收空间（会重建表，短期占用资源）。

三 清理二进制日志 Binlog

• 自动过期策略：在配置文件 /etc/mysql/my.cnf 的 [mysqld] 段设置过期时间，例如 expire_logs_days = 7（保留最近 7 天）；同时可设置单个文件大小 max_binlog_size = 100M，便于滚动。设置后 MySQL 会自动清理过期的 binlog。
• 手动清理：在确认从库已同步或不再需要旧日志时，按文件或时间清理。示例：PURGE BINARY LOGS TO ‘mysql-bin.000003’; 或 PURGE BINARY LOGS BEFORE ‘2025-01-01 00:00:00’; 清理前可用 SHOW BINARY LOGS; 查看现有日志。
• 重要提示：开启基于时间的自动清理（如 expire_logs_days）前，确保复制与恢复窗口满足要求；手动 PURGE 前确认从库不再需要这些日志，避免复制中断。

四 清理错误日志与慢查询日志

• 错误日志：路径通常在 /var/log/mysql/error.log（以 my.cnf 中 log_error 为准）。若日志过大，可采用“轮转+压缩+清理”的方式处理，例如使用 logrotate 按日轮转并保留近 N 天；不建议直接删除正在写入的日志文件，以免丢失告警与故障信息。
• 慢查询日志：用于定位性能问题，建议开启并定期归档分析，而非简单清空；若确需释放空间，可先备份后轮转/清空，并确保监控与告警不中断。

五 自动化与维护建议

• 使用 Cron 定时执行脚本：将清理 SQL 或调用存储过程写入脚本，通过 Ubuntu 的 Cron 在业务低峰定时执行。示例：0 2 * * * /usr/local/bin/cleanup_mysql.sh（脚本内需安全地处理凭据，建议使用 ~/.my.cnf 或凭据文件并设 600 权限）。
• 定期优化与重建：对高碎片的 MyISAM/InnoDB 表，在确认无长事务后执行 OPTIMIZE TABLE 回收空间；对大表优化安排在低峰时段，并监控执行时长与复制延迟。
• 容量与趋势监控：建立库表体积与 binlog 占用的定期巡检（如每周），结合告警阈值提前处理空间风险；对清理任务保留审计与回滚记录，便于追溯。