MySQL 8.0.23 临时表空间文件ibtmp1暴增原因及解决方法

背景描述
同事反馈现场的MySQL8.0.23支撑库异常停止。分析发现是临时表空间过大撑爆了磁盘导致MySQL异常终止。
分析过程
查看MySQL版本
[root@sjjhpt182 mysql]# mysql -V
mysql Ver 8.0.23 for Linux on x86_64 (MySQL Community Server - GPL)
You have mail in /var/spool/mail/root
查看配置文件
–日志位置
[root@sjjhpt182 ~]# cat /etc/my.cnf | grep log

Remove the leading "# " to disable binary logging

Binary logging captures changes between backups and is enabled by

default. It’s default setting is log_bin=binlog

disable_log_bin

log-error=/var/log/mysqld.log
#log-error=/var/log/mysqld.log
#binlog keep 2 days
#binlog_expire_logs_seconds=172800

–数据目录位置 datadir=/topsoft/mysql
[root@sjjhpt182 bin]# cat /etc/my.cnf | grep dir
datadir=/topsoft/mysql
#datadir=/topsoft/mysql
查看MySQL日志
日志中报错如下：
[MY-012640] [InnoDB] Error number 28 means ‘No space left on device’
[MY-012267] [InnoDB] Could not set the file size of ‘./ibtmp1’. Probably out of disk space
[MY-012926] [InnoDB] Unable to create the shared innodb_temporary.
详细报错如下：
[root@sjjhpt182 ~]# tail -300f /var/log/mysqld.log
2024-04-19T02:09:52.670797Z 1 [Warning] [MY-012638] [InnoDB] Retry attempts for writing partial data failed.
2024-04-19T02:09:52.670937Z 1 [ERROR] [MY-012639] [InnoDB] Write to file ./ibtmp1 failed at offset 11534336, 1048576 bytes should have been written, only 1007616 were written. Operating system error number 28. Check that your OS and file system support files of this size. Check also that the disk is not full or a disk quota exceeded.
2024-04-19T02:09:52.671070Z 1 [ERROR] [MY-012640] [InnoDB] Error number 28 means ‘No space left on device’
2024-04-19T02:09:52.671327Z 1 [ERROR] [MY-012267] [InnoDB] Could not set the file size of ‘./ibtmp1’. Probably out of disk space
2024-04-19T02:09:52.671470Z 1 [ERROR] [MY-012926] [InnoDB] Unable to create the shared innodb_temporary.
2024-04-19T02:09:52.671613Z 1 [ERROR] [MY-012930] [InnoDB] Plugin initialization aborted with error Generic error.
2024-04-19T02:09:53.035032Z 1 [ERROR] [MY-010334] [Server] Failed to initialize DD Storage Engine
2024-04-19T02:09:53.035415Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2024-04-19T02:09:53.035938Z 0 [ERROR] [MY-010119] [Server] Aborting
查看磁盘空间
/topsoft目录已100%
[root@sjjhpt182 mysql]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/VolGroup-lv_root
45G 12G 31G 28% /
tmpfs 63G 12K 63G 1% /dev/shm
/dev/vda1 477M 39M 413M 9% /boot
/dev/vdc 296G 281G 28M 100% /topsoft
/dev/vdd 296G 15G 266G 6% /tomcat-topsoft
/dev/mapper/lvm_data-vg_data
493G 70M 467G 1% /data
You have mail in /var/spool/mail/root
分析目录大小
发现#innodb_temp目录占用了80G。
[root@sjjhpt182 mysql]# cd /topsoft/
[root@sjjhpt182 topsoft]# du -sh * | sort -hr
280G mysql
313M java
157M nodejs
69M dataexchange
16K lost+found
4.0K logs
4.0K dataexchange182

[root@sjjhpt182 mysql]# ls -lh
total 1.1G
-rw-r-----. 1 mysql mysql 56 Jun 20 2023 auto.cnf
-rw-r----- 1 mysql mysql 879M Apr 19 04:45 binlog.000001
-rw-r----- 1 mysql mysql 16 Apr 17 18:30 binlog.index
-rw-------. 1 mysql mysql 1.7K Jun 20 2023 ca-key.pem
-rw-r–r–. 1 mysql mysql 1.1K Jun 20 2023 ca.pem
-rw-r–r–. 1 mysql mysql 1.1K Jun 20 2023 client-cert.pem
-rw-------. 1 mysql mysql 1.7K Jun 20 2023 client-key.pem
drwxr-x—. 2 mysql mysql 4.0K Jan 9 14:30 dataexchange182
-rw-r-----. 1 mysql mysql 192K Apr 19 04:45 #ib_16384_0.dblwr
-rw-r-----. 1 mysql mysql 8.2M Apr 19 04:45 #ib_16384_1.dblwr
-rw-r----- 1 mysql mysql 9.4K Apr 17 18:25 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12M Apr 19 04:45 ibdata1
-rw-r-----. 1 mysql mysql 48M Apr 19 09:28 ib_logfile0
-rw-r-----. 1 mysql mysql 48M Apr 18 22:30 ib_logfile1
drwxr-x—. 2 mysql mysql 12K Apr 18 18:55 #innodb_temp
drwxr-x—. 2 mysql mysql 4.0K Jun 20 2023 mysql
-rw-r-----. 1 mysql mysql 25M Apr 19 04:44 mysql.ibd
drwxr-x—. 2 mysql mysql 4.0K Jun 20 2023 performance_schema
-rw-------. 1 mysql mysql 1.7K Jun 20 2023 private_key.pem
-rw-r–r–. 1 mysql mysql 452 Jun 20 2023 public_key.pem
-rw-r–r–. 1 mysql mysql 1.1K Jun 20 2023 server-cert.pem
-rw-------. 1 mysql mysql 1.7K Jun 20 2023 server-key.pem
drwxr-x—. 2 mysql mysql 4.0K Jun 20 2023 sys
-rw-r-----. 1 mysql mysql 32M Apr 19 04:45 undo_001
-rw-r-----. 1 mysql mysql 32M Apr 19 04:45 undo_002

