问题

今天写数据库（MySQL）然而出现的

Got a packet bigger than‘max_allowed_packet’bytes

瞬间懵逼??? 

输出看一下，原来真的是超出了MySQL里面的最大长度限制，具体可如下查看：



进入MySQL后：
mysql> show variables like "%max_allow%"
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| max_allowed_packet       | 4194304    |
| slave_max_allowed_packet | 1073741824 |
+--------------------------+------------+

而我的长度恰恰大于这个数值，然后确定是这个问题，原因是数据库内置配置的问题。。。修改！！！

解决方法

修改配置文件



方法一：

可以编辑my.cnf来修改（windows下my.ini）,在[mysqld]段或者mysql的server配置段进行修改。
max_allowed_packet = 20M

如果找不到my.cnf可以通过mysql –help | grep my.cnf去寻找my.cnf文件。（通常我自己的操作是直接find / -name ‘my.cnf’） 

2. 重启mysql服务 

3. 执行命令查看是否成功



SHOW VARIABLES LIKE '%max_allowed_packet%'



方法二：

使用命令修改max_allowed_packet 大小 

1. 执行命令

SET GLOBAL max_allowed_packet = 2*1024*1024*10 -- 单位为B

. 

2. 然后关闭掉这此链接，再进入执行命令



mysql> SHOW VARIABLES LIKE '%max_allowed_packet%
///现在的结果是：
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| max_allowed_packet       | 20971520   |
| slave_max_allowed_packet | 1073741824 |
+--------------------------+------------+

上面我的是第一种方法修改后不生效，然后只能使用第二种。。。根据网上说法，具体问题如下：


  
根据自身经验和网上论坛帖子，第二种方法出现问题的概率相对较小。第一种我在最近的一次设置上，不起作用，使用第二种方式就没有问题， 

  第一种通过修改配置文件之后，max_allowed_packet 的值 不定期的自动改变， 网上对于这种解释是因为mysql内存不足，导致的 

  第二种修改配置文件之后，max_allowed_packet 的值 之后，没有生效，重启也没有生效，一种问题是本地的客户端与mysql服务器没有断开查询的，重新链接，查看是否生效，如果没有生效，使用第二种方式，进行修改

brew安装的mysql是没有默认配置文件的，因此Mysql使用默认配置启动。我们需要手动创建my.cnf去覆盖默认配置。在/usr/local/opt/mysql 下确实有 my.cnf 但是并没有在启用中，应该是作为一个示例保存在安装目录的吧。所以手动添加配置文件到某个默认目录吧

添加修改mysql配置 
mysqld --help --verbose | more (查看帮助, 按空格下翻)

你会看到开始的这一行(表示配置文件默认读取顺序)

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/local/etc/my.cnf ~/.my.cnf

通常这些位置是没有配置文件的, 所以要自己建一个

ls $(brew --prefix mysql)/support-files/my-* (用这个可以找到样例.cnf) 
cp /usr/local/opt/mysql/support-files/my-default.cnf /etc/my.cnf (拷贝到第一个默认读取目录)

按需修改my.cnf

以下是英文资料 摘自http://stackoverflow.com/questions/7973927/for-homebrew-mysql-installs-wheres-my-cnf 最后一段还是比较直白的



There is no my.cnf by default. As such, MySQL starts with all of the default settings. If you want to create your own my.cnf to override any defaults, place it at /etc/my.cnf.

Also, you can run mysql --help and look through it for the conf locations listed.

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf 
The following groups are read: mysql client
The following options may be given as the first argument:
--print-defaults        Print the program argument list and exit.
--no-defaults           Don't read default options from any option file.
--defaults-file=#       Only read default options from the given file #.
--defaults-extra-file=# Read this file after the global files are read.
As you can see, there are also some options for bypassing the conf files, or specifying other files to read when you invoke mysql on the command line.

shareimprove this answer
edited Feb 4 '14 at 2:44

answered Nov 2 '11 at 0:14

