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

On Ubuntu 16.04, this error is fixed by editing

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



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

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





max_allowed_packet      = 256M




Edit 50-server.cnf

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



Update value to higher, something like

max_allowed_packet      = 256M

Now restart MySQL

service mysql restart

Verify it with

show variables like 'max_allowed_packet';


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

See mysql

Posted in MySQL