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:

max_allowed_packet      = 1300M
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
    Commented Apr 14, 2015 at 19:49
  • copies of email so varchar data mainly
    – garfink
    Commented Apr 14, 2015 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
    Commented Apr 14, 2015 at 20:17
  • I set back to the 16M default. dump results in the same error 2013 on row 15
    – garfink
    Commented Apr 14, 2015 at 20:29

5 Answers 5


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


Side Note : The maximum size of max_allowed_packet is 1G


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.

  • 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
    Commented Aug 22, 2016 at 6:55
  • set global net_read_timeout = 120; set global net_write_timeout = 900; worked for me
    – kasi
    Commented Jun 9, 2017 at 11:26
  • Correction: 7200 seconds = 2 hours (not 20)
    – Brandon
    Commented Aug 21, 2021 at 12:22

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


  • 2
    The sections should be the other way around.
    – OrangeDog
    Commented Dec 12, 2018 at 12:32
  • I agree. This is the only solution that simply works! Commented Dec 17, 2021 at 20:50
  • This solution works - I think the key is that [mysqldump] section for the max_allowed_packet variable
    – Bogdan
    Commented May 2, 2023 at 20:55

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?


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

  • Using --quick avoids using a buffer and thus running out of system memory.
    – Brandon
    Commented Aug 21, 2021 at 18:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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