Jericon
1,614918
15      
This no longer seems to be the case; I see a my.cnf file in /usr/local/Cellar/mysql/5.6.15/ (or whichever version you have installed) – William Turrell Dec 30 '13 at 20:02
3       
@williamt "mysql --help" doesn't list that file as being used, I think it's just a default that comes with the installation files – Vinicius Pinto Jan 27 '14 at 17:01
1       
@williamt I think Vinicius is right. I see /usr/local/Cellar/mysql/5.6.20_1/my.cnf but when I added the log-bin to it and restarted mysqld, it still didn't haven't binary logging enabled. It wasn't until I copied that my.cnf to /etc/my.cnf that binary logging was enabled. Perhaps that my.cnf file is used during the initial install, I'm not sure. – Mark Aug 27 '14 at 20:00

I'm on 5.6.26 and I don't see it there. – Adam Grant Oct 1 at 20:19

Docker 修改MySQL配置
作者：YoungJ

前言：大部分程序员在刚使用docker时，按照教程迅速的就装完了mysql，在使用的过程中出现各种问题：比如中文乱码、com.mysql.jdbc.PacketTooBigException: Packet for query is too large (1053 > 1024).等等，最简单的方式通过sql去设置，但每次重启又还原了，那么本文就是通过docker下MySQL的配置去解决该问题。




从安装说起，获取镜像
docker pull mysql:5.7.18



查看镜像
docker images




运行docker容器
docker run -d -p 3306:3306 --name mymysql -e MYSQL_ROOT_PASSWORD=123456 mysql:5.7.18

(* 这里没有设置挂载外部的配置)


查看容器是否启动成功
docker ps




进入容器
docker exec -it 1383f2e49cb7 /bin/bash




查看配置
more /etc/mysql/mysql.conf.d/mysqld.cnf

（* 如果你执行到这步提示没有该文件，别着急，往下看）


编辑配置


第一种方式：将配置从容器中copy到宿主机（之前文章讲到过，可以去看下，如果没有该文件，那这一步省略，稍后直接将我写好的配置放到容器中就好）
docker cp mysql:/etc/mysql/mysql.conf.d/mysqld.cnf /root/mysqld.cnf

mysqld.cnf脚本如下：
# Copyright (c) 2014, 2016, Oracle and/or its affiliates. All rights reserved.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; version 2 of the License.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301 USA

#
# The MySQL  Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
#log-error      = /var/log/mysql/error.log
# By default we only accept connections from localhost
#bind-address   = 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
character_set_server=utf8
init_connect='SET NAMES utf8'
max_allowed_packet = 20M

[mysql]
default-character-set = utf8

[mysql.server]
default-character-set = utf8

[mysqld_safe]
default-character-set = utf8

[client]
default-character-set = utf8

将修改后的配置copy到容器

docker cp /root/mysqld.cnf mymysql:/etc/mysql/mysql.conf.d/

(* mymysql是你容器的name,不清楚的可以通过docker ps看到)


另一种方式，直接编辑

在使用docker容器时，有时候里边没有安装vim，敲vim命令时提示说：vim: command not found，这个时候就需要安装vim，可是当你敲apt-get install vim命令时，提示：

Reading package lists… Done

Building dependency tree

Reading state information… Done

E: Unable to locate package vim

这时候需要敲：
apt-get update

这个命令的作用是：同步 /etc/apt/sources.list 和 /etc/apt/sources.list.d 中列出的源的索引，这样才能获取到最新的软件包。

等更新完毕以后再敲命令：
apt-get install vim

vim /etc/mysql/mysql.conf.d/mysqld.cnf





接下来重启容器就可以了（可以参考之前的文章）
docker restart 1383f2e49cb7 

(* 1383f2e49cb7是容器的CONTAINER ID，可以通过docker ps看到 )



可以参考：

https://blog.csdn.net/i_am_tomato/article/details/80530039
https://www.jb51.net/article/141620.htm

Docker下修改mysql配置

进入容器：

docker exec -it mysql /bin/bash


如果容器中没有安装vim，进行安装：

apt-get update
apt-get install vim


查看配置文件：

cat /etc/mysql/mysql.conf.d/mysqld.cnf


修改配置文件：

vim  /etc/mysql/mysql.conf.d/mysqld.cnf

进入之后进行修改，修改完成后，保存退出

查看docker容器列表：

docker ps

如图：



重启相关的docker容器：

docker restart [CONTAINER ID]

如图