Mysqldump utility is one the quickest way to generate database backup.
However, MySql configuration limits or incorrect settings may lead to mysqldump error 2020.
And backup of a large database often results in Error 2020: Got packet bigger than 'max_allowed_packet' bytes when dumping table.
At 1onlyhost , we often get requests from our customers to fix mysqldump error 2020 error as part of our Server Management Services.
Today, in this write-up we’ll see how our Support Engineers fix these MySQLdump errors.
How is mysqldump useful?
Mysqldump is a database management tool used to take a MySQL database and “dump” it out as a text file that contains a set of SQL statements. Also, it can be used for creating exports of a database as backups and restoring the backup.
In addition, when moving the database to a new server, it will be useful as a tool for restoring a database to a new server.
To back up a database,
mysqldump -u [username] -p [databaseName] > [filename].sqlTo restoring a database,
mysql -u [username] -p [databaseName] < [filename].sqlFurthermore, mysqldump utility is quite popular in database management as it can be simply operated from the command line.
How we fix common mysqldump related errors
From our experience in managing servers, we’ve seen customers facing different kinds of problems when using the mysqldump command. The top reason is often incorrect MySQL configuration limits.
Now, let’s see the topmost reason for mysqldump error 2020 and how our Support Team solved this common error.
Global parameter settings
Recently, one of our customers had a problem while taking the backup of huge databases using mysqldump, the backup process resulted in the following error:
mysqldump: Error 2020: Got packet bigger than 'max_allowed_packet' bytes when dumping table `log` at row:
Then, our Support Engineers found that the global parameter ‘max_allowed_packet’ was set to a low value which caused the error. Here, the database size was high and this caused further errors.
So, we increased the value of ‘max_allowed_packet’ and took the backup using the following command:
mysqldump --max_allowed_packet=1073741824 databasename > databasename.sqlFixing mysqldump errors permanently
Often, modifying the MySQL variables via the command line will not work especially when you need to backup many databases. In such cases, our Support Engineers first check the existing MySQL variables set on the server. For example, on a Plesk server the variable setting will appear as:
To fix the error, we adjust the value of variables in the MySQL configuration file. Depending on the server setup, the MySQL configuration file can be found at /etc/my.cnf
We open the my.cnf file and add max_allowed_packet=512M to [mysqldump] section.
[mysqldump]
max_allowed_packet=512M
wait_timeout = 1200[Need assistance to fix mysqldump error 2020? We’ll help you.]
Conclusion
In short, mysqldump helps to create a MySQL backup easily. However, mysqldump error 2020 error may occur due to incorrect max_allowed_packet size, wrong MySQL configuration settings, etc. Today, we saw how our Support Engineers fixed this error.
