ibtmp1文件過大

有個數據庫發現磁盤告警 已經100% 經過排查發現數據庫的data目錄下有個

ibtmp1是個什么東西呢？查看官方文檔后發現

The temporary tablespace is a tablespace for non-compressed InnoDB temporary tables and related objects. The configuration option, innodb_temp_data_file_path, defines a relative path for the temporary tablespace data file. If innodb_temp_data_file_path is not defined, a single auto-extending 12MB data file named ibtmp1 is created in the data directory. The temporary tablespace is recreated on each server start and receives a dynamically generated space ID, which helps avoid conflicts with existing space IDs. The temporary tablespace cannot reside on a raw device. Startup is refused if the temporary tablespace cannot be created.

The temporary tablespace is removed on normal shutdown or on an aborted initialization. The temporary tablespace is not removed when a crash occurs. In this case, the database administrator may remove the temporary tablespace manually or restart the server with the same configuration, which removes and recreates the temporary tablespace

這是非壓縮的innodb臨時表的獨立表空間。通過innodb_temp_data_file_path參數指定文件的路徑，文件名和大小，默認配置為ibtmp1:12M:autoextend，也就是說在支持大文件的系統這個文件大小是可以無限增長的。

該參數是5.7的新特性

針對臨時表及相關對象引入新的“non-redo” undo log，存放于臨時表空間。該類型的undo log非 redolog 因為臨時表不需崩潰恢復、也就無需redo logs，但卻需要 undo log用于回滾、MVCC等。默認的臨時表空間文件為ibtmp1，位于數據目錄在每次服務器啟動時被重新創建，可通過innodb_temp_data_file_path指定臨時表空間。(http://dev.mysql.com/doc/refman/5.7/en/innodb-temporary-table-undo-logs.html)

解決辦法：

1，修改my.cnf配置文件：

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

2，設置innodb_fast_shutdown參數

SET GLOBAL innodb_fast_shutdown = 0;  #InnoDB does a slow shutdown, a full purge and a change buffer merge before shutting down

3，關閉mysql服務

4，刪除ibtmp1文件

5，啟動mysql服務

注意：為了避免以后再出現類似的情況，一定要在限制臨時表空間的最大值，如innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:5G