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?

Answer 1

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