导入大型 sql 文件时 MySQL 服务器已消失 [英] MySQL Server has gone away when importing large sql file

问题描述

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

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

'MySql 服务器已经消失'

'MySql server has gone away'

怎么办?

推荐答案

如前所述 这里:

MySQL 服务器的两个最常见的原因(和修复)已经消失(错误 2006)是:

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. 检查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 小时).

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 = 600 seconds (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).

服务器丢弃了不正确或过大的数据包.如果 mysqld 得到一个太大或不正确的数据包，它假定某些东西已经客户端出错并关闭连接.你可以增加通过增加值的最大数据包大小限制my.cnf 文件中的 max_allowed_pa​​cket.在 Debian 上:sudo nano/etc/mysql/my.cnf，设置max_allowed_pa​​cket = 64M(可以当错误 2006 消失时调整/减少此值)，然后 sudo/etc/init.d/mysql restart.

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 选项文件的命令尚未作为注释提供(例如在 php.ini 中).因此，您必须在 my.cnf 或 my.ini 中键入任何更改/调整，并将它们放在 mysql/data 目录或任何其他路径，在适当的选项组下，例如 [client]、[myslqd] 等.例如:

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.cnf or my.ini and place them in mysql/data directory or in any of the other paths, under the proper group of options such as [client], [myslqd], etc. For example:

[mysqld]
wait_timeout = 600
max_allowed_packet = 64M

然后重启服务器.要获取它们的值，请在 mysql 客户端中输入:

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

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

这篇关于导入大型 sql 文件时 MySQL 服务器已消失的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！