blocks|key|272379|text|mysqldump+-uroot+-p+db_name+table_name+--where='id<1000000'|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|272380|或者您可以使用|unstyled|272381|SELECT+*+INTO+OUTFILE+'data_path.sql'+from+table+where+id<100000|272382|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|L|8|@]|9|@]|A|$]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$B|C]]|$1|I|3|-4|5|F|7|N|8|@]|9|@]|A|$]]]|J|$]]

<pre class="lang-sh prettyprint-override"><code>mysqldump -uroot -p db_name table_name --where='id&lt;1000000'
</code></pre>

or you can use

<pre class="lang-sql prettyprint-override"><code>SELECT * INTO OUTFILE 'data_path.sql' from table where id&lt;100000
</code></pre>

blocks|key|270353|text|mysqldump+--skip-triggers+--compact+--no-create-info+--user=USER+--password=PASSWORD+-B+DATABASE+--tables+MY_TABLE+--where='SOME_COLUMN>=xxxx'+>+out.sql|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|270354|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>mysqldump --skip-triggers --compact --no-create-info --user=USER --password=PASSWORD -B DATABASE --tables MY_TABLE --where='SOME_COLUMN&gt;=xxxx' &gt; out.sql
</code></pre>

blocks|key|2974089|text|转储的文件与您使用的SQL+select文件不同。对于第二种方法，您不能简单地使用:+mysql+database+<+table将表转储到数据库中。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2974090|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

The file dumped is different from the file you use SQL select. 
For the 2nd approach, you can not simply use: mysql database &lt; table 
to dump the table into a database.

blocks|key|275897|text|在我的例子中，我执行了以下命令：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|275898|SELECT+*+
++INTO+OUTFILE+'C:\Documents+and+Settings\Anton.Zarkov\Desktop\joomla_export\data_AZ.sql'
++FROM+`jos_glossary`
+WHERE+id>6000|code-block|syntax|javascript|275899|275900|没有语法错误-查询通过。|unordered-list-item|275901|结果为NULL+-未写入任何行。(我确定-最后一个ID是6458)如果我重复查询an+error+occurs+=>+#1086+-+File+'C:Documents+and+SettingsAnton.ZarkovDesktopjoomla_exportdata_AZ.sql'+already+exists|ordered-list-item|offset|length|style|CODE|275902|Unfortunately，在磁盘C上的任何地方都找不到“现有”文件。其中是it?|275903|275904|+|275905|275906|275907|​|275908|条件是:+PHP+phpMyAdmin+SQL+Dump；版本3.4.5；主机:本地主机；服务器版本:+5.5.16；版本:+5.3.8|BOLD|275909|entityMap^0|0|0|0|1|3|4|15|37|1|1|0|0|0|0|0|1N|9|0^^$0|@$1|2|3|4|5|6|7|15|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|16|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|17|8|@]|9|@]|A|$]]|$1|H|3|I|5|J|7|18|8|@]|9|@]|A|$]]|$1|K|3|L|5|M|7|19|8|@$N|1A|O|1B|P|Q]|$N|1C|O|1D|P|Q]]|9|@]|A|$]]|$1|R|3|S|5|M|7|1E|8|@]|9|@]|A|$]]|$1|T|3|-4|5|6|7|1F|8|@]|9|@]|A|$]]|$1|U|3|V|5|6|7|1G|8|@]|9|@]|A|$]]|$1|W|3|-4|5|J|7|1H|8|@]|9|@]|A|$]]|$1|X|3|-4|5|6|7|1I|8|@]|9|@]|A|$]]|$1|Y|3|Z|5|6|7|1J|8|@]|9|@]|A|$]]|$1|10|3|11|5|6|7|1K|8|@$N|1L|O|1M|P|12]]|9|@]|A|$]]|$1|13|3|-4|5|6|7|1N|8|@]|9|@]|A|$]]]|14|$]]

In my case i have execute this:

<pre><code>SELECT * 
 INTO OUTFILE 'C:\Documents and Settings\Anton.Zarkov\Desktop\joomla_export\data_AZ.sql'
 FROM `jos_glossary`
 WHERE id&gt;6000
</code></pre>

<ul>
<li>there is no syntax error - the query passes through.
<ol>
<li>The result is <code>NULL</code> - no rows were written. (I'm sure - the last ID is 6458)</li>
<li>If I repeat the query a<code>n error occurs =&gt; #1086 - File 'C:Documents and SettingsAnton.ZarkovDesktopjoomla_exportdata_AZ.sql' already exists</code></li>
<li>Unfortunately I cannot find the "existing" file anywhere on disk C. Where is it?</li>
</ol></li>
</ul>

The conditions are:
phpMyAdmin SQL Dump; version 3.4.5; host: localhost; server version: 5.5.16; PHP version: 5.3.8

blocks|key|274006|text|mysqldump+-uroot+-p+db_name+table_name+--where'id<1000000'+>+yourdumpname.sql|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|274007|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>mysqldump -uroot -p db_name table_name --where'id&lt;1000000' &gt; yourdumpname.sql
</code></pre>

blocks|key|270369|text|下面的查询是从id范围中选择，您可以使用date_created或any代替id|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|270370|mysqldump+--opt+--host=dns+--user=user_name+--password=your_passwd+db_name+--tables+table_name++--where+"id+>+1+and+id+<+100+"+>+/file_name.sql|code-block|syntax|javascript|270371|例如：--where="date_created+>+'2019-01-18'+"+-->+insted+of+id|CODE|270372|entityMap^0|K|C|12|2|0|0|3|12|1K|2|0^^$0|@$1|2|3|4|5|6|7|P|8|@$9|Q|A|R|B|C]|$9|S|A|T|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|U|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|V|8|@$9|W|A|X|B|M]|$9|Y|A|Z|B|C]]|D|@]|E|$]]|$1|N|3|-4|5|6|7|10|8|@]|D|@]|E|$]]]|O|$]]