[root@sjjhpt182 mysql]# du -sh * | sort -hr
199G dataexchange182
80G #innodb_temp
879M binlog.000001
48M ib_logfile1
48M ib_logfile0
32M undo_002
32M undo_001
26M mysql.ibd
12M ibdata1

–查看#innodb_temp目录下有哪些文件 按时间升序
[root@sjjhpt182 #innodb_temp]# ls -lhtr
total 80G
-rw-r----- 1 mysql mysql 728M Apr 18 21:10 temp_3.ibt
-rw-r----- 1 mysql mysql 472M Apr 19 00:14 temp_32.ibt
-rw-r----- 1 mysql mysql 472M Apr 19 00:37 temp_40.ibt
-rw-r----- 1 mysql mysql 80K Apr 19 00:59 temp_20.ibt
-rw-r----- 1 mysql mysql 728M Apr 19 02:29 temp_12.ibt
-rw-r----- 1 mysql mysql 472M Apr 19 02:37 temp_54.ibt
-rw-r----- 1 mysql mysql 472M Apr 19 03:27 temp_25.ibt
-rw-r----- 1 mysql mysql 472M Apr 19 04:32 temp_47.ibt
-rw-r----- 1 mysql mysql 472M Apr 19 04:35 temp_35.ibt
-rw-r----- 1 mysql mysql 472M Apr 19 04:35 temp_70.ibt
-rw-r----- 1 mysql mysql 728M Apr 19 04:39 temp_8.ibt
-rw-r----- 1 mysql mysql 472M Apr 19 04:44 temp_37.ibt
-rw-r----- 1 mysql mysql 475M Apr 19 04:45 temp_1.ibt
-rw-r----- 1 mysql mysql 472M Apr 19 04:45 temp_56.ibt
-rw-r----- 1 mysql mysql 316M Apr 19 04:45 temp_174.ibt
-rw-r----- 1 mysql mysql 496M Apr 19 04:45 temp_55.ibt
-rw-r----- 1 mysql mysql 312M Apr 19 04:45 temp_155.ibt
-rw-r----- 1 mysql mysql 368M Apr 19 04:45 temp_106.ibt
-rw-r----- 1 mysql mysql 456M Apr 19 04:45 temp_42.ibt
-rw-r----- 1 mysql mysql 728M Apr 19 04:45 temp_5.ibt
-rw-r----- 1 mysql mysql 460M Apr 19 04:45 temp_61.ibt
-rw-r----- 1 mysql mysql 368M Apr 19 04:45 temp_95.ibt
-rw-r----- 1 mysql mysql 360M Apr 19 04:45 temp_94.ibt
-rw-r----- 1 mysql mysql 372M Apr 19 04:45 temp_81.ibt
-rw-r----- 1 mysql mysql 452M Apr 19 04:45 temp_60.ibt
-rw-r----- 1 mysql mysql 348M Apr 19 04:45 temp_131.ibt
-rw-r----- 1 mysql mysql 448M Apr 19 04:45 temp_66.ibt
-rw-r----- 1 mysql mysql 456M Apr 19 04:45 temp_62.ibt
-rw-r----- 1 mysql mysql 728M Apr 19 04:45 temp_11.ibt
-rw-r----- 1 mysql mysql 368M Apr 19 04:45 temp_103.ibt
-rw-r----- 1 mysql mysql 456M Apr 19 04:45 temp_21.ibt
-rw-r----- 1 mysql mysql 728M Apr 19 04:45 temp_19.ibt
-rw-r----- 1 mysql mysql 324M Apr 19 04:45 temp_168.ibt
-rw-r----- 1 mysql mysql 296M Apr 19 04:45 temp_191.ibt
-rw-r----- 1 mysql mysql 328M Apr 19 04:45 temp_142.ibt
-rw-r----- 1 mysql mysql 360M Apr 19 04:45 temp_120.ibt
-rw-r----- 1 mysql mysql 368M Apr 19 04:45 temp_92.ibt
-rw-r----- 1 mysql mysql 320M Apr 19 04:45 temp_151.ibt
-rw-r----- 1 mysql mysql 384M Apr 19 04:45 temp_87.ibt
-rw-r----- 1 mysql mysql 456M Apr 19 04:45 temp_26.ibt
-rw-r----- 1 mysql mysql 336M Apr 19 04:45 temp_156.ibt
-rw-r----- 1 mysql mysql 452M Apr 19 04:45 temp_34.ibt
-rw-r----- 1 mysql mysql 308M Apr 19 04:45 temp_195.ibt
-rw-r----- 1 mysql mysql 456M Apr 19 04:45 temp_75.ibt
-rw-r----- 1 mysql mysql 448M Apr 19 04:45 temp_63.ibt
-rw-r----- 1 mysql mysql 456M Apr 19 04:45 temp_48.ibt
-rw-r----- 1 mysql mysql 472M Apr 19 04:45 temp_29.ibt
-rw-r----- 1 mysql mysql 316M Apr 19 04:45 temp_171.ibt
-rw-r----- 1 mysql mysql 320M Apr 19 04:45 temp_170.ibt
-rw-r----- 1 mysql mysql 372M Apr 19 04:45 temp_101.ibt
-rw-r----- 1 mysql mysql 376M Apr 19 04:45 temp_98.ibt
-rw-r----- 1 mysql mysql 456M Apr 19 04:45 temp_41.ibt
-rw-r----- 1 mysql mysql 464M Apr 19 04:45 temp_49.ibt
-rw-r----- 1 mysql mysql 460M Apr 19 04:45 temp_27.ibt
-rw-r----- 1 mysql mysql 344M Apr 19 04:45 temp_132.ibt
-rw-r----- 1 mysql mysql 352M Apr 19 04:45 temp_118.ibt
-rw-r----- 1 mysql mysql 452M Apr 19 04:45 temp_43.ibt
-rw-r----- 1 mysql mysql 464M Apr 19 04:45 temp_24.ibt
-rw-r----- 1 mysql mysql 312M Apr 19 04:45 temp_183.ibt
-rw-r----- 1 mysql mysql 324M Apr 19 04:45 temp_153.ibt
-rw-r----- 1 mysql mysql 352M Apr 19 04:45 temp_112.ibt
-rw-r----- 1 mysql mysql 728M Apr 19 04:45 temp_10.ibt
-rw-r----- 1 mysql mysql 376M Apr 19 04:45 temp_93.ibt
-rw-r----- 1 mysql mysql 424M Apr 19 04:45 temp_82.ibt
-rw-r----- 1 mysql mysql 456M Apr 19 04:45 temp_77.ibt
-rw-r----- 1 mysql mysql 452M Apr 19 04:45 temp_59.ibt
-rw-r----- 1 mysql mysql 464M Apr 19 04:45 temp_22.ibt
-rw-r----- 1 mysql mysql 308M Apr 19 04:45 temp_196.ibt
-rw-r----- 1 mysql mysql 320M Apr 19 04:45 temp_177.ibt
-rw-r----- 1 mysql mysql 320M Apr 19 04:45 temp_161.ibt
-rw-r----- 1 mysql mysql 344M Apr 19 04:45 temp_147.ibt
-rw-r----- 1 mysql mysql 328M Apr 19 04:45 temp_141.ibt
-rw-r----- 1 mysql mysql 348M Apr 19 04:45 temp_130.ibt
-rw-r----- 1 mysql mysql 368M Apr 19 04:45 temp_110.ibt
-rw-r----- 1 mysql mysql 464M Apr 19 04:45 temp_89.ibt
-rw-r----- 1 mysql mysql 428M Apr 19 04:45 temp_84.ibt
-rw-r----- 1 mysql mysql 448M Apr 19 04:45 temp_57.ibt
-rw-r----- 1 mysql mysql 452M Apr 19 04:45 temp_45.ibt
-rw-r----- 1 mysql mysql 308M Apr 19 04:45 temp_194.ibt
-rw-r----- 1 mysql mysql 312M Apr 19 04:45 temp_186.ibt
-rw-r----- 1 mysql mysql 304M Apr 19 04:45 temp_184.ibt
-rw-r----- 1 mysql mysql 320M Apr 19 04:45 temp_163.ibt
-rw-r----- 1 mysql mysql 320M Apr 19 04:45 temp_159.ibt
-rw-r----- 1 mysql mysql 336M Apr 19 04:45 temp_146.ibt
-rw-r----- 1 mysql mysql 352M Apr 19 04:45 temp_111.ibt
-rw-r----- 1 mysql mysql 416M Apr 19 04:45 temp_105.ibt
-rw-r----- 1 mysql mysql 364M Apr 19 04:45 temp_102.ibt
-rw-r----- 1 mysql mysql 460M Apr 19 04:45 temp_23.ibt
-rw-r----- 1 mysql mysql 308M Apr 19 04:45 temp_197.ibt
-rw-r----- 1 mysql mysql 312M Apr 19 04:45 temp_188.ibt
-rw-r----- 1 mysql mysql 316M Apr 19 04:45 temp_179.ibt
-rw-r----- 1 mysql mysql 320M Apr 19 04:45 temp_178.ibt
-rw-r----- 1 mysql mysql 384M Apr 19 04:45 temp_115.ibt
-rw-r----- 1 mysql mysql 372M Apr 19 04:45 temp_108.ibt
-rw-r----- 1 mysql mysql 444M Apr 19 04:45 temp_6.ibt
-rw-r----- 1 mysql mysql 456M Apr 19 04:45 temp_44.ibt
-rw-r----- 1 mysql mysql 448M Apr 19 04:45 temp_39.ibt
-rw-r----- 1 mysql mysql 468M Apr 19 04:45 temp_36.ibt
-rw-r----- 1 mysql mysql 300M Apr 19 04:45 temp_192.ibt
-rw-r----- 1 mysql mysql 324M Apr 19 04:45 temp_164.ibt
-rw-r----- 1 mysql mysql 456M Apr 19 04:45 temp_64.ibt
-rw-r----- 1 mysql mysql 324M Apr 19 04:45 temp_158.ibt
-rw-r----- 1 mysql mysql 336M Apr 19 04:45 temp_148.ibt
-rw-r----- 1 mysql mysql 340M Apr 19 04:45 temp_135.ibt
-rw-r----- 1 mysql mysql 344M Apr 19 04:45 temp_127.ibt
-rw-r----- 1 mysql mysql 368M Apr 19 04:45 temp_107.ibt
-rw-r----- 1 mysql mysql 412M Apr 19 04:45 temp_86.ibt
-rw-r----- 1 mysql mysql 464M Apr 19 04:45 temp_72.ibt
-rw-r----- 1 mysql mysql 448M Apr 19 04:45 temp_33.ibt
-rw-r----- 1 mysql mysql 316M Apr 19 04:45 temp_176.ibt
-rw-r----- 1 mysql mysql 312M Apr 19 04:45 temp_175.ibt
-rw-r----- 1 mysql mysql 344M Apr 19 04:45 temp_139.ibt
-rw-r----- 1 mysql mysql 344M Apr 19 04:45 temp_136.ibt
-rw-r----- 1 mysql mysql 332M Apr 19 04:45 temp_128.ibt
-rw-r----- 1 mysql mysql 344M Apr 19 04:45 temp_126.ibt
-rw-r----- 1 mysql mysql 372M Apr 19 04:45 temp_99.ibt
-rw-r----- 1 mysql mysql 372M Apr 19 04:45 temp_97.ibt
-rw-r----- 1 mysql mysql 368M Apr 19 04:45 temp_91.ibt
-rw-r----- 1 mysql mysql 308M Apr 19 04:45 temp_181.ibt
-rw-r----- 1 mysql mysql 320M Apr 19 04:45 temp_173.ibt
-rw-r----- 1 mysql mysql 344M Apr 19 04:45 temp_121.ibt
-rw-r----- 1 mysql mysql 728M Apr 19 04:45 temp_9.ibt
-rw-r----- 1 mysql mysql 372M Apr 19 04:45 temp_96.ibt
-rw-r----- 1 mysql mysql 460M Apr 19 04:45 temp_78.ibt
-rw-r----- 1 mysql mysql 344M Apr 19 04:45 temp_150.ibt
-rw-r----- 1 mysql mysql 448M Apr 19 04:45 temp_13.ibt
-rw-r----- 1 mysql mysql 464M Apr 19 04:45 temp_90.ibt
-rw-r----- 1 mysql mysql 328M Apr 19 04:45 temp_143.ibt
-rw-r----- 1 mysql mysql 376M Apr 19 04:45 temp_83.ibt
-rw-r----- 1 mysql mysql 456M Apr 19 04:45 temp_46.ibt
-rw-r----- 1 mysql mysql 308M Apr 19 04:45 temp_199.ibt
-rw-r----- 1 mysql mysql 312M Apr 19 04:45 temp_182.ibt
-rw-r----- 1 mysql mysql 728M Apr 19 04:45 temp_16.ibt
-rw-r----- 1 mysql mysql 320M Apr 19 04:45 temp_166.ibt
-rw-r----- 1 mysql mysql 340M Apr 19 04:45 temp_149.ibt
-rw-r----- 1 mysql mysql 332M Apr 19 04:45 temp_122.ibt
-rw-r----- 1 mysql mysql 352M Apr 19 04:45 temp_113.ibt
-rw-r----- 1 mysql mysql 452M Apr 19 04:45 temp_80.ibt
-rw-r----- 1 mysql mysql 320M Apr 19 04:45 temp_169.ibt
-rw-r----- 1 mysql mysql 412M Apr 19 04:45 temp_88.ibt
-rw-r----- 1 mysql mysql 448M Apr 19 04:45 temp_31.ibt
-rw-r----- 1 mysql mysql 304M Apr 19 04:45 temp_198.ibt
-rw-r----- 1 mysql mysql 344M Apr 19 04:45 temp_125.ibt
-rw-r----- 1 mysql mysql 344M Apr 19 04:45 temp_124.ibt
-rw-r----- 1 mysql mysql 344M Apr 19 04:45 temp_123.ibt
-rw-r----- 1 mysql mysql 448M Apr 19 04:45 temp_68.ibt
-rw-r----- 1 mysql mysql 728M Apr 19 04:45 temp_2.ibt
-rw-r----- 1 mysql mysql 308M Apr 19 04:45 temp_200.ibt
-rw-r----- 1 mysql mysql 312M Apr 19 04:45 temp_167.ibt
-rw-r----- 1 mysql mysql 344M Apr 19 04:45 temp_160.ibt
-rw-r----- 1 mysql mysql 400M Apr 19 04:45 temp_100.ibt
-rw-r----- 1 mysql mysql 728M Apr 19 04:45 temp_7.ibt
-rw-r----- 1 mysql mysql 456M Apr 19 04:45 temp_76.ibt
-rw-r----- 1 mysql mysql 456M Apr 19 04:45 temp_74.ibt
-rw-r----- 1 mysql mysql 464M Apr 19 04:45 temp_73.ibt
-rw-r----- 1 mysql mysql 456M Apr 19 04:45 temp_69.ibt
-rw-r----- 1 mysql mysql 452M Apr 19 04:45 temp_52.ibt
-rw-r----- 1 mysql mysql 416M Apr 19 04:45 temp_51.ibt
-rw-r----- 1 mysql mysql 452M Apr 19 04:45 temp_50.ibt
-rw-r----- 1 mysql mysql 360M Apr 19 04:45 temp_4.ibt
-rw-r----- 1 mysql mysql 320M Apr 19 04:45 temp_190.ibt
-rw-r----- 1 mysql mysql 316M Apr 19 04:45 temp_189.ibt
-rw-r----- 1 mysql mysql 320M Apr 19 04:45 temp_172.ibt
-rw-r----- 1 mysql mysql 728M Apr 19 04:45 temp_14.ibt
-rw-r----- 1 mysql mysql 332M Apr 19 04:45 temp_145.ibt
-rw-r----- 1 mysql mysql 344M Apr 19 04:45 temp_134.ibt
-rw-r----- 1 mysql mysql 344M Apr 19 04:45 temp_133.ibt
-rw-r----- 1 mysql mysql 344M Apr 19 04:45 temp_129.ibt
-rw-r----- 1 mysql mysql 352M Apr 19 04:45 temp_114.ibt
-rw-r----- 1 mysql mysql 440M Apr 19 04:45 temp_85.ibt
-rw-r----- 1 mysql mysql 456M Apr 19 04:45 temp_79.ibt
-rw-r----- 1 mysql mysql 452M Apr 19 04:45 temp_71.ibt
-rw-r----- 1 mysql mysql 436M Apr 19 04:45 temp_67.ibt
-rw-r----- 1 mysql mysql 452M Apr 19 04:45 temp_65.ibt
-rw-r----- 1 mysql mysql 452M Apr 19 04:45 temp_58.ibt
-rw-r----- 1 mysql mysql 456M Apr 19 04:45 temp_53.ibt
-rw-r----- 1 mysql mysql 464M Apr 19 04:45 temp_38.ibt
-rw-r----- 1 mysql mysql 728M Apr 19 04:45 temp_30.ibt
-rw-r----- 1 mysql mysql 416M Apr 19 04:45 temp_28.ibt
-rw-r----- 1 mysql mysql 308M Apr 19 04:45 temp_193.ibt
-rw-r----- 1 mysql mysql 728M Apr 19 04:45 temp_18.ibt
-rw-r----- 1 mysql mysql 312M Apr 19 04:45 temp_187.ibt
-rw-r----- 1 mysql mysql 320M Apr 19 04:45 temp_185.ibt
-rw-r----- 1 mysql mysql 320M Apr 19 04:45 temp_180.ibt
-rw-r----- 1 mysql mysql 728M Apr 19 04:45 temp_17.ibt
-rw-r----- 1 mysql mysql 320M Apr 19 04:45 temp_165.ibt
-rw-r----- 1 mysql mysql 320M Apr 19 04:45 temp_162.ibt
-rw-r----- 1 mysql mysql 728M Apr 19 04:45 temp_15.ibt
-rw-r----- 1 mysql mysql 340M Apr 19 04:45 temp_157.ibt
-rw-r----- 1 mysql mysql 324M Apr 19 04:45 temp_154.ibt
-rw-r----- 1 mysql mysql 324M Apr 19 04:45 temp_152.ibt
-rw-r----- 1 mysql mysql 344M Apr 19 04:45 temp_140.ibt
-rw-r----- 1 mysql mysql 344M Apr 19 04:45 temp_138.ibt
-rw-r----- 1 mysql mysql 344M Apr 19 04:45 temp_137.ibt
-rw-r----- 1 mysql mysql 384M Apr 19 04:45 temp_119.ibt
-rw-r----- 1 mysql mysql 352M Apr 19 04:45 temp_117.ibt
-rw-r----- 1 mysql mysql 356M Apr 19 04:45 temp_116.ibt
-rw-r----- 1 mysql mysql 376M Apr 19 04:45 temp_109.ibt
-rw-r----- 1 mysql mysql 404M Apr 19 04:45 temp_104.ibt
-rw-r----- 1 mysql mysql 332M Apr 19 04:45 temp_144.ibt
问题原因
临时表空间过大撑爆了磁盘
临时表空间过大撑爆了磁盘导致MySQL异常终止。
ibtmp1增长的原因
ibtmp1增长主要与SQL有关，尤其是大量的分组聚合，排序，join查询SQL.通常如下情况会造成iptmp1上涨：
1.查询语句会先查询temp_table_size(内存分配)的量，当临时存储的量超过这个参数限制时，就会在iptmp1中申请占用空间。
2.select order group by GROUP BY 无索引字段或group by + order by 的子句字段不一样时。
3.select (select) 子查询
4.insert into select … from … 表数据复制
5.select union select 联合语句
解决办法
有以下3种解决办法，本文档采用办法1。
办法1：限制ibtmp1文件大小：innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:5G
办法2：优化SQL，避免使用临时表。
办法3：重启mysql实例释放ibtmp1文件
移动部分临时文件至空间大的目录
[root@sjjhpt182 data]# cd /topsoft/mysql/#innodb_temp

[root@sjjhpt182 #innodb_temp]# ls -lh temp_3.ibt
-rw-r----- 1 mysql mysql 728M Apr 18 21:10 temp_3.ibt
[root@sjjhpt182 #innodb_temp]# mv temp_3.ibt /data/temp_3.ibt
You have mail in /var/spool/mail/root

[root@sjjhpt182 #innodb_temp]# ls -lh temp_32.ibt
-rw-r----- 1 mysql mysql 472M Apr 19 00:14 temp_32.ibt
[root@sjjhpt182 #innodb_temp]# mv temp_32.ibt /data/temp_32.ibt
配置文件中增加限制全局临时表空间数据文件大小的参数
–查看配置文件中是否有临时表空间参数
[root@sjjhpt182 bin]# cat /etc/my.cnf | grep innodb_temp_data_file_path
无返回

–配置文件中增加临时表空间参数
vi /etc/my.cnf
[mysqld]
innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:1G
启动MySQL服务
–查看操作系统版本
[root@sjjhpt182 data]#cat /etc/redhat-release
CentOS Linux release 6.7.2009 (Core)

–启动MySQL服务
[root@sjjhpt182 data]# service mysqld start

–查看日志，启动成功
[root@sjjhpt182 ~]# tail -300f /var/log/mysqld.log
2024-04-19T02:09:53.036987Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.23) MySQL Community Server - GPL.
2024-04-19T02:16:45.230982Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.23) starting as process 38690
2024-04-19T02:16:45.239616Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-04-19T02:17:05.331871Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-04-19T02:17:05.683383Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: ‘::’ port: 33060, socket: /var/run/mysqld/mysqlx.sock
2024-04-19T02:17:11.696208Z 0 [System] [MY-010229] [Server] Starting XA crash recovery…
2024-04-19T02:17:11.702595Z 0 [System] [MY-010232] [Server] XA crash recovery finished.
2024-04-19T02:17:17.555459Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2024-04-19T02:17:17.555821Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2024-04-19T02:17:17.611985Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: ‘8.0.23’ socket: ‘/var/lib/mysql/mysql.sock’ port: 3306 MySQL Community Server - GPL.
观察目录大小
/topsoft目录空间已释放
[root@sjjhpt182 bin]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/VolGroup-lv_root
45G 12G 31G 28% /
tmpfs 63G 12K 63G 1% /dev/shm
/dev/vda1 477M 39M 413M 9% /boot
/dev/vdc 296G 201G 80G 72% /topsoft
/dev/vdd 296G 16G 265G 6% /tomcat-topsoft
/dev/mapper/lvm_data-vg_data
493G 1.3G 466G 1% /data
临时表空间的位置中内容
发现原来的文件已删除并重新进行了生成。
[root@sjjhpt182 bin]# cd /topsoft/mysql/#innodb_temp/
[root@sjjhpt182 #innodb_temp]# ls -lh
total 160K
-rw-r----- 1 mysql mysql 80K Apr 19 10:24 temp_10.ibt
-rw-r----- 1 mysql mysql 80K Apr 19 10:24 temp_1.ibt
-rw-r----- 1 mysql mysql 80K Apr 19 10:24 temp_2.ibt
-rw-r----- 1 mysql mysql 80K Apr 19 10:24 temp_3.ibt
-rw-r----- 1 mysql mysql 80K Apr 19 10:24 temp_4.ibt
-rw-r----- 1 mysql mysql 80K Apr 19 10:24 temp_5.ibt
-rw-r----- 1 mysql mysql 80K Apr 19 10:24 temp_6.ibt
-rw-r----- 1 mysql mysql 80K Apr 19 10:24 temp_7.ibt
-rw-r----- 1 mysql mysql 80K Apr 19 10:24 temp_8.ibt
-rw-r----- 1 mysql mysql 80K Apr 19 10:24 temp_9.ibt
查看限制全局临时表空间数据文件大小的参数
mysql> SELECT @@innodb_temp_data_file_path;
±--------------------------------+
| @@innodb_temp_data_file_path |
±--------------------------------+
| ibtmp1:12M:autoextend:max:1024M |
±--------------------------------+
1 row in set (0.00 sec)

知识点补充
临时表空间
InnoDB使用会话临时表空间和全局临时表空间。
会话临时表空间
会话临时表空间存储用户创建的临时表和优化器在InnoDB配置为磁盘内部临时表的存储引擎时创建的内部临时表。从 MySQL 8.0.16 开始，用于磁盘内部临时表的存储引擎是InnoDB. （以前，存储引擎是由internal_tmp_disk_storage_engine 的值决定的 ）。
会话临时表空间是在第一次请求创建磁盘临时表时从临时表空间池中分配给会话的。一个会话最多分配两个表空间，一个用于用户创建的临时表，另一个用于优化器创建的内部临时表。分配给会话的临时表空间用于会话创建的所有磁盘临时表。当会话断开连接时，其临时表空间将被截断并释放回池中。服务器启动时会创建一个包含 10 个临时表空间的池。池的大小永远不会缩小，表空间会根据需要自动添加到池中。临时表空间池在正常关闭或中止初始化时被删除。会话临时表空间文件在创建时大小为五页，并且有一个.ibt文件扩展名。
为会话临时表空间保留了 40 万个空间 ID 的范围。因为每次启动服务器时都会重新创建会话临时表空间池，所以会话临时表空间的空间 ID 在服务器关闭时不会持久保存，并且可以重复使用。
该innodb_temp_tablespaces_dir 变量定义创建会话临时表空间的位置。默认位置是 #innodb_temp数据目录中的目录。如果无法创建临时表空间池，则拒绝启动。
临时表释放后，空间会释放，但是磁盘空间不会释放，空闲空间可以被复用。释放磁盘空间只能重启。
$> cd BASEDIR/data/#innodb_temp
$> ls
temp_10.ibt temp_2.ibt temp_4.ibt temp_6.ibt temp_8.ibt
temp_1.ibt temp_3.ibt temp_5.ibt temp_7.ibt temp_9.ibt
在基于语句的复制 (SBR) 模式下，在副本上创建的临时表驻留在单个会话临时表空间中，该临时表空间仅在 MySQL 服务器关闭时被截断。
该INNODB_SESSION_TEMP_TABLESPACES 表提供有关会话临时表空间的元数据。
该 表提供有关在实例 INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO 中处于活动状态的用户创建的临时表的元数据。
全局临时表空间
全局临时表空间 ( ibtmp1) 存储回滚段，用于对用户创建的临时表所做的更改。
该innodb_temp_data_file_path 变量定义了全局临时表空间数据文件的相对路径、名称、大小和属性。如果没有为 指定值 ，则默认行为是创建一个在 目录中innodb_temp_data_file_path命名的单个自动扩展数据文件。初始文件大小略大于 12MB。 ibtmp1innodb_data_home_dir
全局临时表空间在正常关闭或中止初始化时被删除，并在每次启动服务器时重新创建。全局临时表空间在创建时接收动态生成的空间 ID。如果无法创建全局临时表空间，则拒绝启动。如果服务器意外停止，则不会删除全局临时表空间。在这种情况下，数据库管理员可以手动删除全局临时表空间或重新启动 MySQL 服务器。重新启动 MySQL 服务器会自动删除并重新创建全局临时表空间。
临时表释放后，空间会释放，但是磁盘空间不会释放，空闲空间可以被复用。释放磁盘空间只能重启。
全局临时表空间不能驻留在原始设备上。
查看全局临时表空间元数据
INFORMATION_SCHEMA.FILES提供有关全局临时表空间的元数据。发出类似于此的查询以查看全局临时表空间元数据：

mysql> SELECT * FROM INFORMATION_SCHEMA.FILES WHERE TABLESPACE_NAME=‘innodb_temporary’\G
默认情况下，全局临时表空间数据文件会自动扩展并根据需要增加大小，也就是说在支持大文件的系统这个文件大小是可以无限增长的。
全局临时表空间数据文件是否自动扩展
要确定全局临时表空间数据文件是否自动扩展，请检查 innodb_temp_data_file_path 设置：
mysql> SELECT @@innodb_temp_data_file_path;
±-----------------------------+
| @@innodb_temp_data_file_path |
±-----------------------------+
| ibtmp1:12M:autoextend |
要检查全局临时表空间数据文件的大小
要检查全局临时表空间数据文件的大小，请INFORMATION_SCHEMA.FILES 使用与此类似的查询来查询表：
mysql> SELECT FILE_NAME, TABLESPACE_NAME, ENGINE, INITIAL_SIZE, TOTAL_EXTENTS*EXTENT_SIZE
AS TotalSizeBytes, DATA_FREE, MAXIMUM_SIZE FROM INFORMATION_SCHEMA.FILES
WHERE TABLESPACE_NAME = ‘innodb_temporary’\G
*************************** 1. row ***************************
FILE_NAME: ./ibtmp1
TABLESPACE_NAME: innodb_temporary
ENGINE: InnoDB
INITIAL_SIZE: 12582912
TotalSizeBytes: 12582912
DATA_FREE: 6291456
MAXIMUM_SIZE: NULL

或
SELECT FILE_NAME, TABLESPACE_NAME, ENGINE, INITIAL_SIZE, TOTAL_EXTENTS*EXTENT_SIZE/1024/1024
AS TotalSize_MB, DATA_FREE/1024/1024 as FreeSize_MB, MAXIMUM_SIZE FROM INFORMATION_SCHEMA.FILES
WHERE TABLESPACE_NAME = ‘innodb_temporary’;
TotalSizeBytes显示全局临时表空间数据文件的当前大小。有关其他字段值的信息，请参阅 第 26.3.15 节，“INFORMATION_SCHEMA FILES 表”。
或者，检查操作系统上的全局临时表空间数据文件大小。全局临时表空间数据文件位于 innodb_temp_data_file_path 变量定义的目录中。
要回收全局临时表空间数据文件占用的磁盘空间，请重新启动 MySQL 服务器。重新启动服务器会根据 定义的属性删除并重新创建全局临时表空间数据文件 innodb_temp_data_file_path。
限制全局临时表空间数据文件的大小
要限制全局临时表空间数据文件的大小，请配置 innodb_temp_data_file_path以指定最大文件大小。例如：
[mysqld]
innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:500M
配置 innodb_temp_data_file_path 需要重新启动服务器。
临时表空间相关的参数
临时表空间相关的参数:tmp_table_size ；max_heap_table_size ；innodb_temp_data_file_path

参考链接：
https://blog.csdn.net/chenqiushi123/article/details/109578415
https://mysql.net.cn/doc/refman/8.0/en/innodb-temporary-tablespace.html#:~:text=%E6%88%96%E8%80%85%EF%BC%8C%E6%A3%80%E6%9F%A5%E6%93%8D%E4%BD%9C%E7%B3%BB%E7%BB%9F%E4%B8%8A%E7%9A%84%E5%85%A8%E5%B1%80%E4%B8%B4%E6%97%B6%E8%A1%A8%E7%A9%BA%E9%97%B4%E6%95%B0%E6%8D%AE%E6%96%87%E4%BB%B6%E5%A4%A7%E5%B0%8F%E3%80%82%20%E5%85%A8%E5%B1%80%E4%B8%B4%E6%97%B6%E8%A1%A8%E7%A9%BA%E9%97%B4%E6%95%B0%E6%8D%AE%E6%96%87%E4%BB%B6%E4%BD%8D%E4%BA%8E%20innodb_temp_data_file_path%20%E5%8F%98%E9%87%8F%E5%AE%9A%E4%B9%89%E7%9A%84%E7%9B%AE%E5%BD%95%E4%B8%AD%E3%80%82%20%E8%A6%81%E5%9B%9E%E6%94%B6%E5%85%A8%E5%B1%80%E4%B8%B4%E6%97%B6%E8%A1%A8%E7%A9%BA%E9%97%B4%E6%95%B0%E6%8D%AE%E6%96%87%E4%BB%B6%E5%8D%A0%E7%94%A8%E7%9A%84%E7%A3%81%E7%9B%98%E7%A9%BA%E9%97%B4%EF%BC%8C%E8%AF%B7%E9%87%8D%E6%96%B0%E5%90%AF%E5%8A%A8,MySQL%20%E6%9C%8D%E5%8A%A1%E5%99%A8%E3%80%82%20%E9%87%8D%E6%96%B0%E5%90%AF%E5%8A%A8%E6%9C%8D%E5%8A%A1%E5%99%A8%E4%BC%9A%E6%A0%B9%E6%8D%AE%20%E5%AE%9A%E4%B9%89%E7%9A%84%E5%B1%9E%E6%80%A7%E5%88%A0%E9%99%A4%E5%B9%B6%E9%87%8D%E6%96%B0%E5%88%9B%E5%BB%BA%E5%85%A8%E5%B1%80%E4%B8%B4%E6%97%B6%E8%A1%A8%E7%A9%BA%E9%97%B4%E6%95%B0%E6%8D%AE%E6%96%87%E4%BB%B6%20innodb_temp_data_file_path%20%E3%80%82
特别感谢中的