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
Best Answer
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