blocks|key|2780315|text|我建议你试试负载数据信息。它非常快。我没有使用它加载到远程服务器，但有mysqlimport实用程序。参见不同方法的比较：https://dev.mysql.com/doc/refman/5.5/en/insert-speed.html。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|2780316|此外，还需要将sql脚本转换为适合LOAD+DATA+INFILE子句的格式。|style|CODE|2780317|entityMap|0|LINK|mutability|MUTABLE|url|https://dev.mysql.com/doc/refman/5.5/en/load-data.html|1|https://dev.mysql.com/doc/refman/5.5/en/mysqlimport.html|2|https://dev.mysql.com/doc/refman/5.5/en/insert-speed.html^0|6|6|0|Z|B|1|1P|1L|2|0|H|G|0^^$0|@$1|2|3|4|5|6|7|T|8|@]|9|@$A|U|B|V|1|W]|$A|X|B|Y|1|Z]|$A|10|B|11|1|12]]|C|$]]|$1|D|3|E|5|6|7|13|8|@$A|14|B|15|F|G]]|9|@]|C|$]]|$1|H|3|-4|5|6|7|16|8|@]|9|@]|C|$]]]|I|$J|$5|K|L|M|C|$N|O]]|P|$5|K|L|M|C|$N|Q]]|R|$5|K|L|M|C|$N|S]]]]

I suggest you to try <a href="https://dev.mysql.com/doc/refman/5.5/en/load-data.html" rel="nofollow">LOAD DATA INFILE</a>. It is extremely fast. I've not used it for loading to remote server, but there is <a href="https://dev.mysql.com/doc/refman/5.5/en/mysqlimport.html" rel="nofollow">mysqlimport</a> utility. See a comparison of different approaches: <a href="https://dev.mysql.com/doc/refman/5.5/en/insert-speed.html" rel="nofollow">https://dev.mysql.com/doc/refman/5.5/en/insert-speed.html</a>.

Also you need to convert your sql script to format suitable for <code>LOAD DATA INFILE</code> clause.

blocks|key|434859|text|您可以使用shell脚本将sql文件分解为多个文件(基于表)&然后一个接一个地准备一个shell脚本来导入该文件。这将迅速插入，而不是一次进入。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|434860|原因是插入的记录占用了单个进程在内存中的空间，而不是删除。您可以看到，在5小时后导入脚本时，查询执行速度将很慢。|434861|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

You can break the sql file into several files (on basis of tables) by using shell script &amp; then prepare a shell script to one by one to import the file. This would speedy insert instead of one go. 

The reason is that inserted records occupied space in memory for a single process and not remove. You can see when you are importing script after 5 hours the query execution speed would be slow.

blocks|key|2780414|text|谢谢你们的帮助。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2780415|我已经采纳了你的一些建议，并对此做了一些比较，现在是时候发布结果了。目标单一sql脚本15+The。|2780416|总的来说，我试过：|2780417|将数据导入为带有索引的单一sql脚本；(以Days为例，我最终将其终止。不要自己动手，你会生气的。)|ordered-list-item|offset|length|style|BOLD|2780418|将数据导入为没有索引的单一sql脚本；(与上面相同)|2780419|将数据导入为带有索引的split脚本(以单个sql为例，我将大文件分成大约41+an的小块)。每个树干约2m19.586s，总周长)；|2780420|在没有索引的情况下，以script的形式导入数据；(每个主干采用2m9.326+s)。|2780421|(不幸的是，我没有为这个数据集尝试加载数据方法)|2780422|结论：|2780423|如果您不想使用Load数据方法，则必须将庞大的sql导入mysql。最好是：|2780424|分为小剧本；|2780425|删除索引|2780426|您可以在导入后再添加索引。干杯|2780427|谢谢@btilly+@Hitesh+Mundra|2780428|entityMap^0|0|0|0|L|4|0|0|1G|9|0|W|9|0|0|0|3|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|18|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|19|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|1A|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|1B|8|@$I|1C|J|1D|K|L]]|9|@]|A|$]]|$1|M|3|N|5|H|7|1E|8|@]|9|@]|A|$]]|$1|O|3|P|5|H|7|1F|8|@$I|1G|J|1H|K|L]]|9|@]|A|$]]|$1|Q|3|R|5|H|7|1I|8|@$I|1J|J|1K|K|L]]|9|@]|A|$]]|$1|S|3|T|5|6|7|1L|8|@]|9|@]|A|$]]|$1|U|3|V|5|6|7|1M|8|@$I|1N|J|1O|K|L]]|9|@]|A|$]]|$1|W|3|X|5|6|7|1P|8|@]|9|@]|A|$]]|$1|Y|3|Z|5|H|7|1Q|8|@]|9|@]|A|$]]|$1|10|3|11|5|H|7|1R|8|@]|9|@]|A|$]]|$1|12|3|13|5|6|7|1S|8|@]|9|@]|A|$]]|$1|14|3|15|5|6|7|1T|8|@]|9|@]|A|$]]|$1|16|3|-4|5|6|7|1U|8|@]|9|@]|A|$]]]|17|$]]

