时间戳

首页 > 解决方案 > 在 MySQL 5.6.20 中释放空间 - InnoDB

mysql - 在 MySQL 5.6.20 中释放空间 - InnoDB

问题描述

首先，不是数据库人。这就是问题所在，数据库的数据驱动器已满 96%。在 my.cnf 中有一行包含以下内容，（由于空间仅显示部分）

innodb_data_file_path=nmsdata1:4000M;nmsdata2:4000M;

上升到

nmsdata18:4000M:autoextend

因此，在存储文件的文件夹中，文件 1-17 的大小为 4gb，截至今天，文件 18 的大小为 136gb。

我继承了这个系统，它没有供应商支持或太多文档。我可以看到有几张桌子真的很大

Table_name                             NumRows              Data Length
----------                             -------              -----------
pmdata                                 100964536            14199980032
fault                                  310864227            63437946880
event                                  385910821            107896160256

我知道发生了大量的写入，应该有一个 cron 作业告诉它只保留最近 3 个月的数据，但我担心数据库是碎片化的，没有释放空间供使用。

所以我的任务是释放数据库中的空间，这样驱动器就不会被填满。

标签： mysqlinnodb

解决方案

这是 innodb 的一个弱点：表空间从不收缩。它们会增长，即使您对表进行“碎片整理”，它们也只会在内部写入表空间的另一部分，从而使更多的表空间“空闲”以供其他数据使用，但磁盘上文件的大小不会缩小.

即使您 DROP TABLE，也不会为驱动器释放空间。

长期以来，这一直是 InnoDB 的痛点：https ://bugs.mysql.com/bug.php?id=1341 （大约 2003 年报道）。

解决方法是innodb_file_per_table=1在您的配置中使用，因此每个表都有自己的表空间。然后，当您使用OPTIMIZE TABLE <tablename>它时，通过将数据复制到新表空间以更高效、更紧凑的内部布局进行碎片整理，然后删除碎片化的表空间。

但在你的情况下，这有一个大问题。即使您在设置后优化表innodb_file_per_table=1，它们的数据也会被复制到新的表空间中，但这仍然不会像您的nmsdata118 那样缩小或删除旧的多表表空间。它们仍然很大，但“空”。

我要说的是你完蛋了。没有办法缩小这些表空间，而且由于磁盘空间已满，因此也无法重构它们。

这就是我要做的：构建一个新的 MySQL 服务器。确保innodb_file_per_table=1已配置。还要配置数据文件路径的默认值：innodb_data_file_path=ibdata1:12M:autoextend. 这将使中央表空间从一开始就变小。我们将避免用数据扩展它。

然后导出您当前数据库服务器的转储，所有这些。将其导入新的 MySQL 服务器。它将遵循 file-per-table 设置，并且数据将创建并填充新的表空间，每个表一个。

鉴于您对数据增长的了解，这也是构建具有更大存储空间的新服务器的机会。

导入这么多数据需要很长时间。多长时间取决于您的服务器性能规格，但至少需要几个小时。也许几天。如果您在导入时原始数据库仍在占用流量，这将是一个问题。

解决方案是使用复制，因此您的新服务器可以从创建转储的点“赶上”到数据库的当前状态。此过程已记录在案，但对于不是数据库专业人士的人来说，这可能是一个相当长的学习曲线，正如您所说：https://dev.mysql.com/doc/refman/8.0/en/replication-howto。 html

您可能应该找一位知道如何完成这项工作的顾问。

推荐阅读