Create temp file delete for MySql

I get temp files created in my database and i'd like a quick and easy code to delete these, also a friend who is running the same d/base has never deleted their and has over 8000!

Could the code below be changed to a wildcard to delete any tables that are:
default_temp_*

I also have a table called default_temp which cannot be deleted! So it has to be anything called default_temp_

Thanks Ian

DROP TABLE `default_temp_6bb2824c646b02eca19751294f70cabe` ,
`default_temp_33cdb9e7da8954e74aaefe0ca0669ee6` ,
`default_temp_86e8a587156040c985c6890055feea4d` ,
`default_temp_209b67489cfc5f9dc13ebc2c4e77edb6` ,
`default_temp_84766867ba2a5127c18ba90dff0f9fb4`  ;

Code (markup):

 

You could use a simple script that does a "SHOW TABLES" sql-command and then issues a DROP TABLE for each table that matches the pattern.

 

I've never tried it with a table name but maybe doing something like DROP TABLE LIKE 'default_temp_%' ?

 

Unfortunately (?) DROP TABLE doesn't work with wildcards. But if you have obtained a list of tables using SHOW TABLES LIKE they could all be deleted with one DROP TABLE command:

DROP TABLE table1, table2, table3...

Code (markup):

The complete reference on DROP TABLE can be found here:
http://dev.mysql.com/doc/refman/4.1/en/drop-table.html

 

Potentially very dangerous scriptlet you can run from the command line:
(This is all one line, replace yourpass with your mysql root password, yourdatabase with your database name).

PASSWORD=yourpass; DATABASE=yourdatabase; for table in `mysql -u root -p$PASSWORD -se "show tables;" $DATABASE | grep -v Tables_in_$DATABASE | grep '_temp_'`; do mysql -u root -p$PASSWORD -e "DROP table $table;" $DATABASE; done

Make sure you backup your database first

 

Thanks.....still working on this as the 'host' has come back and said the cpanel backup won't back up due to the size of the d/b (eg all the temp tables) - what crap, so as it's not my d/b I'm not keen on the 'dangerous script'!

I'll let you know how I get on

 

If cpanel can't backup do it yourself from the command line:

mysqldump -u root -p -A | gzip -c9 > mysqlbackup-`date +%F`.gz

 

When u say command line do you mean in shell access mode? Which she doesn't have...

I've done show tables and then done this:

DROP TABLE 'default_0016d78728f671e6e7a617d5e0b06a85temp','default_01635d8a30e1ecf2ff7c242c7493aba0temp',   'default_0ff4e7fa08c0f97e93a35690e669d032temp';

Code (markup):

etc....

And I get this error:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''default_0016d78728f671e6e7a617d5e0b06a85temp','default_001a8ca4a205bdb34365d81f' at line 1

 

The mysqldump-command has to be issued from the command line, not the sql shell. If you don't have ssh access this won't be easy.

The DROP TABLE command should work if you omit the quotes.

 

BINGO....removing the quotes worked......now deleted the 8000 temp tables, hope she's grateful!

Thanks a lot everyone.

Ian