Exporting UTF8 table

I'm having a problem with dumping a UTF-8 table to an sql file. The table has many non-ascii characters in it, but when I export it, the file has them encoded as an odd sequence of hex numbers.

I'm not sure if it's something to do with how the table is set up but I'm hoping someone can help me with this as my backup is now useless.

An example, the letter ö appears as it should in the table, but in the sql file, it appears as <C3><83><C2><B6> and when I reimport it back through Heidi, it appears as garbage.

Can anyone please help?

looks like a multiple encoding to me - ie result of "encode something from other charset to utf8, then encode result to utf8 again".
check that your server variables charset_* all have the value "utf8" (except character_set_filesystem which should be "binary"); if any of these is different, mysql will automatically translate the utf8 values to that charset on output (may happen several times).

Quite a few other users experienced similar problems. Mostly it turned out that HeidiSQL misdetects the encoding of its self produced UTF8 file. Solution was mostly to select "UTF8" in the open-file dialog's "Encoding" drop down.

good idea - except that <C3><83><C2><B6> is not the utf8 code for ö (<00><F6> is). Also the presence of two "prefixed" characters ((C|D) xx - 16bit utf8) in place of one source character seems to indicate to me a double encoding.

Thanks for the responses. All the character_set variables are set to utf8 (except the filesystem one) so it can't be that. Any other ideas?

One thing I've noticed is when I do an 'Export as SQL' from one session to another, the encoding is fine. It's only when I export to a file and back in that it goes wrong

Which encoding did you select in the file-open dialog? As said, leaving it to "Auto-detect" probably misdetects the file's encoding. Select "UTF-8" testwise.