Mysqldump error 2020 max allowed packet in Linux RHEL 7

Mysqldump error 2020 max allowed packet in Linux RHEL 7 - 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 it a little.

Several methods can be edited in 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 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 set up the max allowed packet value in MySQL

login to myself :

# 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

An example of a restore that I did is shown below :

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

And that's how to overcome the output error from "mysqldump: Error 2020: Got packet bigger than 'max_allowed_packet' bytes when dumping table Kitsake at row: 1494812".

Maybe that's all I can share with you guys, hopefully, this article will be useful.

Thank You.