MySQL 带有或不带有where子句的多个表的mysqldump
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/32368101/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
mysqldump with multiple tables with or without where clause
提问by Sworup Shakya
I have a set of tables in my database that I have to take a dump ( :D ) of. My problem is I want to take some data from some tables that only date back certain days and would like to keep the remaining tables in tact.
我的数据库中有一组表，我必须转储（ :D ）。我的问题是我想从某些表中获取一些数据，这些数据只能追溯到某些日期，并希望保持其余表的完整性。
The query I came up with was something like:
我想出的查询是这样的：
mysqldump -h<hostname> -u<username> -p <databasename>
<table1> <table2> <table3>
<table4> --where 'created > DATE_SUB(now(), INTERVAL 7 DAY)',
<table5> --where 'created > DATE_SUB(now(), INTERVAL 7 DAY)
--single-transaction --no-create-info | gzip
> $(date +%Y-%m-%d-%H)-dump.sql.gz
The trouble with the above code is that table1, table2 and table3 will try to take the where clause of table4. I don't want that cause that would spit out an error that created field does not exist in these tables.
上面代码的问题是table1、table2和table3会尝试取table4的where子句。我不希望这个原因会吐出一个错误，即这些表中不存在创建的字段。
I tried putting comma (,) after table names as I did after where clause but it doesn't work.
我尝试在表名之后加上逗号 (,)，就像在 where 子句之后一样，但它不起作用。
At this point I'm pretty much stuck and have no more alternative expect create two different sql dump files, which I wouldn't want to do.
在这一点上，我几乎被卡住了，没有更多的选择，希望创建两个不同的 sql 转储文件，这是我不想做的。
回答by Sandeep Maharjan
make two dumps or if you dont want to make two dumps then try two command
进行两次转储，或者如果您不想进行两次转储，请尝试使用两个命令
a.
一种。
mysqldump -h<hostname> -u<username> -p
<databasename> <table1> <table2> <table3>
--single-transaction --no-create-info > dumpfile.sql
b.
湾
mysqldump -h<hostname> -u<username> -p <databasename>
<table4> --where 'created > DATE_SUB(now(), INTERVAL 7 DAY)',
<table5> --where 'created > DATE_SUB(now(), INTERVAL 7 DAY)
--single-transaction --no-create-info >> dumpfile.sql
c.
C。
gzip dumpfile.sql
回答by James
So the solution above won't work unless the tables have a common foreign key field.
因此，除非表具有公共外键字段，否则上述解决方案将不起作用。
If you look at my example below, the user_addresses, user_groups, and user_payment_methods all have the user_id field i common. When mysqldumpexecutes the where clause it will filter those tables.
如果你看我下面的例子，user_addresses、user_groups 和 user_payment_methods 都有我常用的 user_id 字段。当mysqldump执行 where 子句时，它将过滤这些表。
mysqldump -u <username> -p <password>
user_addresses user_groups user_payment_methods
-w "user_id
in (select id from users where email like '%@domain.com')"
--single-transaction| gzip > sqldump.sql.gz
