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
global character set and collation on
the master and the slave, regardless of the slave MySQL
version. (These are controlled by the
--character-set-server
and
--collation-server
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. Note 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 made 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
so forth. 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
. 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 you have databases on the master with character sets that
differ from the global
character_set_server
value,
you should design your CREATE
TABLE
statements so that tables in those databases
do not implicitly rely on the database default character
set. A good workaround is to state the table character set
and collation explicitly in CREATE
TABLE
statements.
User Comments
Add your own comment.