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

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

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

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

解决方案

导入大型 MySQL 数据库的策略

PHPMyAdmin 导入

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

max_allowed_pa​​cket读取缓冲区大小

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

max_execution_time最大输入时间内存限制

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

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

使用 Big Dump 的过程相当简单:您基本上将 SQL 导入文件和 Big Dump 脚本放在服务器上，在 Big Dump 脚本中设置一些配置，然后运行脚本.Big Dump 处理剩下的事情！

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

使用 MySQL 控制台转到命令行

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

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

使用`db_name`

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

mysql->使用 my_great_database;mysql->源 sql_import_file.sql

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

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

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

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

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

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.

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.

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.

解决方案

Strategies for importing large MySQL databases

PHPMyAdmin Import

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:

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:

max_execution_time
max_input_time
memory_limit

Using Big Dump staggered MySQL dump importer

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/.

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!

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.

Go command line with MySQL console

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!

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:

use `db_name`

Command use followed 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 use command. 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.

mysql-> use my_great_database;

mysql-> source sql_import_file.sql

Command source followed by the location of a SQL file will import the SQL file to the database you previously specified with the use command. 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:

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.

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

Another solution is to use MySQL Workbench.