如何防止大型 MySQL 导入的连接超时

How to Prevent Connection Timeouts for Large MySQL Imports

提问by Memor-X

During development, how our local WAMP servers get up-to-date data from the test server is that a dump of the database is made and we upload that dump using the source command to load the .sql file.

在开发过程中，我们的本地 WAMP 服务器如何从测试服务器获取最新数据是对数据库进行转储，然后我们使用 source 命令加载该转储以加载 .sql 文件。

Recently, at the very end of the import we have been getting errors about the @old variables which stored the original settings like foreign key constraints before they're changed (so turning off foreign key constraints so that the import doesn't throw errors when it recreates tables and attempts to create foreign keys when one of the tables has yet to be created). I have worked out that the cause is that the product table is getting more and more data and at a point the session has timed out during the import.

最近，在导入的最后，我们收到了关于 @old 变量的错误，这些变量在更改之前存储了原始设置，如外键约束（因此关闭外键约束，以便导入时不会抛出错误）当其中一个表尚未创建时，它会重新创建表并尝试创建外键）。我已经确定原因是产品表正在获取越来越多的数据，并且在导入过程中会话超时。

I'm wondering what setting can I set (either as part of the SQL query on in the my.ini file) that will stop all timeouts, in effect making a session last forever while we are signed in.

我想知道我可以设置什么设置（作为 my.ini 文件中的 SQL 查询的一部分）来停止所有超时，实际上使会话在我们登录时永远持续下去。

回答by Christian Mark

Strategies for importing large MySQL databases

导入大型 MySQL 数据库的策略

PHPMyAdmin Import

PHPMyAdmin 导入

Chances are if you're reading this, PHPMyAdmin was not an option for your large MySQL database import. Nonetheless it is always worth a try, right? The most common cause of failure for PHPMyAdmin imports is exceeding the import limit. If you're working locally or have your own server, you can try changing the MySQL ini settings usually found in the my.ini file located in the MySQL install folder. If you're working with WAMP on Windows, you can access that file using the WAMP control panel under MySQL > my.ini. Remember to restart WAMP so your new settings will be used. Settings you may want to increase here include:

如果您正在阅读本文，很有可能 PHPMyAdmin 不是您的大型 MySQL 数据库导入的选项。尽管如此，它总是值得一试，对吧？PHPMyAdmin 导入失败的最常见原因是超出了导入限制。如果您在本地工作或拥有自己的服务器，则可以尝试更改通常位于 MySQL 安装文件夹中的 my.ini 文件中的 MySQL ini 设置。如果您在 Windows 上使用 WAMP，则可以使用 MySQL > my.ini 下的 WAMP 控制面板访问该文件。请记住重新启动 WAMP，以便使用您的新设置。您可能希望在此处增加的设置包括：

max_allowed_packet
read_buffer_size

Even with enhanced MySQL import settings you may still find that imports time out due to PHP settings. If you have access to PHP.ini, you can make edits to the maximum execution time and related settings. In WAMP, access the PHP.ini file under the WAMP control panel at PHP > php.ini. Consider raising the limits on the following settings while trying large MySQL imports:

即使使用增强的 MySQL 导入设置，您仍可能会发现由于 PHP 设置导致导入超时。如果您有权访问 PHP.ini，则可以对最长执行时间和相关设置进行编辑。在 WAMP 中，通过 PHP > php.ini 访问 WAMP 控制面板下的 PHP.ini 文件。在尝试大型 MySQL 导入时，请考虑提高以下设置的限制：

max_execution_time
max_input_time
memory_limit

Using Big Dump staggered MySQL dump importer

使用 Big Dump 交错 MySQL 转储导入器

If basic PHPMyAdmin importing does not work, you may want to try the Big Dump script from Ozerov.de for staggered MySQL imports. What this useful script does is run your import in smaller blocks, which is exactly what is often needed to successfully import a large MySQL dump. It is a free download available at http://www.ozerov.de/bigdump/.

如果基本的 PHPMyAdmin 导入不起作用，您可能需要尝试使用 Ozerov.de 中的 Big Dump 脚本进行交错的 MySQL 导入。这个有用的脚本的作用是在较小的块中运行您的导入，这正是成功导入大型 MySQL 转储通常需要的。它可以在http://www.ozerov.de/bigdump/ 上免费下载。

The process of using Big Dump is fairly simple: you basically position your SQL import file and the Big Dump script together on the server, set a few configs in the Big Dump script and then run the script. Big Dump handles the rest!

使用 Big Dump 的过程相当简单：您基本上将 SQL 导入文件和 Big Dump 脚本放在服务器上，在 Big Dump 脚本中设置一些配置，然后运行脚本。大转储处理其余的！

One key point about this otherwise great option, is that it will not work at all on MySQL exports that contain extended inserts. So if you have the option to prevent extended inserts, try it. Otherwise you will have to use another method for importing your large MySQL file.