Below query is to select from id range you can use date_created or any instead of id 

<pre><code>mysqldump --opt --host=dns --user=user_name --password=your_passwd db_name --tables table_name --where "id &gt; 1 and id &lt; 100 " &gt; /file_name.sql
</code></pre>

ex: <code>--where="date_created &gt; '2019-01-18' "</code> --> insted of id

blocks|key|3187924|text|这个问题一如既往地流行，大多数人会发现这类问题，因为他们受累于mysql和mysqldump的单线程设计。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3187925|如果您有数百万或数十亿行，导出可能需要几天(到几周)，因此最终只能导出部分数据。|3187926|解决这个问题的一个快速方法是导出部分数据，如果你有一个数字键(比如自动增量id)，这个方法效果最好。|3187927|下面是一个linux/unix示例，说明如何以比正常速度快20-100倍的速度粗略地导出一个表。|3187928|Assumed+column+"id"+is+from+1+to+10000000
Assumed+cpu+has+16+threads
Assumed+disk+is+an+ssd+or+nvme
seq+0+1000+%7C+xargs+-n1+-P16+-I{}+%7C+mysqldump+-h+localhost+--password=PASSWORD+--single-transaction+DATABASE+TABLE+--where+"id+>+{}*10000+AND+id+<+{}*10000%2B10000"+-r+output.{}|code-block|syntax|javascript|3187929|上面的代码将运行16个线程，大致将导出时间缩短到正常的1/10。它创建了16个文件，这些文件也可以并行加载，这使得加载速度提高了10倍。|3187930|在一个强大的服务器上，我使用多达150个并行线程，这取决于您正在运行的磁盘和cpu的类型。|3187931|这种方法稍加改进，可以将一周出口的加载或导出时间缩短到几个小时。|3187932|在网络上执行此操作时--compress会有很大帮助，忽略insert语句也会有助于处理mysql索引错误，这些索引在大数据上是无法避免的。使用'mysql+-f‘加载数据进一步有助于避免在这种情况下停止。|3187933|附注:永远不要在大型表的末尾使用mysql选项添加索引和键。|3187934|entityMap^0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Y|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Z|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|10|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|11|8|@]|9|@]|A|$]]|$1|H|3|I|5|J|7|12|8|@]|9|@]|A|$K|L]]|$1|M|3|N|5|6|7|13|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|14|8|@]|9|@]|A|$]]|$1|Q|3|R|5|6|7|15|8|@]|9|@]|A|$]]|$1|S|3|T|5|6|7|16|8|@]|9|@]|A|$]]|$1|U|3|V|5|6|7|17|8|@]|9|@]|A|$]]|$1|W|3|-4|5|6|7|18|8|@]|9|@]|A|$]]]|X|$]]

The question is current as ever, most people will find these sort of questions because they suffer from the single-threaded design of mysql and mysqldump. 
If you have millions or billions of rows exporting can take days (to weeks) so you end up only exporting parts of the data instead.
A quick hack to solve this is to export portions of the data, this works best if you have a numeric key (like an autoincrement id). 
Below is a linux/unix example on how to export a table rougly 20-100 times faster than normal.
<pre><code>Assumed column &quot;id&quot; is from 1 to 10000000
Assumed cpu has 16 threads
Assumed disk is an ssd or nvme
seq 0 1000 | xargs -n1 -P16 -I{} | mysqldump -h localhost --password=PASSWORD --single-transaction DATABASE TABLE --where &quot;id &gt; {}*10000 AND id &lt; {}*10000+10000&quot; -r output.{}
</code></pre>
The above code will run 16 threads, roughly cutting time to export to 1/10 of normal. It creates 16 files that also can be loaded in parallel which speeds up loading up to 10 times. 
On a strong server I use up to 150 parallel threads, this depends on the type of disk and cpu you are running. 
This method, a bit refined, can cut the loading or export of a 1 week export to a few hours.
When doing this over network --compress can help a lot, also ignore insert statements will help with faulty mysql indexes that are not avoidable on large data. loading data with 'mysql -f' further helps to avoid stopping in such cases.
P.S. never use the mysql options to add indexes and keys at the end on large tables.

So I can export only a table like this:

<pre><code>mysqldump -u root -p db_name table_name &gt; table_name.sql
</code></pre>

Is there any way to export only a portion of a table with mysqldump? For example, 0 - 1,000,000 rows, 1,000,000 - 2,000,000 rows, etc.

Should I do this with mysqldump or a query?

How to use mysqldump for a portion of a table?

数据库

大数据

服务器

所以我只能导出一个这样的表：mysqldump -u root -p db_name table_name > table_name.sql有没有办法用mysqldump只导出表的一部分？例如，0- 1,000,000行，1,000,000 - 2,000,000行等。我应该用、mysqldump、还是查询来做这件事？

问如何将mysqldump用于表的一部分？
EN

回答 7

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何将mysqldump用于表的一部分？EN

回答 7

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何将mysqldump用于表的一部分？
EN