mysql - 如何在 MySQL 中收缩/清除 ibdata1 文件

Question

我在localhost中使用MySQL作为在R中进行统计的“查询工具”，也就是说，每次运行R脚本时，我都会创建一个新数据库(A)，创建一个新表(B)，导入将数据放入 B，提交查询以获取我需要的数据，然后我删除 B 并删除 A。

它对我来说工作正常，但我意识到 ibdata 文件的大小正在迅速增加，我没有在 MySQL 中存储任何内容，但 ibdata1 文件已经超过 100 MB。

我在设置中使用了或多或少的默认 MySQL 设置，有没有一种方法可以让我在一段固定的时间后自动收缩/清除 ibdata1 文件？

Answer 1

ibdata1 没有收缩是 MySQL 的一个特别烦人的特性。 ibdata1 文件实际上无法缩小，除非您删除所有数据库、删除文件并重新加载转储。

但是您可以配置 MySQL，以便将每个表(包括其索引)存储为一个单独的文件。这样 ibdata1 就不会变大。根据 Bill Karwin's comment 的说法，从 MySQL 5.6.6 版开始默认启用此功能。

这是我不久前做的。但是，要将您的服务器设置为为每个表使用单独的文件，您需要更改 my.cnf 以启用此功能:

[mysqld]
innodb_file_per_table=1

https://dev.mysql.com/doc/refman/5.6/en/innodb-file-per-table-tablespaces.html

因为你想从 ibdata1 中回收空间，你实际上必须删除文件:

1. 对所有数据库、过程、触发器等执行 mysqldump 除了 mysql 和 performance_schema 数据库
2. 删除所有数据库除了上述 2 个数据库
3. 停止mysql
4. 删除 ibdata1 和 ib_log 文件
5. 启动mysql
6. 从转储中恢复

当您在第 5 步中启动 MySQL 时，将重新创建 ibdata1 和 ib_log 文件。

现在您可以出发了。当您创建一个新的数据库进行分析时，这些表将位于单独的 ibd* 文件中，而不是在 ibdata1 中。由于您通常很快就会删除数据库，因此 ibd* 文件将被删除。

http://dev.mysql.com/doc/refman/5.1/en/drop-database.html

你可能已经看到了:
http://bugs.mysql.com/bug.php?id=1341

通过使用命令 ALTER TABLE <tablename> ENGINE=innodb 或 OPTIMIZE TABLE <tablename> 可以从 ibdata1 中提取数据和索引页以分离文件。但是，除非您执行上述步骤，否则 ibdata1 不会缩小。

关于 information_schema ，没有必要也不可能删除。它实际上只是一堆只读 View ，而不是表。并且没有与它们关联的文件，甚至没有数据库目录。 informations_schema 使用内存数据库引擎，在停止/重新启动 mysqld 时被删除并重新生成。参见 https://dev.mysql.com/doc/refman/5.7/en/information-schema.html。