how to remove Mariadb temp files

Question

I have a /mariadb_tmp directory (as specified in my mariadb-server.cnf) which is filled with files dating back to last November.

-rw-rw---- 1 mysql mysql   1032192 Nov 19 07:47 #sql899_5d5_0.ibd
-rw-rw---- 1 mysql mysql    278528 Nov 19 09:30 #sql899_ab8f_0.ibd
-rw-rw---- 1 mysql mysql    278528 Nov 19 09:30 #sql899_ad17_0.ibd
-rw-rw---- 1 mysql mysql    638976 Nov 19 09:32 #sql899_baf3_0.ibd
-rw-rw---- 1 mysql mysql    409600 Nov 19 09:32 #sql899_bbec_0.ibd
-rw-rw---- 1 mysql mysql    262144 Nov 19 09:35 #sql899_c96a_0.ibd
...
-rw-rw---- 1 mysql mysql    704512 Jan 16 10:08 #sql899_603c0c4_1.ibd
-rw-rw---- 1 mysql mysql    540672 Jan 16 10:08 #sql899_603c123_0.ibd
-rw-rw---- 1 mysql mysql 494927872 Jan 16 10:20 #sql899_603eb7b_0.ibd
-rw-rw---- 1 mysql mysql    606208 Jan 16 10:27 #sql899_60470ff_1.ibd

Googling on 'delete old mariadb temp files' leads to advice such as:

drop table `#sql899_5d5_0`;

which results in the error 'no database selected.' Of course, I don't know what database each tmp files comes from, so I don't know if that would work.

There are no *.frm files to go with these, and they are in the specified tmpdir directory, not the datadir.

Any idea how to clean these up? They are taking up a bunch of space on a disk that we really can't expand (for non-technical reasons) nor should we have to keep these around that long.

Thanks,

Ed Greenberg

Answer 1

After checking they aren't attached to an active process, ls -la /proc/$(pidof mysqld)/fd, or fuser {filename}, they can be removed with rm.

From MariaDB-10.4 onwards you shouldn't see these created on disk again ( MDEV-15584).

Answer 2

I often use the following commands:

sudo find /tmp -type f -atime +10 -delete

OR

find /tmp/* -type d -mtime +5 -print0 | xargs -0 rm -rf