CODEWITHSUNDEEP
mysqlccharacter-encoding
Casualet
Casualet

Reputation: 305

How does mysqldump write binary data into files for MySQL logical backup?

I am using mysqldump to back up a table. The schema is as follows:

CREATE TABLE `student` (
  `ID` bigint(20) unsigned DEFAULT NULL,
  `DATA` varbinary(64) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

I can use the following command to backup my data in the table.

mysqldump -uroot -p123456 tdb > dump.sql.

Now I want to write my own code using the MySQL c interface to generate the file similar to dump.sql. So I just

However, when I check the table fields written into the file, I find that the file generated by mysqldump and by my own program are different.For example,

So, My question is: Why is writting data using sprintf(f,"%s",xx) for backup not correct? Is it enough to just add ' ' in the front and end of the string? If so, what if the data of that field happen to have ' in it?

Also, I wonder what it means to write some unprintable characters into a text file.

Also, I read stackoverflow.com/questions/16559086 and tried --hex-blob option. Is it OK if I transform every byte of the binary data into hex form and then write simple text strings into the dump.sql.

Then, instead of getting '[[ \\^X\í^G\ÑX` C;·Qù^Dô7<8a>¼!{<96>aÓ¹<8c> HÀaHr^Q^^½n÷^Kþ<98>IZ<9f>3þ' I got something like 0x5B5B095C18ED07D1586009433BB751F95E44F4378ABC217B9661D3B98C0948C0614872111EBD6EF70BFE98495A9F33FE All the characters are printable now!

However, If I choose this method, I wonder if I can meet problems when I use other encoding schemes other than latin1.

Also, the above words are all my own ideas, I also wonder I there are other ways to back up data using the C interface.

Thank you for your help!

Upvotes: 1

Views: 2021

Answers (1)

Rick James
Rick James

Reputation: 142218

latin1, utf8, etc are CHARACTER SETs. They apply to TEXT and VARCHAR columns, not BLOB and VARBINARY columns.

Using --hex-blob is a good idea.

If you have "unprintable characters" in TEXT or CHAR, then either you have been trying to put a BLOB into such -- naughty -- or the print mechanism does is not set for the appropriate charset.

Upvotes: 2

Related Questions