Mysql max_allowed_packet参数

官网连接:https://dev.mysql.com/doc/refman/8.0/en/packet-too-large.html

https://www.cndba.cn/hbhe0316/article/22617

https://www.cndba.cn/hbhe0316/article/22617

The maximum size of one packet or any generated/intermediate string, or any parameter sent by the mysql_stmt_send_long_data() C API function. The default is 64MB.https://www.cndba.cn/hbhe0316/article/22617

The packet message buffer is initialized to net_buffer_length bytes, but can grow up to max_allowed_packet bytes when needed. This value by default is small, to catch large (possibly incorrect) packets.https://www.cndba.cn/hbhe0316/article/22617

You must increase this value if you are using large BLOB columns or long strings. It should be as big as the largest BLOB you want to use. The protocol limit for max_allowed_packet is 1GB. The value should be a multiple of 1024; nonmultiples are rounded down to the nearest multiple.

When you change the message buffer size by changing the value of the max_allowed_packet variable, you should also change the buffer size on the client side if your client program permits it. The default max_allowed_packet value built in to the client library is 1GB, but individual client programs might override this. For example, mysql and mysqldump have defaults of 16MB and 24MB, respectively. They also enable you to change the client-side value by setting max_allowed_packet on the command line or in an option file.https://www.cndba.cn/hbhe0316/article/22617

The session value of this variable is read only. The client can receive up to as many bytes as the session value. However, the server does not send to the client more bytes than the current global max_allowed_packet value. (The global value could be less than the session value if the global value is changed after the client connects.)

1.查看默认值

mysql> SHOW VARIABLES LIKE 'max_allowed_packet';
+--------------------+---------+
| Variable_name      | Value   |
+--------------------+---------+
| max_allowed_packet | 4194304 |
+--------------------+---------+
1 row in set (0.00 sec)

2.设置global级别https://www.cndba.cn/hbhe0316/article/22617

mysql> set global max_allowed_packet=32*1024*1024;
Query OK, 0 rows affected (0.00 sec)

重新登录mysql控制台后再次查看https://www.cndba.cn/hbhe0316/article/22617https://www.cndba.cn/hbhe0316/article/22617https://www.cndba.cn/hbhe0316/article/22617https://www.cndba.cn/hbhe0316/article/22617

mysql> quit
Bye
[root@mysql57 ~]# /mysql/bin/mysql -uroot -pwwwwww -S /mysql/mysql.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or /g.
Your MySQL connection id is 3
Server version: 5.7.36-log MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '/h' for help. Type '/c' to clear the current input statement.

mysql> SHOW VARIABLES LIKE 'max_allowed_packet';
+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| max_allowed_packet | 33554432 |
+--------------------+----------+
1 row in set (0.01 sec)

3.设置/etc/my.cnf文件

[root@mysql57 ~]# cat /etc/my.cnf | grep -i max-allowed-packet
max-allowed-packet = 32M
[root@mysql57 ~]# service mysqld restart
Shutting down MySQL.... SUCCESS! 
Starting MySQL. SUCCESS!

版权声明：本文为博主原创文章，未经博主允许不得转载。

MYSQL