带有多个表的mysqldump，带或不带where子句 [英] mysqldump with multiple tables with or without where clause

问题描述

我的数据库中有一组表，我必须对它们进行转储(:D).我的问题是我想从某些表中获取一些数据，这些数据只能追溯到某些天，并且希望保持其余表的完好无损.

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.

我想到的查询是这样的:

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

上面的代码的麻烦在于table1，table2和table3将尝试采用table4的where子句.我不希望这种原因会吐出一个错误，即这些表中不存在已创建的字段.

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.

我尝试像在where子句之后一样在表名后添加逗号(，)，但这不起作用.

I tried putting comma (,) after table names as I did after where clause but it doesn't work.

在这一点上，我已经很困了，别无选择，只能创建两个我不想做的不同的sql dump文件.

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.

推荐答案

进行两个转储，或者如果您不想进行两个转储，则尝试两个命令

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.

gzip dumpfile.sql

这篇关于带有多个表的mysqldump，带或不带where子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！