通过命令行将大型SQL文件导入到MySQL

Question

我想通过Ubuntu命令行将一个大约300MB的SQL文件导入到MySQL中。我使用了以下命令：

source /var/www/myfile.sql;

现在它正在显示看似无限的行：

Query OK, 1 row affected (0.03 sec)

然而它已经运行了一段时间。我之前从未导入过这么大的文件，所以我想知道这是否正常，如果进程停滞或出现错误，这是否会在命令行中显示，还是这个进程将一直继续下去？

谢谢

- user2028856

---

它将在脚本中持续运行每个查询，直到客户端崩溃或MySQL死机，或者它处理完所有的查询。 - Marc B

如果客户端崩溃或mysql死机，我会在命令行中看到错误消息吗？还是它会无限制地继续运行，看起来像是正在运行。我只是想知道这一点，以便如果导入需要数小时，我不会浪费时间而不重新启动进程。 - user2028856

如果客户端崩溃，你只会回到shell提示符。 - Marc B

如果服务器崩溃了...其实不太确定，很可能客户端会不断尝试重新连接并开始大量输出错误信息，而不是成功的查询通知。 - Marc B

Answer 1

您可以使用标准输入导入 .sql 文件，方法如下： mysql -u <用户> -p<密码> <数据库名> < file.sql 注意： <-p> 和 <密码> 之间不应该有空格。

参考资料：http://dev.mysql.com/doc/refman/5.0/en/mysql-batch-commands.html 注意事项：此答案经过建议编辑略微更改，使用内联密码参数。我可以推荐它用于脚本，但您应该知道，当您直接在参数中写入密码 (-p<password>) 时，它可能会被 shell 历史记录缓存，从而将您的密码透露给任何能够读取历史记录文件的人。而 -p 则要求您通过标准输入输入密码。

- Martin Nuc

---

请参考此处的参考文献以获取您的方法：http://dev.mysql.com/doc/refman/5.0/en/mysql-batch-commands.html - Martin Nuc

我们导入像4 GB这样的文件，需要大约4-5分钟，这取决于机器的性能。所以我猜你的文件最多只需要1分钟。但这只是猜测。 - Martin Nuc

什么？4GB需要4-5分钟？我在Windows Azure虚拟机上运行着一个纯净的MySQL安装，该虚拟机配备了4个核心和7GB的RAM。已经过去了30分钟，但还在继续。你认为这里有什么问题吗？ - user2028856

基本上，我查找了一些信息并对 MySQL 调优进行了一些研究。我将 InnoDB 的缓冲池大小增加到了 12G，并进行了其他一些设置，我注意到速度有了显著的提升。我的导入过程从 20 多个小时降低到了 1 小时，这非常好 :) - user2028856

这个没有起作用。因为我的文件有700GB！在一个经常超时的托管服务器上。但是这是正确的方向。我获得了SSH访问权限，然后使用Unix的split命令拆分文件。Vim仍然太慢了，所以我使用head和tail来确保我的拆分是可接受的。我还必须使用cat附加头信息/变量。然后运行命令，它起作用了。我用45分钟完成了手动操作需要9小时才能完成的工作！谢谢Martin！ - rlb.usa

Answer 2

关于导入大文件所需的时间，最重要的原因是默认情况下mysql的设置是"autocommit=true"，在导入文件之前必须将其关闭，然后检查如何像宝石一样导入文件...

首先打开MySQL：

mysql -u root -p

然后，您只需要执行以下操作：

mysql>use your_db

mysql>SET autocommit=0 ; source the_sql_file.sql ; COMMIT ;

- Paresh Behede

---

非常酷，我喜欢它。还有一条信息。使用以下命令将所有SQL文件收集到一个SQL文件中：cat *.sql >> all_data.sql。这非常有用。我现在正在导入3.5G的文件 :) 不要忘记表必须是MyIsam类型的。 - kodmanyagha

表 xxx 不存在...为什么？ - John Joe

导入完成后，自动提交不应该返回到“SET autocommit=1;”吗？ - machineaddict

