弹性云服务器 centos 里 修改Mariadb(Mysql)的max_allowed_packet属性以确保大的数据能插入数据库保存

参考链接：https://blog.csdn.net/qq_34988304/article/details/92762504

环境：centos 华为弹性云服务器

uname -a
Linux ecs-d11a-0002 4.18.0-80.7.2.el7.aarch64 #1 SMP Thu Sep 12 16:13:20 UTC 2019 aarch64 aarch64 aarch64 GNU/Linux

问题表现：客户端提交数据大了，无法保存到数据库

问题原因：MySQL根据配置文件会限制Server接受的数据包大小。有时候大的插入和更新会受 max_allowed_packet 参数限制，导致写入或者更新失败。(比方说导入数据库,数据表)

处理方法步骤：

1。查询原本数据库max_allowed_packet信息，通过命令show VARIABLES like '%max_allowed_packet%';查询：

[root@ecs-d11a-0002 ~]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 20993
Server version: 5.5.68-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]>  show VARIABLES like '%max_allowed_packet%';
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| max_allowed_packet       | 1048576    |
| slave_max_allowed_packet | 1073741824 |
+--------------------------+------------+
2 rows in set (0.00 sec)

可以看到max_allowed_packet的大小为1048576=1024*1024*1 即1M ，此时会发现超1M文件是无法正常保存到数据库里的，因此我们需要进行修改

根据资料，可以通过命令或者是修改配置文件的方式 

这里尝试通过命令修改：

MariaDB [(none)]> set global max_allowed_packet = 2*1024*1024*100;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]>  show VARIABLES like '%max_allowed_packet%';
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| max_allowed_packet       | 1048576    |
| slave_max_allowed_packet | 1073741824 |
+--------------------------+------------+
2 rows in set (0.00 sec)

可以看到命令修改并未生效，那么我们尝试通过修改配置文件的方式

2。修改配置文件/etc/my.cnf

vi /etc/my.cnf

在[mysqld]段或者mysql的server配置段进行修改,加入max_allowed_packet的设定，一般不要超过2G

cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake
#通过max_allowed_packet=500M设置可以接收保存的最大数据量为500M
max_allowed_packet=500M

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

3、设置完毕之后不会立即生效，需要重启一下服务：

systemctl restart mariadb

4、重启之后再次确认设置是否生效：

mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 17
Server version: 5.5.68-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]>  show VARIABLES like '%max_allowed_packet%';
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| max_allowed_packet       | 524288000  |
| slave_max_allowed_packet | 1073741824 |
+--------------------------+------------+
2 rows in set (0.00 sec)

MariaDB [(none)]> exit
Bye

这样设置就完成了，本次操作使用的是root用户操作的，如果是其他用户，过程中可能需要注意权限问题。