根据模式跳过mysqldump中的表 [英] Skip tables in mysqldump based on a pattern

问题描述

是否可以通过mysqldump命令限制某些表(即以名称"test"开头)?

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

但是问题是，大约有20个表的名称以'test'开头.有什么方法可以跳过这些表(不使用像"--ignore-table=database.table1 --ignore-table=database.table2 --ignore-table=database.table3 .... --ignore-table=database.table20"这样的长命令?

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?

推荐答案

不幸的是

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

但是，您可以使用脚本通过将其连接到information_schema并使用以下命令列出所有表来生成mysqldump:

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');

然后让它生成 --ignore-table 所有与^test的正则表达式匹配的表名的参数.

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.

如果要获取所有非测试表的所有内容，而仅获取另一表的架构的所有内容，则需要使用两个单独的mysqldump命令(一个用于所有测试表的ignore-table命令，以及仅用于架构的一个和另一个仅适用于仅模式"表的模式)，第二个通过使用>> 运算符.

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

这篇关于根据模式跳过mysqldump中的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！