Skip tables in mysqldump based on a pattern

Question

Is there a way to restrict certain tables (ie. start with name 'test') from the mysqldump command?

mysqldump -u username -p database \
  --ignore-table=database.table1  \
  --ignore-table=database.table2 etc > database.sql

But the problem is, there is around 20 tables with name start with 'test'. Is there any way to skip these tables(without using these long command like "--ignore-table=database.table1 --ignore-table=database.table2 --ignore-table=database.table3 .... --ignore-table=database.table20"?

And is there any way to dump only schema but no data?

Consider creating a script to generate said command - it can query the information schema for the table names. The --ignore-table option, however, requires an exact name. — user2864740
my problem is, I cant give like --ignore-table=database.table1 ... --ignore-table=database.table20 . is there any way to ingore the tables with name start with 'test' string? — Futuregeek
Have you considered just running a simple dump and then using regex to remove the bits you don't want? — ydaetskcoR
No, since those tables have large amount of data, I need to remove them at the time of dump itself. — Futuregeek

ydaetskcoR ydaetskcoR · Accepted Answer · 2014-08-22T09:13:15

Unfortunately mysqldump requires table names to be fully qualified so you can't specify a parameter as a regex pattern.

You could, however, use a script to generate your mysqldump by having it connect to the information_schema and list all the tables using something like:

SELECT TABLE_NAME, TABLE_SCHEMA
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA NOT IN ('INFORMATION_SCHEMA', 'mysql', 'PERFORMANCE_SCHEMA');

And then having it generate --ignore-table parameters for all table names that match the regex of ^test.

To dump only the schema and no data you can use --no-data=true as a parameter.

If you want to get everything for all of the non test tables but only the schema for another table then you would need to use two separate mysqldump commands (one for the ignore-table for all test tables plus the schema only one and another for only the schema of the schema only table) with the second one appending to the output file by using the >> append operator.

So your resulting script might generate something like:

mysqldump -u root -ptoor databaseName --ignore-table=testTable1 --ignore-table=testTable2 --ignore-table=testTable3  --ignore-table=schemaOnlyTable > mysqldump.sql

mysqldump -u root -ptoor databaseName schemaOnlyTable --no-data=true >> mysqldump.sql

Skip tables in mysqldump based on a pattern

1 Answers