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

Question

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??

Thanks in hope and advance!

A little more information below - following some questions and advice:

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

Answer 1

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

Answer 2

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 !!!

Answer 3

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.