HostOnNet Blog

Ubuntu 16 mysqldump Got packet bigger than max_allowed_packet

When taking MySQL backup with mysqldump, i get following error

root@server1:~# mysqldump db-name > db-name.sql
mysqldump: Error 2020: Got packet bigger than 'max_allowed_packet' bytes when dumping table `wp_posts` at row: 32208
root@server1:~# 

On Ubuntu 16.04, this error is fixed by editing

vi /etc/mysql/mariadb.conf.d/50-mysql-clients.cnf

Find

[mysqldump]

Add brlow

max_allowed_packet      = 256M

Here is the modified file

root@server1:~# cat /etc/mysql/mariadb.conf.d/50-mysql-clients.cnf
#
# These groups are read by MariaDB command-line tools
# Use it for options that affect only one utility
#

[mysql]
# Default is Latin1, if you need UTF-8 set this (also in server section)
default-character-set = utf8mb4

[mysql_upgrade]

[mysqladmin]

[mysqlbinlog]

[mysqlcheck]

[mysqldump]
max_allowed_packet      = 256M

[mysqlimport]

[mysqlshow]

[mysqlslap]
root@server1:~# 

Edit 50-server.cnf

vi /etc/mysql/mariadb.conf.d/50-server.cnf

Find

max_allowed_packet

Update value to higher, something like

max_allowed_packet      = 256M

Now restart MySQL

service mysql restart

Verify it with

mysql
show variables like 'max_allowed_packet';

Example

root@server1:~# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3220
Server version: 10.0.31-MariaDB-0ubuntu0.16.04.2 Ubuntu 16.04

Copyright (c) 2000, 2017, 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 | 268435456 |
+--------------------+-----------+
1 row in set (0.00 sec)

MariaDB [(none)]> exit
Bye
root@server1:~# 

See mysql


Posted in MySQL