When I try to Backup and the Output Error is mysqldump error 2020 max allowed packet

Hi everyone, this time I will share my experience where I got stuck. When backing up one of my databases, I get this error which causes the backup to fail in the middle when the backup process occurs.

Maybe this story from my experience can be a reference and solve your problems too, just look at the root cause.

Issue

Once upon a time I was doing a mysql database backup on linux and I got an error output like this:

mysqldump: Error 2020: Got packet bigger than 'max_allowed_packet' bytes when dumping table `Kitsake` at row: 1494812.

error when backup databases

Cause

In this issue, I can conclude that there is a limitation given by mysql which only gives a value of 4194304. And this is the default value given by mysql "CMIIW". So we have to modify a little.

There are several methods that can be edited in the my.cnf configuration and need to restart the mysql, you can also type the command in mysql, it has an immediate effect, but when restarting the mysql service will disappear to default again, or you can also directly add options when backup.

Variable Max Allowed Packet

Resolution

So here I just use the fast method. The issue above will be resolved and resolved when you enter the option --max_allowed_packet = 512M or greater ..

You can adjust the value as needed.

Trying

For Backup

For the example I usually do, when backup the database and the backup will be used for the slave database on replication, and at that time I get an error like the one above I will add an option like this:

# mysqldump -u root -p -h 192.168.50.27 --skip-lock-tables --single-transaction --flush-logs --hex-blob --routines --opt --master-data = 2 --max_allowed_packet = 1G --databases kitsake blogger> blogger16112020.sql

Trying Dumping Again With Option Max Allowed Packet

For Restore

Usually when a database restore is generated with a command like this, it won't work when restoring with a normal command like:

# mysql -u root -p < blogger16112020.sql

It will bring up an error in the middle of the backup process:

ERROR 2006 (HY000) at line 23002: MySQL server has gone away

ERROR 2006 (HY000) at line 23002 MySQL server has gone away

To fix this you also have to add options such as during backup, namely the option --max_allowed_packet = 512M or greater.

Before you give the command to restore you have to setup the max allowed packet value in mysql

login to mysqld :

# mysql -u root -p
password :

mysql> show global variables like '%max_allowed_packet%';
mysql> set global max_allowed_packet = 1073741824;
mysql> show global variables like '%max_allowed_packet%';

exit

set global max allowed packet

For an example of a restore that I did is as shown below :

# mysql -u root -p --max_allowed_packet = 1G < blogger16112020.sql