I have inherited a large problem. We have a database that stores customer details in various tables. One of the fields in the table is of type mediumblob.
Unfortunately there was a disk failure. The backups of the database were been done by the mysqldump command. The exact command that was used was incorrect and it also did not take the blob into account.
The dump command was as follows:
mysqldump -l -F -e -u root -p --fields-terminated-by=, --fields-enclosed-by=\\ –quote-names $database -T $BACKUPDIR
Unfortunately this resulted in Mysql adding escape characters to the binary data and also enclosing all fields with " --quote-names" for example: “–quote-namesT1|1|test.htm --quote-names,”
I have managed to load the data into mysql (using both mysqlimport and LOAD DATA) but my understanding is that these commands dont support blobs(?) so not sure how else to do it.
Using LOAD DATA infile … enclosed by ’ --quote-names ’ doesnt work as it needs to be a single character. So once I got the data loaded using just load data local infile … into table terminated by ‘,’;
I then used the SQL query:
update table set fieldname=(replace(fieldname,’ --quote-names’,‘’));
to get rid of the --quote-names from enclosing the data in each field.
So my question is as follows, and don’t laugh, is there by any chance that there is some magical step/tool that I could do to restore this data and that the binary data is restored correctly to its original format?
Mysql v5.0.45 using MyISAM.