MySQL dump-restore replaces characters with 3Fh

Question

FreeBSD Server with Mysql and make.conf like that

.if ${.CURDIR} == ${PORTSDIR}/databases/mysql50-server
WITH_CHARSET=cp1251
WITH_COLLATION=cp1251_bin
BUILD_OPTIMIZED=yes
BUILD_STATIC=yes
.endif
.if ${.CURDIR} == ${PORTSDIR}/databases/mysql50-client
WITH_CHARSET=cp1251
WITH_COLLATION=cp1251_bin
BUILD_OPTIMIZED=yes
.endif

I have mysql table with such structure

mysql> show create table phpbb_bt_torrents\G
*************************** 1. row ***************************
       Table: phpbb_bt_torrents
Create Table: CREATE TABLE `phpbb_bt_torrents` (
`info_hash` char(20) CHARACTER SET cp1251 COLLATE cp1251_bin NOT NULL DEFAULT '',
) ENGINE=InnoDB AUTO_INCREMENT=128511 DEFAULT CHARSET=cp1251

I'm backing it up at night with mysqldump --quote-names --add-drop-table db>db.sql

If I restore backup using mysql < db.sql on the same server then I'm getting not quite the same data, for example:

SELECT hex(info_hash) FROM phpbb_bt_torrents

8CB0701A0C8B7D976538**88**E8B959A6420AEC7679 becomes 8CB0701A0C8B7D976538**3F**E8B959A6420AEC7679 after restore

A9**98**6D2E961E1F407E74E9D18D811DDB702924F5 becomes A9**3F**6D2E961E1F407E74E9D18D811DDB702924F5 after restore

and so on... i've noticed only 88h and 98h characters corrupts, but there can be more.

Is there a way to backup-restore data without corruption?

Answer 1

Can you try dumping with "mysqldump --opt ..." and then restoring it?

It shouldn't make a difference when on the same server, just to make sure this cannot be some user-specific configuration.

Answer 2

I've opened the ticket on Bugs.MySQL.com

Workaround from there:

specify option --default-character-set=cp1251. But this workaround would not work with test case with mixed character sets