Accessing impact of deleting ibtmp1 file in MYSQL

There is a file called ibtmp1 and it is consuming 138 GB. I understand, this stores uncompressed data. Do we really need these files or can we delete it? if deleting is fine, then could you please share the command or the process. Please note that I am using Windows system and MySQL Server version is 5.6

thank you.

That file comes from MySQL 5.7, not 5.6. You can check by connecting to MySQL and running:

mysql> SELECT VERSION();

Shrinking ibtmp1 would require shutting down MySQL. You cannot just the delete the file because there will be a open file handle on that file coming from mysqld.exe.

First, log into MySQL and run:

mysql> SET GLOBAL innodb_fast_shutdown = 0;

Next, shutdown MySQL, delet the file and start mysql back up:

C:\> net stop mysql
C:\> del ibtmp1
C:\> net start mysql

If your ibtmp1 grew that much, then you have been running some bad queries that create large temp tables.

StackExchange Database Administrators Q#134294, answer score: 4