Thanks for all your guys help.

I have taken some of your advices and done some comparison on this, now it is time to post the results. The target single sql script 15GB.

Overall, I tried:

<ol>
<li>importing data as single sql script with index; (Take Days, finally I killed it. DONOT TRY THIS YOURSELF, you will be pissed off.)</li>
<li>importing data as single sql script without index; (Same as above)</li>
<li>importing data as split sql script with index (Take the single sql as an example, I split the big file into small trunks around 41MB each. Each trunk takes around 2m19.586s, Total around );</li>
<li>importing data as split sql script without index; (Each trunk takes 2m9.326s.)</li>
</ol>

(Unfortunately I did not tried the Load Data method for this dataset)

Conclusion:

If you do not want to use Load Data method when you have to import a giant sql into mysql. It is better to:

<ol>
<li>Divide into small scripts;</li>
<li>Remove the index</li>
</ol>

You can add the index back after importing. Cheers

Thanks @btilly @Hitesh Mundra

blocks|key|434952|text|将以下命令放在giant.sql文件的开头|type|unstyled|depth|inlineStyleRanges|entityRanges|data|434953|SET+AUTOCOMMIT+=+0;
SET+FOREIGN_KEY_CHECKS=0;|code-block|syntax|javascript|434954|然后在最后|434955|SET+FOREIGN_KEY_CHECKS+=+1;
COMMIT;
SET+AUTOCOMMIT+=+1;|434956|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

Put the following commands at the head of giant.sql file

<pre><code>SET AUTOCOMMIT = 0;
SET FOREIGN_KEY_CHECKS=0;
</code></pre>

and following at the end 

<pre><code>SET FOREIGN_KEY_CHECKS = 1;
COMMIT;
SET AUTOCOMMIT = 1;
</code></pre>

Currently I have a situation which needs to import a giant sql script into mysql. The sql script content is mainly about INSERT operation. But there are so much records over there and the file size is around 80GB.

The machine has 8 cpus, 20GB mem. I have done something like:

<pre><code>mysql -h [*host_ip_address*] -u *username* -px xxxxxxx -D *databaseName* &lt; giant.sql
</code></pre>

But the whole process takes serveral days which is quite long.Is any other options to import the sql file into database? 

Thanks so much.

how to FAST import a giant sql script for mysql?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

目前，我遇到了一种情况，需要将一个巨大的sql脚本导入mysql。sql脚本内容主要涉及插入操作。但是那里有这么多的记录，文件大小在80 is左右。该机器有8个cpus，20 mem的内存。我做过这样的事情：mysql -h [*host_ip_address*] -u *username* -px xxxxxxx -...

问如何快速导入用于mysql的大型sql脚本？
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何快速导入用于mysql的大型sql脚本？EN