再次了解max_allowed_packet

bisal的个人杂货铺 2022-07-15

409

MySQL的参数很多，当出现问题时，往往就是某个参数在作祟，一方面说明MySQL的控制灵活，另一方面就要求熟知常用的参数作用，才能在出现问题的时候快速定位。

曾经写过一篇《小白学习MySQL - max_allowed_packet》的文章，其中介绍了MySQL的max_allowed_packet参数，最近碰巧看到爱可生开源社区推送了《技术分享 | MySQL：max_allowed_packet影响了什么？》，在此借鉴一下，有助于更系统地了解这个参数。

max_allowed_packet表示MySQL Server或者客户端接收packet的最大大小，packet即数据包，MySQL Server和客户端都有这个限制。

数据包

每个数据包，都由包头、包体两部分组成，包头由3字节的包体长度、1字节的包编号组成。3字节最多能够表示2 ^ 24 = 16777216字节(16M)，就是说，一个数据包的包体长度必须小于等于16M。

如果要发送超过16M的数据怎么办？

当要发送大于16M的数据时，会将数据拆分成多个16M的数据包，除最后一个数据包之外，其他数据包大小都是16M。而MySQL Server收到这样的包后，如果发现包体长度等于16M ，他就知道本次接收的数据由多个数据包组成，会先将当前数据包的内容写入缓冲区，然后接着读取下一个数据包，并将下一个数据包的内容追加到缓冲区，直到读到结束数据包，就接收到客户端发送的完整数据了。

那怎样算一个数据包？

一个SQL是一个数据包
返回查询结果时，一行数据算一个数据包。
解析的binlog ，如果用MySQL客户端导入，一个SQL算一个数据包。
在复制中，一个event算一个数据包。

下面我们通过测试来讨论max_allowed_packet的实际影响。

导入SQL文件受max_allowed_packet限制么？

如果SQL文件中有单个SQL大小超过max_allowed_packet，会报错，

##导出时设置 mysqldump --net-buffer-length=16M，这样保证导出的sql文件中单个 multiple-row INSERT 大小为 16M
mysqldump -h127.0.0.1 -P13306 -uroot -proot --net-buffer-length=16M \
--set-gtid-purged=off sbtest sbtest1 > data/backup/sbtest1.sql

##设置max_allowed_packet=1M

##导入报错
[root@localhost data]# mysql -h127.0.0.1 -P13306 -uroot -proot db3 < data/backup/sbtest1.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1153 (08S01) at line 41: Got a packet bigger than 'max_allowed_packet' bytes

导入解析后的binlog受max_allowed_packet限制么？

row格式的binlog，单个SQL修改的数据产生的binlog如果超过max_allowed_packet，就会报错。

在恢复数据到指定时间点的场景，解析后的binlog单个事务大小超过1G，并且这个事务只包含一个SQL，此时一定会触发max_allowed_packet的报错。

但是恢复数据的任务又很重要，怎么办呢？可以将binlog改名成relay log，用SQL线程回放来绕过这个限制。

查询结果受max_allowed_packet限制么？

查询结果中，只要单行数据不超过客户端设置的max_allowed_packet即可，

##插入2行20M大小的数据
[root@localhost tmp]# dd if=/dev/zero of=20m.img bs=1 count=0 seek=20M
记录了0+0 的读入
记录了0+0 的写出
0字节(0 B)已复制，0.000219914 秒，0.0 kB/秒
[root@localhost tmp]# ll -h 20m.img
-rw-r--r-- 1 root root 20M 6月   6 15:15 20m.img

mysql> create table t1(id int auto_increment primary key,a longblob);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t1 values(NULL,load_file('/tmp/20m.img'));
Query OK, 1 row affected (0.65 sec)

mysql> insert into t1 values(NULL,load_file('/tmp/20m.img'));
Query OK, 1 row affected (0.65 sec)

##mysql客户端默认 --max-allowed-packet=16M,读取失败
mysql> select * from t1;
ERROR 2020 (HY000): Got packet bigger than 'max_allowed_packet' bytes

##设置 mysql 客户端 --max-allowed-packet=22M，读取成功
[root@localhost ~]# mysql -h127.0.0.1 -P13306 -uroot -proot --max-allowed-packet=23068672 sbtest -e "select * from t1;" > tmp/t1.txt

[root@localhost ~]# ll  -h tmp/t1.txt
-rw-r--r-- 1 root root 81M 6月   6 15:30 tmp/t1.txt

load data文件大小受max_allowed_packet限制么？

load data文件大小、单行大小都不受max_allowed_packet影响，

##将上一个测试中的数据导出，2行数据一共81M
mysql> select * into outfile '/tmp/t1.csv' from t1;
Query OK, 2 rows affected (0.57 sec)

[root@localhost ~]# ll -h tmp/t1.csv
-rw-r----- 1 mysql mysql 81M 6月   6 15:32 tmp/t1.csv

##MySQL Server max_allowed_packet=16M
mysql> select @@max_allowed_packet;
+----------------------+
| @@max_allowed_packet |
+----------------------+
|             16777216 |
+----------------------+
1 row in set (0.00 sec)

##load data 成功,不受 max_allowed_packet 限制
mysql> load data infile '/tmp/t1.csv' into table t1;
Query OK, 2 rows affected (1.10 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0