mysqldump Error 2013

Question

I have a database installed, that I would like to backup in mysql. The problem is mysqldump fails on exporting the 'maia_mail' table

# mysqldump -u root -p maia > maia.sql
mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `maia_mail` at row: 15

It runs for less than 30 seconds and gets error out as above.

The total size of the DB is 1.3GB with the maia_mail table being 1.0GB

In my.cnf I have these set:

[mysqld]
max_allowed_packet      = 1300M
[mysqldump]
max_allowed_packet      = 1300M

Please advise or give some guidance on how to dump the database?

Answer 1

I could easily suggest changing InnoDB settings which might be a littel heavy-handed just to get a mysqldump to work. You may not like what I am about the suggest, but I believe it's your best (only) option. Here it goes:

SUGGESTION #1 : Disable extended inserts

The default setting for mysqldump would include clumping together hundreds or thousands of rows in a single INSERT. This is known as an extended INSERT. It is causing some overrun beyond just max_allowed_packet.

I answered a post back on Sep 01, 2011 (MySQL server has gone away obstructing import of large dumps) where I discussed doing the same thing for importing a large mysqldump. I believe disabling extended INSERT would help with creating a troublesome mysqldump as well.

mysqldump -u root --skip-extended-insert -p maia > maia.sql

Bad news: What this does in create an INSERT command for each row. This will definitely increase the time it takes to perform the mysqldump. Consequently, it will also increase to time is take to reload (probably by a factor of 10-100.

I have discussed skip-extended-insert before

• Aug 12, 2011 : Why is mysqldump file so large?
• Aug 09, 2013 : Backup / Export data from MySQL 5.5 attachments table keeps failing!
• Nov 16, 2014 : How to batch multiple insert statements for database dump with MySQL?

SUGGESTION #2 : Dump binary data as hex (OPTIONAL)

To make the mysqldump's binary data more byte portable, dump such data in hexadecimal

mysqldump -u root --skip-extended-insert --hex-blob -p maia > maia.sql

Bad News: It will bloat the mysqldump a little more

GIVE IT A TRY !!!

Side Note : The maximum size of max_allowed_packet is 1G

Answer 2

I was also getting the same error while trying to dump 12 GB database. I did the following changes to make it work.

1. Configured max_allowed_packet to 1024M
2. Configured net_read_timeout to 7200
3. Configured net_write_timeout to 7200

Note: I know the time out values are way too high (7200 seconds ie 2 hours). But I did so itentionally just to rule out any chance. I am in the process of finding an optimal timeout value.

Answer 3

Just include the following on your my.ini (Windows) or my.cnf (Linux) configuration file.

[mysqld]
max_allowed_packet=1024M 
net_read_timeout=3600 
net_write_timeout=3600

[mysqldump]
max_allowed_packet=1024M 

Answer 4

I found:

--max-allowed-packet=1G --net-buffer-length=32704

...makes it work where it didn't (reliably) previously, despite net read/write timeout changes, TCP keepalives etc.

The max_allowed_packet settings alone didn't make it work, so may not be required if net_buffer_length is used. - ralph-bolton

Modifying max-allowed-packet and net-buffer-length seems much better than disabling extended inserts. - kristofer

See also What max_allowed_packet is big enough, and why do I need to change it?

Answer 5

Make sure you have enough memory to take dump. Please keep checking memory while taking dump, e.g. using a command like this:

free -mt

If you memory exhaust while taking dump you will get

mysqldump: Error 2013: Lost connection