MySQL磁盘空间满的解决方案及预防措施

问题背景

在日常运维过程中，我们遇到了MySQL服务器磁盘使用率高达100%，只剩下几十兆可用空间的情况。为了应对这一问题，我们尝试了一系列操作，包括备份数据库、删除实例、删除数据库表，并重启MySQL服务，但这些操作并未能有效释放磁盘空间。

深入分析

经过进一步的研究，我们发现磁盘空间未被释放的原因可能是由于数据库中的碎片过多，特别是data_free字段占据了大量空间。可以通过以下SQL查询来检查特定表的碎片情况：

SELECT CONCAT(TRUNCATE(SUM(data_length)/1024/1024,2),'MB') AS data_size, CONCAT(TRUNCATE(SUM(max_data_length)/1024/1024,2),'MB') AS max_data_size, CONCAT(TRUNCATE(SUM(data_free)/1024/1024,2),'MB') AS data_free, CONCAT(TRUNCATE(SUM(index_length)/1024/1024,2),'MB') AS index_size FROM information_schema.tables WHERE TABLE_NAME = 'datainfo';

查询结果显示，某张表的data_free字段竟高达20GB。

解决步骤

根据网络上的建议，我们尝试了以下方法来整理表的碎片：

ALTER TABLE datainfo ENGINE=InnoDB; ANALYZE TABLE datainfo; OPTIMIZE TABLE datainfo;

然而，由于数据库版本为5.562，不支持InnoDB引擎，且目标表已被误删，上述方法无法实施。

最终解决方案

在尝试了多种方法后，我们采取了以下步骤成功释放了磁盘空间：

1. 使用命令查看MySQL的安装位置和配置文件路径：

   ps aux | grep mysqld

2. 停止MySQL服务：

   service mysql stop

3. 删除/var/lib/mysql目录下的ibdata1、ib_logfile0和ib_logfile1文件。
4. 移动MySQL的配置文件：

   mv /etc/my.cnf ./abc

5. 重新启动MySQL服务：

   service mysql start

经过上述操作，磁盘空间得到了有效释放。接下来，我们使用Navicat备份工具进行了数据库的备份与恢复。

1. 使用Navicat备份数据库，生成备份文件（如200409141055.psc）。

2. 新建一个数据库实例，设置数据库名称和字符集。

3. 将备份文件还原到新创建的数据库实例中。初次还原时，虽然表结构创建成功，但表内无数据。经调查发现，这是因为还原过程遇到了错误而中断。调整还原配置后，再次进行还原，最终成功恢复了数据库中的数据。

总结

MySQL数据库碎片化是常见的问题，主要由频繁的删除和插入操作导致。定期整理碎片可以减少磁盘空间的浪费，提高数据库性能。建议定期（如每月一次）进行碎片整理，并选择在网站访问量较低的时间段执行此操作，以避免影响正常业务。