关于这个本来不错的选项的一个关键点是，它根本不适用于包含扩展插入的 MySQL 导出。因此，如果您可以选择防止扩展插入，请尝试一下。否则，您将不得不使用另一种方法来导入大型 MySQL 文件。

Go command line with MySQL console

使用 MySQL 控制台转到命令行

If you're running WAMP (and even if you're not) there is always the option to cut to the chase and import your large MySQL database using the MySQL console. I'm importing a 4GB database this way as I write this post. Which is actually why I have some time to spend writing, because even this method takes time when you have a 4GB SQL file to import!

如果您正在运行 WAMP（即使您没有运行），始终可以选择切入正题并使用 MySQL 控制台导入大型 MySQL 数据库。我在写这篇文章时以这种方式导入了一个 4GB 的数据库。这就是为什么我要花一些时间来写作，因为当你有一个 4GB 的 SQL 文件要导入时，即使是这种方法也需要时间！

Some developers (usually me) are intimidated by opening up a black screen and typing cryptic commands into it. But it can be liberating, and when it comes to MySQL databases it often the best route to take. In WAMP we access the MySQL console from the WAMP control panel at MySQL > MySQL Console. Now let's learn the 2 simple MySQL Console commands you need to import a MySQL database, command-line style:

一些开发人员（通常是我）被打开一个黑屏并在其中输入神秘的命令吓坏了。但它可以是解放的，当涉及到 MySQL 数据库时，它通常是最好的选择。在 WAMP 中，我们从 MySQL > MySQL 控制台的 WAMP 控制面板访问 MySQL 控制台。现在让我们学习导入 MySQL 数据库所需的 2 个简单的 MySQL 控制台命令，命令行样式：

use `db_name`

Command usefollowed by the database name will tell the MySQL console which database you want to use. If you have already set up the database to which you are importing, then you start by issuing the usecommand. Suppose your database is named my_great_database. In this case, issue the following command in the MySQL Console. Note that commands must end with a semi-colon.

命令use后跟数据库名称将告诉 MySQL 控制台您要使用哪个数据库。如果您已经设置了要导入的数据库，那么您首先要发出use命令。假设您的数据库名为my_great_database. 在这种情况下，请在 MySQL 控制台中发出以下命令。请注意，命令必须以分号结尾。

mysql-> use my_great_database;

mysql-> source sql_import_file.sql

Command sourcefollowed by the location of a SQL file will import the SQL file to the database you previously specified with the usecommand. You must provide the path, so if you're using WAMP on your local server, start by putting the SQL file somewhere easy to get at such as C:\sql\my_import.sql. The full command with this example path would be:

命令source后跟 SQL 文件的位置会将 SQL 文件导入到您之前使用该use命令指定的数据库中。您必须提供路径，因此如果您在本地服务器上使用 WAMP，请首先将 SQL 文件放在易于获取的位置，例如 C:\sql\my_import.sql。此示例路径的完整命令将是：

mysql-> source C:\sql\my_import.sql;

After you run that command, the SQL file should begin to be imported. Let the queries run and allow the import to complete before closing the MySQL console.

运行该命令后，应该开始导入 SQL 文件。在关闭 MySQL 控制台之前，让查询运行并允许导入完成。

Further documentation for MySQL command line can be found here: http://dev.mysql.com/doc/refman/5.5/en/mysql.html.

可以在此处找到有关 MySQL 命令行的更多文档：http: //dev.mysql.com/doc/refman/5.5/en/mysql.html。

Another solution is to use MySQL Workbench.

另一种解决方案是使用MySQL Workbench。

回答by CloudyBright

This solution worked for me:

这个解决方案对我有用：

max_allowed_packet  <-- --> upped size to 8M
read_buffer_size  <-- -->  upped from 256 to 512

Using Xampp control panel on localhost. After making the changes to the my.ini file in MySQL config, don't forget to quit Xampp (or Wamp) and restart it for changes to take effect.

在本地主机上使用 Xampp 控制面板。在对 MySQL 配置中的 my.ini 文件进行更改后，不要忘记退出 Xampp（或 Wamp）并重新启动它以使更改生效。

(Four days of head-banging and I finally got it fixed!)

（四天的头撞，我终于把它修好了！）

Symptoms were on Import: #2006 MySql server went away.However, only 10 table rows were being imported out of 87 table rows.

导入时出现症状：#2006 MySql 服务器消失了。但是，在 87 个表行中只有 10 个表行被导入。

回答by Hanzaplastique

Consider using MySQL Workbench, it's free and handles very large script very well (from the menu choose: File-> Open SQL Script- if it's large, it will ask you if you'd like run it). Has served me well over the years when working with large SQL dumps.

考虑使用MySQL Workbench，它是免费的并且可以很好地处理非常大的脚本（从菜单中选择：文件->打开 SQL 脚本- 如果它很大，它会询问您是否要运行它）。多年来在处理大型 SQL 转储时对我很有帮助。