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

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

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 ?

Thanks

解决方案

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

There are two ways to handle it:

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

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.