Thesqldump Error 2013

backupMySQLmysqldump

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?

Best Answer

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

Related Solutions

Mysql – Backup / Export data from MySQL 5.5 attachments table keeps failing!

Try increasing your net_write_timeout (probably a default value of 60secs is too small in your scenario of blob and binary values)

Ref:

net_write_timeout

and in general:

net_read_timeout

Mysql – thesqldump is not dumping data by default

Use only

mysqldump -u user -p database

The -d option means no-data :

   --no-data, -d

       Do not write any table row information (that is, do not dump table contents). This is useful if you want to dump only the CREATE TABLE
       statement for the table (for example, to create an empty copy of the table by loading the dump file).