Bobcares

mysqldump error 2020 – The way we fix the error

by | Aug 19, 2019

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 Bobcares, 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].sql

To restoring a database,

mysql -u [username] -p [databaseName] < [filename].sql

Furthermore, 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.sql

Fixing 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.

PREVENT YOUR SERVER FROM CRASHING!

Never again lose customers to poor server speed! Let us help you.

Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.

GET STARTED

var google_conversion_label = "owonCMyG5nEQ0aD71QM";

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

Never again lose customers to poor
server speed! Let us help you.

Privacy Preference Center

Necessary

Necessary cookies help make a website usable by enabling basic functions like page navigation and access to secure areas of the website. The website cannot function properly without these cookies.

PHPSESSID - Preserves user session state across page requests.

gdpr[consent_types] - Used to store user consents.

gdpr[allowed_cookies] - Used to store user allowed cookies.

PHPSESSID, gdpr[consent_types], gdpr[allowed_cookies]
PHPSESSID
WHMCSpKDlPzh2chML

Statistics

Statistic cookies help website owners to understand how visitors interact with websites by collecting and reporting information anonymously.

_ga - Preserves user session state across page requests.

_gat - Used by Google Analytics to throttle request rate

_gid - Registers a unique ID that is used to generate statistical data on how you use the website.

smartlookCookie - Used to collect user device and location information of the site visitors to improve the websites User Experience.

_ga, _gat, _gid
_ga, _gat, _gid
smartlookCookie
_clck, _clsk, CLID, ANONCHK, MR, MUID, SM

Marketing

Marketing cookies are used to track visitors across websites. The intention is to display ads that are relevant and engaging for the individual user and thereby more valuable for publishers and third party advertisers.

IDE - Used by Google DoubleClick to register and report the website user's actions after viewing or clicking one of the advertiser's ads with the purpose of measuring the efficacy of an ad and to present targeted ads to the user.

test_cookie - Used to check if the user's browser supports cookies.

1P_JAR - Google cookie. These cookies are used to collect website statistics and track conversion rates.

NID - Registers a unique ID that identifies a returning user's device. The ID is used for serving ads that are most relevant to the user.

DV - Google ad personalisation

_reb2bgeo - The visitor's geographical location

_reb2bloaded - Whether or not the script loaded for the visitor

_reb2bref - The referring URL for the visit

_reb2bsessionID - The visitor's RB2B session ID

_reb2buid - The visitor's RB2B user ID

IDE, test_cookie, 1P_JAR, NID, DV, NID
IDE, test_cookie
1P_JAR, NID, DV
NID
hblid
_reb2bgeo, _reb2bloaded, _reb2bref, _reb2bsessionID, _reb2buid

Security

These are essential site cookies, used by the google reCAPTCHA. These cookies use an unique identifier to verify if a visitor is human or a bot.

SID, APISID, HSID, NID, PREF
SID, APISID, HSID, NID, PREF