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

Can anyone please help! - I have a large table in a MySQL 5.5 database. It is a table which holds a mixture of blobs/binary data and just data rows with links to file paths. It has just over a million rows.

I am having desperate problems in getting the data out of this table to migrate it to another server.

I have tried all sorts - mysqldump (with and without -quick), dumping the results of a query via the command line. Using a MySQL admin tool (Navicat) to open and export the data to file, CSV, or do a data transfer (line by line) to another DB and/or another server but all to no avail.

When trying to use the DB admin tool (Navicat), it gets to approx 250k records and then fails with an "Out of memory" error. I am not able to get any error messages from the other processes I have tried, but they seem to fall over at approximately the same number of records.

I have tried playing with the MySQL memory variables (buffer size, log file size, etc) and this does seem to have an effect on where the export stops (currently I have actually made it worse).

Also - max_allowed_packet is set to something ridiculously large as I am aware this can be a problem too.

I am really shooting in the dark, and I keep going round and round trying the same things and getting no further. Can anyone give me any specific guidance, or recommend perhaps any tools which I might be able to use to extract this data out??

The size of the table I am trying to dump - it is difficult to say, but the sql dump gets to 27gb when the mysqldump dies. It could be approximately 4 times that in total.

I have tried running the following mysqldump command:

mysqldump --single-transaction --quick mydatabase attachments --password=abc123 -u root > d:\attachments.sql


And this gives the error:

mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table attachments at row: 251249 – Mayb2Moro 4 hours ago

The server has 8gb RAM, Some of the relevant settings copied below. It is an INNODB database/table.

innodb_buffer_pool_size=3000M
innodb_log_file_size=1113M
max_allowed_packet=2024M
query_cache_size=52M
tmp_table_size=500M
myisam_sort_buffer_size=50M

• What mysqldump commands did you run and what error messages did you receive? – James Lupolt Aug 9 '13 at 8:52
• What is the data size of table you taking as dump and what is the available memory to the MySQL?. – Vinay Aug 9 '13 at 11:15
• mysqldump --single-transaction --quick mydatabase attachments --password=abc123 -u root > d:\attachments.sql To be honest, I am not sure of the error as I have been running in a batch file most recently, but all errors no matter how I try to get the data out seem to be "lost connection to mysql server" or "out of memory". It seems to hit the same point and stop every time - as though it is a bad/huge record? I will run the above again from the command line and get the precise error. – Mayb2Moro Aug 9 '13 at 12:16
• The size of the table - difficult to say, but the sql dump gets to 27gb when the mysqldump dies. It could be approximately 4 times that in total. – Mayb2Moro Aug 9 '13 at 13:12
• The server has 16gb RAM, Some of the relevant settings copied below. It is an INNODB database/table. innodb_buffer_pool_size=3000M, innodb_log_file_size=1113M, max_allowed_packet=2024M, query_cache_size=52M, tmp_table_size=500M, myisam_sort_buffer_size=50M – Mayb2Moro Aug 9 '13 at 13:15

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:

• Ah, brilliant, thanks I will give this a go! – Mayb2Moro Feb 6 '14 at 13:13
• Sadly, still the exact same error - again down to the exact same row :( – Mayb2Moro Feb 10 '14 at 8:52

This was the mysqldump command you ran

mysqldump --single-transaction --quick mydatabase attachments --password=abc123 -u root > d:\attachments.sql


There are two parameters you should try

# extended-insert

Someone wrote this post in the MySQL Documentation

Posted by Enrico Modanese on July 13 2006 1:05pm

I often get errors [MySQL 4.* and 5.*] on reloading a dump of databases having big blobs. I found the solution disabling the --extended-insert (that comes inside the multiple option --opt, enabled by default) with --skip-extended-insert. I think this way is safer, but it is also more more slow.

# hex-blob (Optional)

Dump binary columns using hexadecimal notation (for example, 'abc' becomes 0x616263). The affected data types are BINARY, VARBINARY, the BLOB types, and BIT.

# SUMMARY

Your mysqldump should now look like this

mysqldump --single-transaction --skip-extended-insert --hex-blob --quick mydatabase attachments --password=abc123 -u root > d:\attachments.sql


# CAVEAT

Using --skip-extended-insert will make each row be a single INSERT command. This will cause two problems

1. much slower mysqldump
2. much bigger mysqldump

Make sure Drive D: has enough space (Maybe 2TB ???)

# Give it a Try !!!

• Unfortunately this gives the same error :( Right down to the same row number: mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table attachments at row: 251249`. Is there anyway to just skip that row? I have tried to output using a mysql query limiting the rows, but that fails with "lost connection to mysql server" too! – Mayb2Moro Aug 12 '13 at 7:58

Try with pentaho data integration (kettle for the oldest). Is an etl tool for moving and transform data from dB to dB (or dB to file). Give it a try an learn a new cool tool.

Regards.