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
Post a Comment for "When I try to Backup and the Output Error is mysqldump error 2020 max allowed packet"