Ubuntu下如何管理MySQL数据库空间

Ubuntu 下 MySQL 数据库空间管理

一 快速定位占用

• 查看系统分区使用，确认是否由 MySQL 引起：
  • 命令：df -h
• 查看 MySQL 数据目录总体占用（默认数据目录为 /var/lib/mysql）：
  • 命令：sudo du -sh /var/lib/mysql
• 在 MySQL 内按库统计占用：
  • 命令：SELECT table_schema AS '数据库', ROUND(SUM(data_length + index_length)/1024/1024, 2) AS '大小(MB)' FROM information_schema.tables GROUP BY table_schema;
• 在 MySQL 内按表查看更细信息（含数据长度、索引长度等）：
  • 命令：SHOW TABLE STATUS FROM your_database_name;
• 可视化工具辅助：
  • 可使用 MySQL Workbench 或 phpMyAdmin 查看库表空间分布与报表。

二 安全释放空间的常用操作

• 删除不再需要的库或表（操作前务必先备份）：
  • 删除库：DROP DATABASE db_name;
  • 删除表：DROP TABLE tbl_name;
• 清理 InnoDB 表碎片（适用于大量删除/更新后）：
  • 单表：OPTIMIZE TABLE tbl_name;
  • 注意：在 InnoDB 上，OPTIMIZE 会重建表，可能锁表且耗时，建议在低峰期执行。
• 清理二进制日志（仅当启用二进制日志时）：
  • 查看：SHOW BINARY LOGS;
  • 按文件删除：PURGE BINARY LOGS TO 'mysql-bin.00000X';
  • 按时间删除：PURGE BINARY LOGS BEFORE '2025-01-01 00:00:00';
• 归档与导出大表后删除历史数据（示例思路）：
  • 导出：mysqldump -u user -p db_name tbl_name --where="created_at < '2024-01-01'" > tbl_archive.sql
  • 导入归档库后，再在业务库执行删除：DELETE FROM tbl_name WHERE created_at < '2024-01-01';（可分批次执行避免长时间锁表）

三 自动化与运维建议

• 建立定期备份与清理策略：
  • 备份示例：mysqldump -u your_user -p your_database > your_database_backup.sql
  • 结合 cron 定时备份，并保留有限份数历史备份，避免备份文件无限增长。
• 设置监控与告警：
  • 定期执行 df -h 或监控磁盘使用率，超过阈值及时告警与处理。
• 二进制日志保留策略：
  • 在配置中设置过期时间（如 expire_logs_days），自动清理过旧日志，减少手工维护成本。

四 应急与风险控制

• 磁盘已满或接近满时：
  • 先快速定位大对象（见第一部分），临时清理可删除的日志/归档文件，释放空间。
  • 清理后如需让空间立即体现在文件系统，可重启 MySQL 服务：sudo systemctl restart mysql（仅在确认安全的情况下执行）。
• 严禁直接删除 /var/lib/mysql 下数据文件，除非已停止服务并做好完整备份；误删可能导致数据不可恢复。
• 任何删除/优化/清理操作前，务必先完成有效备份，并在低峰期执行，减少对业务的影响。