Skip tables in mysqldump based on template

Question

Is there a way to restrict certain tables (i.e. start with the 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 that about 20 tables with a name start with 'test'. Is there a way to skip these tables (without using this long command like " --ignore-table=database.table1 --ignore-table=database.table2 --ignore-table=database.table3 .... --ignore-table=database.table20 "?

And is there a way to reset only the circuit, but there is no data?

Answer 1

Unfortunately mysqldump requires table names to be fully qualified, so you cannot specify a parameter as a regular expression pattern.

However, you can use the script to generate your mysqldump by connecting it to the info_scheme and listing 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 generating the --ignore-table options for all table names that match the regex ^test .

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

If you want to get everything for all tables that are not tested, but only a schema for another table, then you will need to use two separate mysqldump commands (one for the ignore table for all test tables plus a schema only one and the other for only the schema only the schema table), and the second one adds to the output file using >> append .

Thus, your script result may generate something like:

 mysqldump -u root -p toor databaseName --ignore-table=testTable1 --ignore-table=testTable2 --ignore-table=testTable3 --ignore-table=schemaOnlyTable > mysqldump.sql mysqldump -u root -p toor databaseName schemaOnlyTable --no-data=true >> mysqldump.sql