如何更改max_allowed_pa​​cket大小

本文翻译自：How to change max_allowed_packet size

I am having a problem with BLOB fields in my MySQL database - when uploading files larger than approx 1MB I get an error Packets larger than max_allowed_packet are not allowed. 我的MySQL数据库中的BLOB字段存在问题-上传大于1MB的文件时出现错误， Packets larger than max_allowed_packet are not allowed.

Here is what i've tried: 这是我尝试过的：

In MySQL Query Browser I ran a show variables like 'max_allowed_packet' which gave me 1048576. 在MySQL查询浏览器中，我运行了一个show variables like 'max_allowed_packet' ，它给了我1048576。

Then I execute the query set global max_allowed_packet=33554432 followed by show variables like 'max_allowed_packet' - it gives me 33554432 as expected. 然后，我执行查询set global max_allowed_packet=33554432然后执行show variables like 'max_allowed_packet'如预期的那样，它给了我33554432。

But when I restart the MySQL server it magically goes back to 1048576. What am I doing wrong here? 但是，当我重新启动MySQL服务器时，它神奇地回到了1048576。在这里我做错了什么？

Bonus question, is it possible to compress a BLOB field? 额外的问题，是否可以压缩BLOB字段？

---

#1楼

参考：https://stackoom.com/question/xPqg/如何更改max-allowed-pa-cket大小

---

#2楼

I think some would also want to know how to find the my.ini file on your PC. 我认为有些人也想知道如何在您的PC上找到my.ini文件。 For windows users, I think the best way is as follows: 对于Windows用户，我认为最好的方法如下：

1. Win+R (shortcut for 'run'), type services.msc , Enter Win + R （“运行”的快捷方式），键入services.msc ，输入
2. You could find an entry like 'MySQL56', right click on it, select properties 您可以找到类似“ MySQL56”的条目，右键单击它，选择属性
3. You could see sth like "D:/Program Files/MySQL/MySQL Server 5.6/bin\\mysqld" --defaults-file="D:\\ProgramData\\MySQL\\MySQL Server 5.6\\my.ini" MySQL56 您可能会看到类似“ D：/ Program Files / MySQL / MySQL Server 5.6 / bin \\ mysqld” --defaults-file =“ D：\\ ProgramData \\ MySQL \\ MySQL Server 5.6 \\ my.ini” MySQL56

I got this answer from http://bugs.mysql.com/bug.php?id=68516 我从http://bugs.mysql.com/bug.php?id=68516得到了这个答案

---

#3楼

One of my junior developers was having a problem modifying this for me so I thought I would expand this in greater detail for linux users: 我的一名初级开发人员在为我修改此文件时遇到了问题，因此我想我将为Linux用户更详细地介绍这一点：

1) open terminal 1）打开终端

2) ssh root@YOURIP 2）ssh root @您的IP地址

3) enter root password 3）输入root密码

4) nano /etc/mysql/my.cnf (if command is not recognized do this first or try vi then repeat: yum install nano ) 4）nano /etc/mysql/my.cnf（如果无法识别命令，请先执行此操作，或者先尝试vi然后再重复：yum install nano）

5) add the line: max_allowed_packet=256M (obviously adjust size for whatever you need) under the [MYSQLD] section. 5）在[MYSQLD]部分下添加以下行：max_allowed_pa​​cket = 256M（显然可以根据需要调整大小）。 He made a mistake of putting it at the bottom of the file first so it did not work. 他犯了一个错误，将其首先放在文件的底部，因此它不起作用。

6) Control + O (save) then ENTER (confirm) then Control + X (exit file) 6）Control + O（保存），然后按ENTER（确认），然后按Control + X（退出文件）

7) service mysqld restart 7）服务mysqld重启

8) You can check the change in the variables section on phpmyadmin 8）您可以在phpmyadmin的变量部分中检查更改

---

#4楼

If getting this error while performing a backup, max_allowed_packet can be set in the my.cnf particularly for mysqldump . 如果在执行备份时出现此错误，则可以在my.cnf设置max_allowed_packet ，特别是对于mysqldump 。

[mysqldump]
max_allowed_packet=512M

I kept getting this error while performing a mysqldump and I did not understand because I had this set in my.cnf under the [mysqld] section. 我在执行mysqldump时一直收到此错误，但我不明白，因为我在[mysqld]部分下的my.cnf对此进行了设置。 Once I figured out I could set it for [mysqldump] and I set the value, my backups completed without issue. 一旦确定可以为[mysqldump]设置它并设置该值，我的备份就可以顺利完成了。

---

#5楼

If you want upload big size image or data in database. 如果要上载较大尺寸的图像或数据库中的数据。 Just change the data type to 'BIG BLOB' . 只需将数据类型更改为'BIG BLOB' 。

---

#6楼

For those running wamp mysql server 对于那些运行wamp mysql服务器的人

Wamp tray Icon -> MySql -> my.ini Wamp tray图标-> MySql-> my.ini

[wampmysqld]
port        = 3306
socket      = /tmp/mysql.sock
key_buffer_size = 16M
max_allowed_packet = 16M        // --> changing this wont solve
sort_buffer_size = 512K

Scroll down to the end until u find 向下滚动到最后直到找到

[mysqld]
port=3306
explicit_defaults_for_timestamp = TRUE

Add the line of packet_size in between 在之间添加packet_size行

[mysqld]
port=3306
max_allowed_packet = 16M
explicit_defaults_for_timestamp = TRUE

Check whether it worked with this query 检查它是否适用于此查询

Select @@global.max_allowed_packet;