Lots of MySQL files starting with #

[Kent Brockman]

Hello friends. Today something caught my attention because a database where phpmyadmin reports as using barely 500 MB, is shown in cPanel as consuming 9 GB...

When I browse to the folder (/var/lib/mysql/dbname) I can see lots of numbered files with extension FRM and IBD with different sizes, from 8 MB to 420 MB, and all of their filenames starting with a #.

As far as I can see, the date of those files could match events in which mysqld just went down due to high cpu usage.

The current databases work as normal, no data losses, and no evident proof of failure. (It's a high traffic, very huge ecommerce wordpress site with thousands of products being converted into HTML cache daily).

So, the questions are:
- What are those files? I attached a screen cap below.
- Can I safely delete them? There are files from nearly Feb 2014, using a total of almost 6 GB.

[email protected] [/var/lib/mysql/mydbname]# dir \#sql-* -hlt
-rw-rw---- 1 mysql mysql 316M Apr 14 05:13 #sql-6141_2257.ibd
-rw-rw---- 1 mysql mysql 8.5K Apr 14 04:59 #sql-6141_2257.frm
-rw-rw---- 1 mysql mysql 416M Apr 13 05:23 #sql-73f8_4429.ibd
-rw-rw---- 1 mysql mysql 8.5K Apr 13 04:59 #sql-73f8_4429.frm
-rw-rw---- 1 mysql mysql 424M Apr 12 05:12 #sql-56fc_4445.ibd
-rw-rw---- 1 mysql mysql 8.5K Apr 12 04:59 #sql-56fc_4445.frm
-rw-rw---- 1 mysql mysql 368M Apr 10 05:15 #sql-cd8_58d4.ibd
-rw-rw---- 1 mysql mysql 8.5K Apr 10 04:59 #sql-cd8_58d4.frm
-rw-rw---- 1 mysql mysql 296M Apr  8 05:29 #sql-47d_2ab2.ibd
-rw-rw---- 1 mysql mysql 8.5K Apr  8 05:15 #sql-47d_2ab2.frm
-rw-rw---- 1 mysql mysql 404M Apr  7 05:35 #sql-7ef_1c00.ibd
-rw-rw---- 1 mysql mysql 8.5K Apr  7 05:14 #sql-7ef_1c00.frm
-rw-rw---- 1 mysql mysql 280M Apr  6 05:24 #sql-41fb_5c55.ibd
-rw-rw---- 1 mysql mysql 8.5K Apr  6 05:16 #sql-41fb_5c55.frm
-rw-rw---- 1 mysql mysql 360M Apr  5 05:25 #sql-19ea_5bee.ibd
-rw-rw---- 1 mysql mysql 8.5K Apr  5 05:13 #sql-19ea_5bee.frm
-rw-rw---- 1 mysql mysql 328M Apr  4 05:24 #sql-60e0_6540.ibd
-rw-rw---- 1 mysql mysql 8.5K Apr  4 05:12 #sql-60e0_6540.frm
-rw-rw---- 1 mysql mysql 388M Apr  3 05:37 #sql-1221_72bc.ibd
-rw-rw---- 1 mysql mysql 8.5K Apr  3 05:18 #sql-1221_72bc.frm
-rw-rw---- 1 mysql mysql 300M Apr  2 05:24 #sql-73b1_f999.ibd
-rw-rw---- 1 mysql mysql 8.5K Apr  2 05:14 #sql-73b1_f999.frm
-rw-rw---- 1 mysql mysql 340M Mar 31 05:32 #sql-1358_8b0e.ibd
-rw-rw---- 1 mysql mysql 8.5K Mar 31 05:20 #sql-1358_8b0e.frm
-rw-rw---- 1 mysql mysql 308M Mar 30 05:23 #sql-491b_7a85.ibd
-rw-rw---- 1 mysql mysql 8.5K Mar 30 05:10 #sql-491b_7a85.frm
-rw-rw---- 1 mysql mysql 376M Mar 29 05:34 #sql-624_6702.ibd
-rw-rw---- 1 mysql mysql 8.5K Mar 29 05:13 #sql-624_6702.frm
-rw-rw---- 1 mysql mysql 336M Mar 28 05:38 #sql-2d45_7c1e.ibd
-rw-rw---- 1 mysql mysql 8.5K Mar 28 05:24 #sql-2d45_7c1e.frm
-rw-rw---- 1 mysql mysql 372M Mar 27 05:37 #sql-5851_6438.ibd
-rw-rw---- 1 mysql mysql 8.5K Mar 27 05:21 #sql-5851_6438.frm
-rw-rw---- 1 mysql mysql 260M Mar 26 05:35 #sql-6828_8ecc.ibd
-rw-rw---- 1 mysql mysql 8.5K Mar 26 05:27 #sql-6828_8ecc.frm
-rw-rw---- 1 mysql mysql  10M Mar 25 05:28 #sql-14f4_7436.ibd
-rw-rw---- 1 mysql mysql 8.5K Mar 25 05:28 #sql-14f4_7436.frm
-rw-rw---- 1 mysql mysql 720K Mar 24 05:38 #sql-47d_1d2c.ibd
-rw-rw---- 1 mysql mysql 8.5K Mar 24 05:38 #sql-47d_1d2c.frm
-rw-rw---- 1 mysql mysql 328M Mar 23 05:34 #sql-7c66_143b7.ibd
-rw-rw---- 1 mysql mysql 8.5K Mar 23 05:21 #sql-7c66_143b7.frm
-rw-rw---- 1 mysql mysql 268M Mar 15 05:29 #sql-789e_79cb.ibd
-rw-rw---- 1 mysql mysql 8.5K Mar 15 05:20 #sql-789e_79cb.frm
-rw-rw---- 1 mysql mysql 392M Mar 13 05:26 #sql-1eda_677c.ibd
-rw-rw---- 1 mysql mysql 8.5K Mar 13 05:13 #sql-1eda_677c.frm
-rw-rw---- 1 mysql mysql 348M Mar 12 05:26 #sql-8b5_3506.ibd
-rw-rw---- 1 mysql mysql 8.5K Mar 12 05:12 #sql-8b5_3506.frm
-rw-rw---- 1 mysql mysql  36M Mar 11 05:29 #sql-404e_3d4e.ibd
-rw-rw---- 1 mysql mysql 8.5K Mar 11 05:27 #sql-404e_3d4e.frm
-rw-rw---- 1 mysql mysql 928K Sep 30  2014 #sql-3c60_1d.ibd
-rw-rw---- 1 mysql mysql 8.5K Sep 30  2014 #sql-3c60_1d.frm

Any ideas?

 

[cPanelMichael]

Hello,

This is likely related to the following statement documented here:

If MySQL crashes in the middle of an ALTER TABLE operation, you may be left with an orphaned intermediate table. Intermediate table names begin with“#sql-”. In your data directory you will see an #sql-*.ibd file and an accompanying#sql-*.frm file with the same name

The referenced document further explains how to address the issue.

Thank you.

 

[Kent Brockman]

Very interesting situation. Thank you for the link, I'll have to read it thoroughly.