Shulou(Shulou.com)05/31 Report--

It is believed that many inexperienced people have no idea about how to set up the max_allowed_packe of MySQL in Linux. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

Mysql limits the packet size accepted by server based on the configuration file.

Sometimes large inserts and updates are limited by the max_allowed_packet parameter, resulting in failure.

View current configuration

Show VARIABLES like'% max_allowed_packet%'

The result displayed is:

+-+ +

| | Variable_name | Value |

+-+ +

| | max_allowed_packet | 1048576 | |

+-+ +

The current configuration is as follows: 1m

Modification method

1. Method 1

You can edit my.cnf to modify it (my.ini under windows), and modify it in the [mysqld] section or the server configuration section of mysql.

Max_allowed_packet = 20m

If you can't find my.cnf, you can go through

Mysql-- help | grep my.cnf

To find the my.cnf file.

[root@localhost usr] # mysql-- help | grep my.cnf

Order of preference, my.cnf, $MYSQL_TCP_PORT

/ etc/my.cnf / etc/mysql/my.cnf / usr/etc/my.cnf ~ / .my.cnf

Under linux, you will find that none of the above files may exist.

1) first determine the path of the configuration file used (if it is not started, start it first)

[root@localhost usr] # ps aux | grep mysql

Root 14688 0.0 11336 1404 pts/0 S 19:07 0:00 / bin/sh / usr/bin/mysqld_safe-- datadir=/var/lib/mysql-- pid-file=/var/lib/mysql/localhost.localdomain138.pid

Mysql 14791 0.0 15.4 1076700 451336 pts/0 Sl 19:07 0:00 / usr/sbin/mysqld-basedir=/usr-datadir=/var/lib/mysql-plugin-dir=/usr/lib64/mysql/plugin-user=mysql-log-error=/var/lib/mysql/localhost.localdomain138.err-pid-file=/var/lib/mysql/localhost.localdomain138.pid

Root 14835 0.0 201584 2504 pts/0 S + 19:09 0:00 mysql-u root-p

Root 15143 0.0 103244 828 pts/1 S+ 19:40 0:00 grep mysql

Find the line in mysqld or mysqld_safe and look at basedir=/path/file. That / path/file is the path to the configuration file.

2) you can also create / etc/my.cnf directly, or find a my.cnf or my-small.cnf copy to / etc/my.cnf,mysql from the relevant directory of your installed mysql (which may be / usr/include/mysql or / usr/share/mysql). This configuration file will be preferred when starting.

You can look for my.cnf-like filenames in the / etc directory with the following command:

[root@localhost usr] # find-name "my*.cnf"

. / my.cnf

. / share/mysql/my-default.cnf

. / share/doc/MySQL-server-5.6.16/my-default.cnf

. / my-new.cnf

3) with the configuration file, add some commonly used configuration parameters under [mysqld] in the configuration file. This parameter will take effect after restarting the mysql server.

Max_allowed_packet=32M

2. Method 2

(a very compromising and tangled approach)

Enter mysql server

Run from the mysql command line

Set global max_allowed_packet = 2 "1024" 1024 "10

Exit the mysql command line and log in again.

Show VARIABLES like'% max_allowed_packet%'

Check to see if max_allowed_packet is edited successfully.

Note: in method 2, if the mysql service is restarted, the value of max_allowed_packet will be restored to the default initial value, and the value set on the command line will not take effect.

If you really can't find the my.cnf file, you can just copy the .cnf file in the / usr/share/mysql directory to the / etc directory and rename it to my.cnf.

After reading the above, have you mastered how to set the max_allowed_packe of MySQL in Linux? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.