导入大型 sql 文件时 MySQL 服务器已消失

MySQL Server has gone away when importing large sql file

提问by FrancisMV123

I tried to import a large sql file through phpMyAdmin...But it kept showing error

我试图通过 phpMyAdmin 导入一个大的 sql 文件...但它一直显示错误

'MySql server has gone away'

'MySql 服务器已经消失'

What to do?

该怎么办？

回答by GBD

As stated here:

如前所述这里：

Two most common reasons (and fixes) for the MySQL server has gone away (error 2006) are:

Server timed out and closed the connection. How to fix:

1. check that wait_timeout variable in your mysqld's my.cnf configuration file is large enough. On Debian: sudo nano /etc/mysql/my.cnf, set wait_timeout = 600seconds (you can tweak/decrease this value when error 2006 is gone), then sudo /etc/init.d/mysql restart. I didn't check, but the default value for wait_timeout might be around 28800 seconds (8 hours).

2. Server dropped an incorrect or too large packet. If mysqld gets a packet that is too large or incorrect, it assumes that something has gone wrong with the client and closes the connection. You can increase the maximal packet size limit by increasing the value of max_allowed_packet in my.cnf file. On Debian: sudo nano /etc/mysql/my.cnf, set max_allowed_packet = 64M(you can tweak/decrease this value when error 2006 is gone), then sudo /etc/init.d/mysql restart.

MySQL 服务器消失（错误 2006）的两个最常见原因（和修复）是：

服务器超时并关闭连接。怎么修：

1. 检查mysqld 的my.cnf 配置文件中的wait_timeout 变量是否足够大。在 Debian: 上sudo nano /etc/mysql/my.cnf，设置wait_timeout = 600秒数（当错误 2006 消失时，您可以调整/减少此值），然后设置sudo /etc/init.d/mysql restart. 我没有检查，但 wait_timeout 的默认值可能约为 28800 秒（8 小时）。

2. 服务器丢弃了不正确或过大的数据包。如果 mysqld 收到一个太大或不正确的数据包，它会假定客户端出现问题并关闭连接。您可以通过增加 my.cnf 文件中 max_allowed_pa​​cket 的值来增加最大数据包大小限制。在 Debian 上：sudo nano /etc/mysql/my.cnf，设置max_allowed_packet = 64M（当错误 2006 消失时，您可以调整/减少此值），然后sudo /etc/init.d/mysql restart.

Edit:

编辑：

Notice that MySQL option files do not have their commands already available as comments (like in php.ini for instance). So you must type any change/tweak in my.cnfor my.iniand place them in mysql/datadirectory or in any of the other paths, under the proper group of options such as [client], [myslqd], etc. For example:

请注意，MySQL 选项文件的命令尚未作为注释提供（例如在 php.ini 中）。所以，你必须键入任何变化/ TWEAKmy.cnf或my.ini并将它们放在mysql/data目录或以任何其它路径的选择，如适当的组下[client]，[myslqd]等。例如：

[mysqld]
wait_timeout = 600
max_allowed_packet = 64M

Then restart the server. To get their values, type in the mysql client:

然后重启服务器。要获取它们的值，请在 mysql 客户端中键入：

> select @@wait_timeout;
> select @@max_allowed_packet;

回答by salsinga

For me this solution didn't work out so I executed

对我来说，这个解决方案没有奏效，所以我执行了

SET GLOBAL max_allowed_packet=1073741824;

in my SQL client.

在我的 SQL 客户端中。

If not able to change this with MYSql service running, you should stop the service and change the variable in "my.ini" file.

如果无法在 MYSql 服务运行时更改此设置，则应停止该服务并更改“my.ini”文件中的变量。

For example:

例如：

max_allowed_packet=20M

回答by Mohan Gathala

If you are working on XAMPP then you can fix the MySQL Server has gone away issue with following changes..

如果您正在使用 XAMPP，那么您可以通过以下更改修复 MySQL 服务器已消失的问题。

open your my.ini file my.ini location is (D:\xampp\mysql\bin\my.ini)

打开你的 my.ini 文件 my.ini 位置是 (D:\xampp\mysql\bin\my.ini)

change the following variable values

更改以下变量值

max_allowed_packet = 64M
innodb_lock_wait_timeout = 500

回答by daemonofchaos

If you are running with default values then you have a lot of room to optimize your mysql configuration.

如果您使用默认值运行，那么您有很大的空间来优化您的 mysql 配置。

The first step I recommend is to increase the max_allowed_packet to 128M.

我建议的第一步是将 max_allowed_pa​​cket 增加到 128M。

Then download the MySQL Tuning Primer scriptand run it. It will provide recommendations to several facets of your config for better performance.

然后下载MySQL Tuning Primer 脚本并运行它。它将为您的配置的多个方面提供建议，以获得更好的性能。

Also look into adjusting your timeout values both in MySQL and PHP.

还要考虑在 MySQL 和 PHP 中调整超时值。

How big (file size) is the file you are importing and are you able to import the file using the mysql command line client instead of PHPMyAdmin?

