Exported BLOB data other than database data

Question

I am trying to export our very large MySQL database (1.6 GB - mostly BLOB) and import to a new server. I dealt with most of the problems and finally completed the import without errors. Using MySQL Query Browser I executed a query in a table with BLOB images and saved one to disk (using the save icon in the query browser). When I tried to open the file, I got the error "wrong image format." Oh. A.

Using the query browser, I checked the value in the source database and the new recently imported database. I believe that the values ​​are different. It could just be coding problems or something like that. Here is what I see:

Source Server (valid data):

FF D8 FF E0 00 10 4A 46 49 46 00 01 01 01 00 60 00 60 00 00 FF DB 00 43 00 08 06 06 07 06 05 08 and so on... 

New server:

 C3 BF C3 98 C3 BF C3 A0 00 10 4A 46 49 46 00 01 01 01 00 60 00 60 00 00 C3 BF C3 9B 00 43 00 08 and so on... 

In this example, a message appears before my newcomer that there are 3 bytes of additional data in front of the data on the "new" server.

Then I checked the sql dump file with the 010 editor . I found a line for this specific example, and here is what I see:

 FF D8 FF E0 5C 30 10 4A 46 49 46 5C 30 01 01 01 5C 30 60 5C 30 60 5C 30 5C 30 FF DB 5C 30 43 5C 30 08 06 06 07 06 05 08 and so on... 

Now I'm over my head. I see a template, I notice that a pair of HEX 5C 30 looks the same as 00, but I don’t understand WHY. At this point, I have a source server that is about to be wiped out, and a new one, which I fear has corrupted data imported into it. I hope this is some kind of encoding problem that can be solved by setting a global variable in MySQL, but I really don't know.

I should also note that when saving files from the original (working) server and the new (damaged) server, the file size is approximately 40% larger for the damaged file.

I checked the character set variables on both servers:

 SHOW VARIABLES LIKE '%char%' 

source server:

 character_set_client utf8 character_set_connection utf8 character_set_database latin1 character_set_filesystem binary character_set_results utf8 character_set_server latin1 character_set_system utf8 

new server:

 character_set_client utf8 character_set_connection utf8 character_set_database latin1 character_set_filesystem binary character_set_results utf8 character_set_server latin1 character_set_system utf8 

They are the same.

Answer 1

Corrupted data from the new database looks like the result of converting the original data from ISO-8859-1 to UTF-8 (for example, U + 00FF - ÿ is FF in the first and C3 BF in the last).

Because BLOBs do not have a character set, the character encoding is not controlled by server variables; I suspect mysqldump outputting your BLOB data to a UTF-8 encoded file ( which is the default ) and it is being encoded along somehow through some combination of server parameters and parameters passed to mysqldump .

A better solution might be to use the --hex-blob option when exporting BLOB fields, which will result in something like:

 INSERT INTO `table` VALUES (0xFFD8FFE0...);