Huge #sql-xxxx_xxxx.ibd files in mysql-data folder

Solution 1:

The table files you are seeing are most likely resulting from an ALTER TABLE operation which could not be completed. The relevant portion of the MySQL docs says:

If MySQL crashes in the middle of an ALTER TABLE operation, you may end up with an orphaned temporary table inside the InnoDB tablespace. Using the Table Monitor, you can see listed a table with a name that begins with #sql-. You can perform SQL statements on tables whose name contains the character “#” if you enclose the name within backticks. Thus, you can drop such an orphaned table like any other orphaned table using the method described earlier. To copy or rename a file in the Unix shell, you need to put the file name in double quotation marks if the file name contains “#”.

so I would simply issue a DROP TABLE on them. Note: Do not simply delete the files - otherwise you will get so called orphaned tables yielding database engine warnings like these:

InnoDB: in InnoDB data dictionary has tablespace id N,
InnoDB: but tablespace with that id or name does not exist. Have
InnoDB: you deleted or moved .ibd files?
InnoDB: This may also be a table created with CREATE TEMPORARY TABLE
InnoDB: whose .ibd and .frm files MySQL automatically removed, but the
InnoDB: table still exists in the InnoDB internal data dictionary.

Solution 2:

i realized this can be temporary files.. the best would be if you could:

• dump your whole database

mysqldump -uuser -ppass database > file

• drop your database and re-create it from dump

cat file | mysql -uuser -ppass database

warning from the bottom still applies.

my initial answer:

do those names correspond to the names of your tables? most probably yes, if so - those are just the data files. innodb storage engine [ibd extension suggests you use it] does not shrink the data files ever. the only way to get them smaller is either to:

• dump each table and re-create it:

mysqldump -uuser -ppass database tablename > file ; cat file | mysql -uuser -ppass database

• run "optimize table tableName;" in mysql for each of the 'big' tables

warning - both operations will take a lot of time [very much depends on number of indices in the tables, your io subsystem; most probably we're talking about hours of time if not more]; will block reads. do not run this during production hours.