MySQL Bugs: #42774: mysqldump command line switch to ignore temporary tables

Bug #42774	mysqldump command line switch to ignore temporary tables
Submitted:	11 Feb 2009 20:05	Modified:	12 Feb 2009 11:53
Reporter:	Daevid Vincent	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: mysqldump Command-line Client	Severity:	S4 (Feature request)
Version:	5.0.51a	OS:	Linux (Ubuntu)
Assigned to:		CPU Architecture:	Any
Tags:	IGNORE, mysqldump, temporary

Description:
We have a live database with tables that are over 500M rows. We cannot take the server down to do a backup, or in this case to delete temporary tables. However we use temporary tables constantly to take offloads (in SQL format, create a temp database/tables, then normalize and re-insert that offload data into our main database). This happens every minute or so and is increasing as we expand. 

We would like a way to ignore temp tables from mysqldump's command line.

/*!40000 ALTER TABLE `offload_temp_1d071827a37c402d8294d4ee65d86e4d` DISABLE KEYS */;
/*!40000 ALTER TABLE `offload_temp_1d071827a37c402d8294d4ee65d86e4d` ENABLE KEYS */;
/*!40000 ALTER TABLE `offload_temp_55cd7b89c3def439a9727821717428ec` DISABLE KEYS */;
/*!40000 ALTER TABLE `offload_temp_55cd7b89c3def439a9727821717428ec` ENABLE KEYS */;

So as you can see, we end up with HUGE amounts of temporary tables being backed up and then on occasion re-imported. Then we don't know which temp tables are stale vs. active (the active ones do remove themselves, but our naming convention doesn't have dates)

How to repeat:
mysqldump --opts to dump out our entire database.

Suggested fix:
mysqldump --ignore-temp-tables

Many thanks for writing a bug report.

Changed category because mysqldump is CLI.

Verified as described.

The problem is related to general indexing.