18

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?

  • 170GB free space. It's also the same whether I dump to machine db is on or remote – garfink Apr 14 '15 at 19:49
  • copies of email so varchar data mainly – garfink Apr 14 '15 at 20:01
  • the 1300M was a recent change, the problem existed when it was set to the default 16M too. Server also had been restarted after the change to 1300M. – garfink Apr 14 '15 at 20:17
  • I set back to the 16M default. dump results in the same error 2013 on row 15 – garfink Apr 14 '15 at 20:29
13

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

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

5

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 i e 20hrs). But I did so itentionally just to rule out any chance. I am in the process of finding an optimal timeout value.

  • 2
    For other users: these are setting on the server, not in the mysqldump config file. Also, 7200 seconds is 2 hours, not 20. – Mark Aug 22 '16 at 6:55
  • set global net_read_timeout = 120; set global net_write_timeout = 900; worked for me – kasi Jun 9 '17 at 11:26
3

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

[mysqld]
max_allowed_packet=1024M 

[mysqldump]
max_allowed_packet=1024M 
net_read_timeout=3600 
net_write_timeout=3600
  • 2
    The sections should be the other way around. – OrangeDog Dec 12 '18 at 12:32
1

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

1

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?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.