不再需要使用MyISAM表。这里是更多信息和其他针对唯一表等的速度调整的手册页面。 https://dev.mysql.com/doc/refman/5.6/en/optimizing-innodb-bulk-data-loading.html - Phil McCarty

嗨...如何跳过错误，以便在出现错误后继续导入...使用Force？如何集成在SET autocommit=0; source the_sql_file.sql; COMMIT; --FORCE;中？ - JuJu

Answer 3

+1 给 @MartinNuc，你可以在批处理模式下运行 mysql 客户端，这样你就不会看到长串的“OK”行了。

导入给定 SQL 文件所需的时间取决于许多因素。不仅是文件的大小，还包括其中的语句类型、服务器的性能以及同时运行的其他任务数量。

@MartinNuc 说他可以在 4-5 分钟内加载 4GB 的 SQL，但我曾在较小的服务器上运行了 0.5 GB 的 SQL 文件，需要 45 分钟才能完成。

我们无法猜测在你的服务器上运行 SQL 脚本需要多长时间。

关于你的评论，

@MartinNuc 是正确的，你可以选择让 mysql 客户端打印每个语句。或者你可以打开第二个会话并运行 mysql> SHOW PROCESSLIST 来查看正在运行的内容。但你可能更感兴趣的是“完成百分比”或剩余语句完成所需时间的估计值。

很抱歉，没有这样的功能。mysql 客户端不知道后面的语句需要多长时间才能运行完毕，甚至不知道有多少语句。因此，它无法为剩余语句的完成提供有意义的时间估计。

- Bill Karwin

---

谢谢你的回复，Bill。马丁提到的命令是否提供某种进程显示或其他信息呢？我现在无法进行测试，因为我将不得不取消当前的导入。 - user2028856

mysql --verbose 用于查看每个命令的详细信息。在参考文献 http://dev.mysql.com/doc/refman/5.0/en/mysql-batch-commands.html 中有提到。 - Martin Nuc

Bill，你可以使用Pipe View Utility来实现百分比完成特性...例如 sudo pv -i 1 -p -t -e DB.sql | mysql -uDB_USER -p DBANAME。 - abhi

@abhi，这是一个很好的提示！它假设DB.sql文件中的每个语句成本相近，但在大多数情况下这并不是一个坏的假设。 - Bill Karwin

除非我经常压缩我的.sql文件，否则我必须知道未压缩的大小才能获得进度条：bunzip2 DB.gz.bz2 | pv -i 1 -p -t -e -s 2758819477 | mysql ... @abhi - Bill Karwin

Answer 4

我在进行大型SQL恢复时使用的解决方案是mysqldumpsplitter脚本。我将我的sql.gz文件分割成单独的表，然后加载到类似于mysql workbench的工具中，并将其处理为所需的模式进行恢复。

以下是该脚本: https://github.com/kedarvj/mysqldumpsplitter 这对于较大的SQL恢复有效，在我使用的一个网站上，平均一个2.5GB的sql.gz文件，20GB未压缩，完全恢复后约为100GB。

- Chris Richardson

Answer 5

我最近导入了一个超过11GB的大型SQL文件。在我的情况下，并不需要全部数据库。我只导入了我需要的一些表格。所以也许以下解决方案对其他人也有用；

grep -A 200 ‘CREATE TABLE `xyz_table’ sql_file_path > new_light_sql_file_path.sql

创建了一个轻量级的 SQL 文件后，您可以按照以下命令轻松导入它。

mysql -u 用户名 -p(密码) 数据库名 < 新的轻量级 SQL 文件路径.sql

- Tuncay Elvanagac

Answer 6

通过命令行将大型SQL文件导入到MySql

1. 首先下载文件。
2. 将文件粘贴到主目录。
3. 在终端（CMD）中使用以下命令：
4. 语法： mysql -u用户名 -p数据库名 < 文件名.sql

例如： mysql -u root -p aanew < aanew.sql

- Shailesh Sharma