MySQL 错误 1153 - 数据包大于“max_allowed_pa​​cket"字节

2021-11-20 00:00:00 mysql max-allowed-packet

我正在导入 MySQL 转储并收到以下错误.

I'm importing a MySQL dump and getting the following error.

$ mysql foo < foo.sql 
ERROR 1153 (08S01) at line 96: Got a packet bigger than 'max_allowed_packet' bytes

显然数据库中有附件，这使得插入非常大.

Apparently there are attachments in the database, which makes for very large inserts.

这是在我的本地机器上，一台从 MySQL 包安装了 MySQL 5 的 Mac.

This is on my local machine, a Mac with MySQL 5 installed from the MySQL package.

我在哪里更改 max_allowed_pa​​cket 以便能够导入转储?

Where do I change max_allowed_packet to be able to import the dump?

还有什么我应该设置的吗?

Is there anything else I should set?

只是运行 mysql --max_allowed_pa​​cket=32M ... 导致同样的错误.

Just running mysql --max_allowed_packet=32M … resulted in the same error.

推荐答案

您可能必须为客户端(您正在运行以执行导入)和正在运行并接受导入的守护进程 mysqld 更改它.

You probably have to change it for both the client (you are running to do the import) AND the daemon mysqld that is running and accepting the import.

>

对于客户端，可以在命令行指定:

For the client, you can specify it on the command line:

mysql --max_allowed_packet=100M -u root -p database < dump.sql

另外，更改mysqld部分下的my.cnf或my.ini文件(通常在/etc/mysql/中)并设置:

Also, change the my.cnf or my.ini file (usually found in /etc/mysql/) under the mysqld section and set:

max_allowed_packet=100M

或者您可以在连接到同一服务器的 MySQL 控制台中运行这些命令:

or you could run these commands in a MySQL console connected to that same server:

set global net_buffer_length=1000000; 
set global max_allowed_packet=1000000000;

(使用非常大的数据包大小值.)

(Use a very large value for the packet size.)

相关文章