如何在MySQL中清理或调整ibtmp1文件的大小? [英] How to clean or resize the ibtmp1 file in MySQL?

问题描述

MySQL 5.7引入了一个新文件 ibtmp1 ，用于在InnoDB中存储临时数据以提高性能.

MySQL 5.7 introduces a new file ibtmp1 for storing temporary data in InnoDB to increase the performance.

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

But I have noted that its size increases continuously. On my db server its sizes increases to 92GB.

是否有任何方法可以减小大小或删除文件而无需重新启动服务器?

Is there any way of reducing size or deleting the file without restarting the server ?

谢谢

推荐答案

一旦创建 ibtmp1 ，除非重新启动mysql服务，否则无法通过任何方法对其进行压缩.

The ibtmp1 once created can't be shrink by any method without restarting mysql service.

有两种处理方法:

注意事项:在服务器启动时，您应该将此文件的大小限制为:

Precaution : At the time of server start you should limit the size of this file as:

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

其中最大5G表示此文件大小限制为5GB.

where max 5G means this file size limits to 5GB.

治愈:如果文件已创建，则需要重新启动服务:

Cure : If file already created you need to restart service:

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

这篇关于如何在MySQL中清理或调整ibtmp1文件的大小?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！