如何在MySQL中清理或调整ibtmp1文件的大小？

Question

MySQL 5.7引入了一个名为ibtmp1的新文件，用于存储InnoDB的临时数据以提高性能。

但我注意到它的大小不断增加。在我的数据库服务器上，它的大小增长到了92GB。

是否有任何方法可以减小它的大小或删除该文件而无需重启服务器？

谢谢

- Aman Aggarwal

---

可能有用的链接：http://dba.stackexchange.com/questions/136627/managing-ibtmp1-file-growth-on-mysql-5-7 - Tim Biegeleisen

@TimBiegeleisen 我正在寻找一种解决方案，可以在不重启服务的情况下解决这个问题，因为在我的情况下，这个文件增长得非常迅速。 - Aman Aggarwal

我给你的问题点了赞，目前这就是我所能做的了。希望你能得到答案。 - Tim Biegeleisen

（不是立即解决方案）我建议您对需要大型临时表的查询进行批判性审视。良好的查询和良好的索引不会消耗92GB的临时空间。 - Rick James

如果必须重新启动，请考虑保存和重新加载缓冲池。这应该可以减少重新启动的时间。 - Rick James

Answer 1

注意：创建了 ibtmp1 文件后，无法通过任何方法在不重启 MySQL 服务的情况下缩小它。处理该问题有两种方法：

预防措施：在服务器启动时，你应该将该文件的大小限制为：

 innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:5G

其中max 5G表示该文件大小限制为5GB。

解决方法：如果文件已经创建，你需要重新启动服务：

SET GLOBAL innodb_fast_shutdown = 0;
Shutdown MySQL
remove ibtmp1
start MySQL.

文档：https://dev.mysql.com/doc/refman/5.7/en/innodb-temporary-tablespace.html

- Aman Aggarwal

---

在运行AWS RDS MySQL 5.7时，“failover-with-reboot”足以截断此文件。 - Alex

这个问题特别针对本地的MySQL，而不是云端。 - Aman Aggarwal

迷你教程：编辑文件“/etc/mysql/mysql.conf.d/mysqld.cnf”。在最后一行添加：“innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:5G”（或xG）。重新启动Mysql：“sudo service mysql restart” --- 在“/var/lib/mysql/ibtmp1”处检查文件大小。 - Avatar

我发现没有必要删除ibtmp1文件，当我重新启动mysql服务时，该文件已被调整大小。Ver 14.14 - Jon Hulka

@JonHulka 这个问题是如果你不能重启怎么办 - 所以预防胜于治疗。 - Aman Aggarwal

@AmanAggarwal 注意措施两面性。我在犯错误时倾向于尽可能少地干扰MySQL管理其数据的方式 - 这可能没有任何区别。我曾经有过一次将ib*文件移动的不好经历。我不需要重新启动服务器，只需重新启动MySQL服务即可。 - Jon Hulka

Answer 2

在mysql 5.7及更高版本中，要回收ibtmp1文件使用的空间，您只需重新启动服务即可。
您不需要设置GLOBAL innodb_fast_shutdown = 0;或手动删除文件。

- Geoff_Clapp

Answer 3

要解决这个问题，请按照以下步骤进行：

1- 运行以下命令：
sudo nano /etc/mysql/my.cnf

2- 在 [mysqld] 行下添加以下行：
innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:5G

3- 保存文件。

4- 现在我们需要释放一些磁盘空间并删除 ibtmp1，可以通过重新启动 SQL 服务器来完成：

sudo service mysql restart

- Mohamad Hamouday

---

据我看来，如果超过了5G的限制，这个设置可能会导致临时表的创建失败。 - undefined