How to clean or resize ibtmp1 file in MySQL?

Question

MySQL 5.7 introduces a new ibtmp1 file for storing temporary data in InnoDB for better performance.

But I noted that its size is constantly increasing. On my db server, its sizes increase to 92 GB.

Is there a way to reduce the size or delete the file without restarting the server ?

thank

Answer 1

Once created ibtmp1 cannot be compressed in any way without restarting the mysql service.

There are two ways to handle this:

: :

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

max 5G , 5 .

: , :

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

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