mysql执行SQL时如果出现磁盘空间不足的情况，会直接抛出相关错误，导致SQL无法正常执行，严重时还会影响整个数据库服务的可用性。这类问题的核心原因大多和二进制日志、临时文件的堆积有关，需要针对性采取清理策略。
磁盘空间不足的常见表现
当mysql所在磁盘空间不足时，通常会出现以下几种典型情况：
- 执行大批量插入、更新或者复杂查询SQL时，返回磁盘空间不足的错误提示
- mysql错误日志中频繁出现写入失败、空间不足的相关记录
- 数据库服务响应变慢，甚至出现自动停止的情况
- 使用
df -h命令查看磁盘使用率，mysql数据目录所在分区使用率达到100%
二进制日志的清理策略
二进制日志是mysql记录所有数据变更操作的文件，主要用于主从复制和数据恢复，默认情况下会一直累积，很容易占满磁盘空间。
查看二进制日志状态
首先可以登录mysql终端，执行以下命令查看当前二进制日志的相关信息：
-- 查看二进制日志是否开启 SHOW VARIABLES LIKE 'log_bin'; -- 查看当前所有二进制日志文件 SHOW BINARY LOGS; -- 查看当前正在写入的二进制日志文件 SHOW MASTER STATUS;
安全清理二进制日志
清理二进制日志需要避免影响主从复制和数据恢复需求，推荐以下几种安全方式：
- 按时间清理：删除指定时间之前的所有二进制日志，适合确认历史日志不需要恢复的场景
-- 删除2024-01-01 00:00:00之前的所有二进制日志 PURGE BINARY LOGS BEFORE '2024-01-01 00:00:00';
- 按文件清理：删除指定文件之前的所有二进制日志，可以通过
SHOW BINARY LOGS的结果选择保留到哪个文件-- 删除mysql-bin.000010之前的所有二进制日志，保留mysql-bin.000010及之后的文件 PURGE BINARY LOGS TO 'mysql-bin.000010';
- 调整自动清理策略：设置二进制日志的过期时间，让mysql自动清理过期的日志，避免手动操作的遗漏
-- 设置二进制日志过期时间为7天，超过7天的日志会自动清理 SET GLOBAL expire_logs_days = 7; -- 永久生效需要修改my.cnf配置文件，添加以下配置后重启mysql -- expire_logs_days = 7
注意：如果是主从复制架构，清理主库的二进制日志前需要确认从库已经同步完所有需要的日志，避免从库同步中断。
临时文件的清理策略
mysql执行排序、分组、大表关联等复杂SQL时，如果内存排序缓冲区不足，就会在磁盘上生成临时文件，这些文件在SQL执行完成后通常会自动删除，但如果出现异常情况可能会残留。
临时文件的生成场景
- 执行
ORDER BY、GROUP BY操作时，排序数据量超过sort_buffer_size设置的值 - 执行大表关联查询时，关联数据量超过
join_buffer_size设置的值 - 创建临时表时，临时表大小超过
tmp_table_size设置的值，会转为磁盘临时表 - 执行
LOAD DATA INFILE等大批量数据导入操作时
临时文件的清理方法
首先可以通过mysql的状态变量查看临时文件的使用情况：
-- 查看磁盘临时表的创建次数 SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables'; -- 查看内存临时表的创建次数 SHOW GLOBAL STATUS LIKE 'Created_tmp_tables';
如果发现有残留的临时文件，可以通过以下方式处理：
- 正常情况：等待当前执行的SQL完成，mysql会自动清理对应的临时文件，不需要手动干预
- 异常情况：如果SQL已经执行失败或者中断，但是临时文件没有删除，可以先找到临时文件的存储目录，默认是
tmpdir变量指定的路径，执行以下命令查看：SHOW VARIABLES LIKE 'tmpdir';
然后到对应目录下删除名称包含MY开头的临时文件，注意删除前需要确认没有正在执行的mysql进程在使用这些文件。 - 优化参数减少临时文件生成：可以适当调大
sort_buffer_size、join_buffer_size、tmp_table_size等参数，减少磁盘临时文件的生成，但是需要注意不要超过服务器内存上限。-- 临时调整排序缓冲区大小为4M SET GLOBAL sort_buffer_size = 4194304; -- 永久生效需要修改my.cnf配置文件 -- sort_buffer_size = 4M
长期预防磁盘空间不足的建议
除了出现问题时清理文件，日常运维中还可以采取以下措施预防磁盘空间不足：
- 定期监控mysql数据目录所在磁盘的使用率，设置告警阈值，比如使用率达到80%时触发告警
- 合理设置二进制日志的过期时间，根据业务的数据恢复需求设置合适的值，不要保留过长时间的日志
- 优化慢查询，减少大批量复杂SQL的执行，避免产生过多的临时文件
- 定期清理无用的数据库备份文件、错误日志文件，释放磁盘空间
- 如果业务数据增长较快，可以提前规划磁盘扩容，或者将二进制日志、临时文件目录挂载到独立的磁盘分区，避免影响数据文件的存储