0

Wondering if there is a way to skip / ignore all temp tables using mysqldump. In our instance, these tables are prefixed as tmp{guid}.

These temp tables have a very short lifespan, they are used for building some sort of reports in its parent application. Lifetime may be up to 1 minute.

EDIT: It has been suggested that I use the ignore-tables parameter, unfortunately this doesn't provide a way for me to specify a wildcard as the table name (tmp*).

Tom Dickson
  • 540
  • 6
  • 19
  • Does this answer your question? [Skip certain tables with mysqldump](https://stackoverflow.com/questions/425158/skip-certain-tables-with-mysqldump) – nbk Feb 03 '20 at 00:26
  • @nbk not really, the ignore-table doesn't support wildcard names like `tmp*` due to the table names being unique. – Tom Dickson Feb 03 '20 at 01:28
  • see mysqldump can't do wildcards, so you have have an language that builds the ignore tables string. Information_schema give you all the temo tables and then build the string. if you have linux you can use https://github.com/maxbube/mydumper/blob/master/docs/mydumper_usage.rst#id3 – nbk Feb 03 '20 at 01:32
  • @nbk thanks for the suggestion but I want to stick to the official tool for the time being (doesn't look like there is much dev being done on dumper at the moment). If I was to build the string and one of the tables no longer exist would it error out or would it silently continue? – Tom Dickson Feb 03 '20 at 02:10
  • @Tom - Hope this helps -> https://stackoverflow.com/questions/25398663/skip-tables-in-mysqldump-based-on-a-pattern – S B Feb 03 '20 at 04:11

1 Answers1

1

You are not talking about tables from CREATE TEMPORARY TABLE ..., correct? Instead, you are talking about a set of tables with a particular naming convention?

Instead of trying to do it with table names, do it with a DATABASE:

CREATE TABLE TempTables;
CREATE TABLE TempTables.abcd (...);

And reference them via the db name:

INSERT INTO TempTables.abcd ...
SELECT ... FROM TempTables.abcd JOIN ...

Then use the suitable parameters on mysqldump to avoid that oneDATABASE` (or pick all the other databases to dump).

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • Not sure if I have misunderstood your solution but I am not building the database, only backing it up. – Tom Dickson Feb 03 '20 at 10:08
  • @TomDickson - I am suggesting a work-around -- put just your "temp" tables in a different database in order to make the selective backup possible. – Rick James Feb 03 '20 at 16:33
  • These temp tables have a very short lifespan, they are used for building some sort of reports in its parent application. Lifetime may be up to 1 minute. – Tom Dickson Feb 03 '20 at 23:56
  • @TomDickson - Is there a big concern about accidentally backing a few of them up? – Rick James Feb 04 '20 at 00:43