89460

Replication and Character Sets

The following applies to replication between MySQL servers that use different character sets:

•    If the master uses MySQL 4.1, you must always use the same gbbal character set and collation on

the master and the slave, regardless of the slave MySQL version. (These are controlled by the --

character-set-server [431] and -collation-server [431] options.) Otherwise, you may

get duplicate-key errors on the slave, because a key that is unique in the master character set might

not be unique in the slave character set. Notę that this is not a cause for concern

when master and

slave are both MySQL 5.0 or later.

•    If the master is older than MySQL 4.1.3, the character set of any Client should never be madę

different from its global value because this character set change is not known to the slave. In other

words, clients should not use SET NAMES, SET CHARACTER SET, and soforth. If both the master

and the slave are 4.1.3 or newer, clients can freely set session values for character set variables

because these settings are written to the binary log and so are known to the slave. That is, clients

can use SET NAMES or SET CHARACTER SET or can set variables such as

collation_client or

collation_server [485], However, clients are prevented from changing the global value of these

variables; as stated previously, the master and slave must always have identical global character set

values. This is true whether you are using statement-based or row-based replication.

•    If the master has databases with a character set different from the global

character_set_server [484] value, you should design your CREATE TABLE statements so that

they do not implicitly rely on the database default character set. A good workaround is to State the

character set and collation explicitly in CREATE TABLE statements.