导入大型SQL文件（>20GB）

Question

我正在尝试将一个非常大的.sql文件导入到我的数据库中，该文件目前有20GB大小。我尝试使用控制台mysql数据库< backup.sql，但这需要超过24小时，并且mysql服务器不再做出反应。

我该如何导入这么大的文件呢？我认为拆分它是正确的方法，但如何正确拆分呢？它只是一个包含多个插入语句的表格。

- clonaech

---

制作一个Python程序，逐行读取并将其输入数据库。您还可以使程序打印偶尔的状态消息（％完成等）。 - Sudipta Chatterjee

对于 PHP，有这样一个程序：http://www.ozerov.de/bigdump/，但它可能无法帮到你。 - biziclop

@SudiptaChatterjee 那有什么帮助呢？那会显著变慢，因为您现在需要进行文件解析、SQL执行和execv等操作。 - mmtauqir

这个20GB的文件是如何生成的？是由另一个SQL服务器生成的吗？这是一次性操作，还是您需要定期生成它？ - RandomUs1r

@gbtimmon和mtahmed - 我认为这将允许超时等，同时通过状态提示保持进程活动。然而，下面的其他答案肯定更合适。 - Sudipta Chatterjee

Answer 1

MySQL语言内置了基于API的表插入功能。请参见下面的链接：http://dev.mysql.com/doc/refman/5.1/en/load-data.html。

您需要将文件从插入语句重新格式化为某种CSV格式或类似格式，但这比单个语句快得多，因为它是一种与RDBMS通信的方式，“我要上传大量数据，请在最后保存重新索引和簿记开销，确保你有足够的空间并在填满空间时一次性占用它，确保使用适当的锁等等”。

- gbtimmon

---

有没有脚本或其他东西可以从我的“插入到”文件创建这样的文件？或者我可以将数据库导出到这样的文件吗？ - clonaech

有很多脚本可用，如果你知道的话，awk会起作用，但是良好的流程设计会告诉你，你希望生成INSERT文件的过程生成一个.csv文件。我需要知道那个过程才能理解你需要做什么来使你的文件格式正确。 - gbtimmon

好的，我看到mysqldump有一个选项。但是如何保存索引？或者我必须在导入之后指定该索引吗？ - clonaech

如果您正在尝试进行表复制，则可能不希望导出数据。数据库应该也有支持此操作的函数，这将是更快的选择。您现在所选的路线将需要为新数据构建新索引，这会影响一些运行时间。事实上，从一个数据库到另一个数据库的表复制也很可能需要重建索引，因为硬盘映射不同，B树也会不同。 - gbtimmon

总的来说，如何将这样一个大表从一个服务器传输到另一个服务器是最佳方式呢？ - clonaech

Answer 2

你可以将 SQL 文件拆分并导入数据库。

最简单的拆分 sqldump 文件的方式是使用 sqldumpsplitter 软件，下载 link。

或者使用此终端命令进行拆分： split -l 5000 ./path/to/mysqldump.sql ./mysqldump/dbpart- 其中，5000 是你想要拆分的 SQL 命令行数。接下来的两个参数分别是源文件路径和目标文件路径。产生的每个拆分文件都会带有末尾的字母名称。

希望这能帮助其他遇到这个问题的人。

- Giri

Answer 3

简单地锁定表，然后复制该表的数据文件（.frm、.MYI、.MYD）。
立即获得一个可以导入到任何数据库的准备好的数据文件。

这种方法对于myisam非常有效。 不确定innodb是否适用。

- ajreal

Answer 4

我建议您在尝试跨多个文件保留SQL查询时考虑以下情况：

• 单个SQL查询可能跨越多行，因此您不能仅按行拆分
• 有效的SQL查询将以分号（;）结尾，大多数情况下，这将是该行的结尾。

掌握了这两种情况的知识后，您可以编写一个简单的脚本，逐行处理您的SQL转储，并在找到以分号（;）结尾的行或者500行时将其拆分成块。

我曾经为类似的问题编写过这样的脚本：https://gist.github.com/pratikone/0a8d503ffe893b2111f5a1d70d3999b7

它只检查行中的分号（;），而不是行末的分号（;），但我没有遇到任何SQL查询中包含分号（;）的情况，所以它完美地工作。鉴于这一点，如果您的数据可以在查询之间有分号，则需要进行小修改。

- pratikone