Mac OS X 上安装Mysql配置文件的添加及修改配置文件以及出现1153：Got a packet bigger than‘max_allowed_packet’bytes

 

本人在Mac上使用mysql数据库是平时数量级小的时候还没有体现出来，然而遇到大量数据的导入和默认时间的设置时就不好使了，备份还原或数据导入报错1153：Got a packet bigger than‘max_allowed_packet’bytes

然而Mac上修改配置和在Windows上修改又不一样，特意找了一下资料，在此记录下来，如下引用了几位前辈的文章内容。

转载自：

https://blog.csdn.net/StriverLi/article/details/78637026?locationNum=8&fps=1

https://www.jianshu.com/p/55ba63012185

https://www.jianshu.com/p/d3f1ea79cb65

https://blog.csdn.net/bahaidong/article/details/80475684

正文：

安装教程参考官网：https://dev.mysql.com/doc/refman/8.0/en/installing.html   或者自行百度，很简单在此不做介绍。

了解命令：（实际路径请根据自己安装路径填写，一般都是安装在/usr/local/下边的）

启动MySQL服务

sudo /usr/local/mysql/support-files/mysql.server start

停止MySQL服务

sudo /usr/local/mysql/support-files/mysql.server stop

重启MySQL服务

sudo /usr/local/mysql/support-files/mysql.server restart

在修改或者添加配置文件之前请先停止MySQL服务

在修改或者添加配置文件之前请先停止MySQL服务

在修改或者添加配置文件之前请先停止MySQL服务

mysql在新版中去掉了默认配置文件my.cnf如果需要可以手动创建

 

1）在 /etc 新建 my.cnf 文件 

sudo vim /etc/my.cnf

然后输入开机密码，即可（创建）打开my.cnf 

如果是之前没有my.cnf文件那么就会创建文件，存在就进入修改。

打开后按下键盘I，最下方会出现INSERT 单词，进入编辑模式，代表现在可以修改该文件，只需要修改你想修改的配置即可，注意要在英文输入的状态下按键盘I，修改完成后按下esc,退出编辑模式。然后按下shift+:,输入wq 保存并退出：

常用指令：

:wq   保存后退出vim
:wq!  强制储存后退出
:w    保存但不退出
:w!   若文件属性为只读时，强制写入该档案
:q    退出vi
:q!   若曾修改过档案，又不想储存，使用 ! 为强制离开不储存档案。

2）将如下配置内容写入到文件中

# Example MySQL config file for medium systems.  
  #  
  # This is for a system with little memory (32M - 64M) where MySQL plays  
  # an important part, or systems up to 128M where MySQL is used together with  
  # other programs (such as a web server)  
  #  
  # MySQL programs look for option files in a set of  
  # locations which depend on the deployment platform.  
  # You can copy this option file to one of those  
  # locations. For information about these locations, see:  
  # http://dev.mysql.com/doc/mysql/en/option-files.html  
  #  
  # In this file, you can use all long options that a program supports.  
  # If you want to know which options a program supports, run the program  
  # with the "--help" option.  
  # The following options will be passed to all MySQL clients  
  [client]
  default-character-set=utf8
  #password   = your_password  
  port        = 3306  
  socket      = /tmp/mysql.sock   
  # Here follows entries for some specific programs  
  # The MySQL server  
  [mysqld]
  character-set-server=utf8
  init_connect='SET NAMES utf8
  port        = 3306  
  socket      = /tmp/mysql.sock  
  skip-external-locking  
  key_buffer_size = 16M  
  max_allowed_packet = 10M  
  table_open_cache = 64  
  sort_buffer_size = 512K  
  net_buffer_length = 8K  
  read_buffer_size = 256K  
  read_rnd_buffer_size = 512K  
  myisam_sort_buffer_size = 8M  
  character-set-server=utf8  
  init_connect='SET NAMES utf8' 
# Don't listen on a TCP/IP port at all. This can be a security enhancement,  
# if all processes that need to connect to mysqld run on the same host.  
# All interaction with mysqld must be made via Unix sockets or named pipes.  
# Note that using this option without enabling named pipes on Windows  
# (via the "enable-named-pipe" option) will render mysqld useless!  
#   
#skip-networking  
 
