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

文章题目：ibtmp1文件过大
URL分享：http://cdiso.cn/article/pdhjch.html