Thursday, August 21, 2008

Change mysql max_allowed_packet variable

There are several ways to change the max_allowed_packet variable size in mysql

1. Edit max_allowed_packet entry of /etc/my.cnf file (on Linux). Change the following line

[mysqld]
max_allowed_packet=16M

2. Start the mysql server with the --max_allowed_packet option.

/etc/init.d/mysql restart --max_allowed_packet=1024M

3. To edit the max_allowed_packet without restarting the server.

mysql> show variables like 'max_allowed_packet%' ;
+--------------------+---------+
| Variable_name | Value |
+--------------------+---------+
| max_allowed_packet | 1048576 |
+--------------------+---------+
1 row in set (0.00 sec)

mysql> set max_allowed_packet = 1073741824;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'max_allowed_packet%' ;
+-----------------------------------+---------------------+
| Variable_name | Value |
+----------------------------------+---------------------+
| max_allowed_packet | 1073741824 |
+--------------------+------------+----------------------+

4 comments:

jamil said...

Hi Krishantha,

when i restart the mysql, the set variables will be back to its original value "1048576".

how will i change the value of max_allowed_packet permanently to 1073741824?...

thank you...

jamil said...

Hi Krishantha,

when i restart the mysql, the set variables will be back to its original value "1048576".

how will i change the value of max_allowed_packet permanently to 1073741824?...

thank you...

Krishantha said...

Hijamil,

I think best way is to edit the mysql my.cnf file and set the value.

thanks

ranjith said...

@jamil :

Edit the my.conf file using any editor .( I use vi editor) and save the file after editing it .
May be you forgot saving it .!! --hope this helps

In command mode at vi -editor:

:wq! to save and quit