# Replication Master Server (default)  
# binary logging is required for replication  
  log-bin=mysql-bin  
 
    # binary logging format - mixed recommended  
    binlog_format=mixed  
 
      # required unique id between 1 and 2^32 - 1  
      # defaults to 1 if master-host is not set  
      # but will not function as a master if omitted  
      server-id   = 1  
 
    # Replication Slave (comment out master section to use this)  
    #  
    # To configure this host as a replication slave, you can choose between  
    # two methods :  
    #  
    # 1) Use the CHANGE MASTER TO command (fully described in our manual) -  
    #    the syntax is:  
    #  
    #    CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,  
    #    MASTER_USER=<user>, MASTER_PASSWORD=<password> ;  
    #  
    #    where you replace <host>, <user>, <password> by quoted strings and  
    #    <port> by the master's port number (3306 by default).  
    #  
    #    Example:  
    #  
    #    CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,  
    #    MASTER_USER='joe', MASTER_PASSWORD='secret';  
    #  
    # OR  
    #  
    # 2) Set the variables below. However, in case you choose this method, then  
    #    start replication for the first time (even unsuccessfully, for example  
    #    if you mistyped the password in master-password and the slave fails to  
    #    connect), the slave will create a master.info file, and any later  
    #    change in this file to the variables' values below will be ignored and  
    #    overridden by the content of the master.info file, unless you shutdown  
    #    the slave server, delete master.info and restart the slaver server.  
    #    For that reason, you may want to leave the lines below untouched  
    #    (commented) and instead use CHANGE MASTER TO (see above)  
    #  
    # required unique id between 2 and 2^32 - 1  
    # (and different from the master)  
    # defaults to 2 if master-host is set  
    # but will not function as a slave if omitted  
    #server-id       = 2  
    #  
    # The replication master for this slave - required  
    #master-host     =   <hostname>  
    #  
    # The username the slave will use for authentication when connecting  
    # to the master - required  
    #master-user     =   <username>  
    #  
    # The password the slave will authenticate with when connecting to  
    # the master - required  
    #master-password =   <password>  
    #  
    # The port the master is listening on.  
    # optional - defaults to 3306  
    #master-port     =  <port>  
    #  
    # binary logging - not required for slaves, but recommended  
    #log-bin=mysql-bin  
 
      # Uncomment the following if you are using InnoDB tables  
      #innodb_data_home_dir = /usr/local/mysql/data  
      #innodb_data_file_path = ibdata1:10M:autoextend  
      #innodb_log_group_home_dir = /usr/local/mysql/data  
      # You can set .._buffer_pool_size up to 50 - 80 %  
      # of RAM but beware of setting memory usage too high  
      #innodb_buffer_pool_size = 16M  
      #innodb_additional_mem_pool_size = 2M  
      # Set .._log_file_size to 25 % of buffer pool size  
      #innodb_log_file_size = 5M  
      #innodb_log_buffer_size = 8M  
      #innodb_flush_log_at_trx_commit = 1  
      #innodb_lock_wait_timeout = 50  
 
        [mysqldump]  
        quick  
        max_allowed_packet = 16M  
 
          [mysql]  
          no-auto-rehash  
          # Remove the next comment character if you are not familiar with SQL  
          #safe-updates  
          default-character-set=utf8   
 
        [myisamchk]  
        key_buffer_size = 20M  
        sort_buffer_size = 20M  
        read_buffer = 2M  
        write_buffer = 2M  
 
          [mysqlhotcopy]  
          interactive-timeout

3）保存文件 

esc,退出编辑模式,输入:wq 保存并退出

4）修改文件读写权限

sudo chmod 664 /etc/my.cnf    

一定要修改为664，不然MySQL读取文件的时候权限会出错

最后附上my.cnf配置文件，已经配置好了，编码格式也是正确的，下载后，
！！！切记不要使用电脑自带的文本软件打开该文件，这样会改变文件的编码格式，以后修改配置的时候也是使用vim命令去编辑

下载链接：
链接: https://pan.baidu.com/s/1bzP4LZjsVn4Iqot2vmjduw 提取码: ma6k 

然后我们启动MySQL服务就可以使用了

sudo /usr/local/mysql/support-files/mysql.server start  （实际路径请根据自己安装路径填写，一般都是安装在/usr/local/下边的）

【友情提示：在配置后，MySQL服务器如果启动不了。不要慌，我们只需要重启电脑就可以了。】

可在mysql命令行通过命令：

show VARIABLES like '%max_allowed_packet%';

查看是否修改成功！在此我max_allowed_packet修改为了300M