您要导入的文件有多大（文件大小），您能否使用 mysql 命令行客户端而不是 PHPMyAdmin 导入文件？

回答by askthebigo

If you are using MAMP on OS X, you will need to change the max_allowed_packetvalue in the template for MySQL.

如果您在 OS X 上使用 MAMP，则需要更改max_allowed_packetMySQL 模板中的值。

1. You can find it at: File > Edit template > MySQL my.cnf

2. Then just search for max_allowed_packet, change the value and save.

1. 您可以在以下位置找到它：文件 > 编辑模板 > MySQL my.cnf

2. 然后只需搜索max_allowed_packet，更改值并保存。

回答by Dan.faudemer

I solved my issue with this short /etc/mysql/my.cnf file :

我用这个简短的 /etc/mysql/my.cnf 文件解决了我的问题：

[mysqld]
wait_timeout = 600
max_allowed_packet = 100M

回答by TekOps

The other reason this can happen is running out of memory. Check /var/log/messages and make sure that your my.cnf is not set up to cause mysqld to allocate more memory than your machine has.

发生这种情况的另一个原因是内存不足。检查 /var/log/messages 并确保您的 my.cnf 没有设置为导致 mysqld 分配比您的机器更多的内存。

Your mysqld process can actually be killed by the kernel and then re-started by the "safe_mysqld" process without you realizing it.

你的 mysqld 进程实际上可以被内核杀死，然后在你没有意识到的情况下被“safe_mysqld”进程重新启动。

Use top and watch the memory allocation while it's running to see what your headroom is.

使用 top 并在它运行时观察内存分配，看看你的空间是多少。

make a backup of my.cnf before changing it.

在更改之前备份 my.cnf。

回答by BenV136

I had this error and other related ones, when I imported at 16 GB SQL file. For me, editing my.ini and setting the following (based on several different posts) in the [mysqld] section:

当我导入 16 GB SQL 文件时，我遇到了这个错误和其他相关错误。对我来说，编辑 my.ini 并在 [mysqld] 部分设置以下内容（基于几个不同的帖子）：

max_allowed_packet      = 110M
innodb_buffer_pool_size=511M
innodb_log_file_size=500M
innodb_log_buffer_size = 800M
net_read_timeout        = 600
net_write_timeout       = 600

If you are running under Windows, go to the control panel, services, and look at the details for MySQL and you will see where my.ini is. Then after you edit and save my.ini, restart the mysql service (or restart the computer).

如果您在 Windows 下运行，请转到控制面板、服务并查看 MySQL 的详细信息，您将看到 my.ini 在哪里。然后编辑保存my.ini后，重启mysql服务（或重启电脑）。

If you are using HeidiSQL, you can also set some or all of these using that.

如果您使用的是 HeidiSQL，您还可以使用它来设置部分或全部。

回答by coderama

I updated "max_allowed_packet" to 1024M, but it still wasn't working. It turns out my deployment script was running:

我将“max_allowed_pa​​cket”更新为 1024M，但它仍然无法正常工作。原来我的部署脚本正在运行：

mysql --max_allowed_packet=512M --database=mydb -u root < .\db\db.sql

Be sure to explicitly specify a bigger number from the command line if you are donig it this way.

如果您这样做，请务必从命令行明确指定一个更大的数字。

回答by fooquency

If your data includes BLOBdata:

如果您的数据包括BLOB数据：

Note that an import of data from the command line seems to choke on BLOB data, resulting in the 'MySQL server has gone away' error.

请注意，从命令行导入数据似乎会阻塞 BLOB 数据，从而导致“MySQL 服务器已消失”错误。

To avoid this, re-create the mysqldump but with the --hex-blobflag:

为避免这种情况，请重新创建 mysqldump 但使用以下--hex-blob标志：

http://dev.mysql.com/doc/refman/5.7/en/mysqldump.html#option_mysqldump_hex-blob

http://dev.mysql.com/doc/refman/5.7/en/mysqldump.html#option_mysqldump_hex-blob

which will write out the data file with hex values rather than binary amongst other text.

这将用十六进制值而不是其他文本中的二进制写出数据文件。

PhpMyAdmin also has the option "Dump binary columns in hexadecimal notation (for example, "abc" becomes 0x616263)" which works nicely.

PhpMyAdmin 还具有选项“以十六进制表示法转储二进制列（例如，“abc”变为 0x616263）”效果很好。

Note that there is a long-standing bug (as of December 2015) which means that GEOMcolumns are not converted: Back up a table with a GEOMETRY column using mysqldump?so using a program like PhpMyAdmin seems to be the only workaround (the option noted above does correctly convert GEOM columns).

请注意，存在一个长期存在的错误（截至 2015 年 12 月），这意味着GEOM列未转换： 使用 mysqldump 备份具有 GEOMETRY 列的表？所以使用像 PhpMyAdmin 这样的程序似乎是唯一的解决方法（上面提到的选项可以正确转换 GEOM 列）。