How to clean or resize the ibtmp1 file in MySQL?

Question

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

Solution 1

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.

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

Solution 2

In mysql 5.7 and higher all you need to do to reclaim the space used by the ibtmp1 file is restart the service.

You do not have to set GLOBAL innodb_fast_shutdown = 0; or manually delete the file.

Solution 3

To solve this problem just do the following steps:

1- run this command:
sudo nano /etc/mysql/my.cnf

2- Add the following row under [mysqld] row
innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:5G

3- Save the file.

4- Now we need to free up some disk space and delete ibtmp1, we can do that by restarting the SQL server:

sudo service mysql restart