System variables are not replicated correctly when using
STATEMENT
mode, except for the following
variables when they are used with session scope:
When MIXED
mode is used, the variables in the
preceding list, when used with session scope, cause a switch
from statement-based to row-based logging. See
Section 5.2.4.3, “Mixed Binary Logging Format”.
sql_mode
is also replicated
except for the
NO_DIR_IN_CREATE
mode; the
slave always preserves its own value for
NO_DIR_IN_CREATE
, regardless
of changes to it on the master. This is true for all replication
formats.
However, when mysqlbinlog parses a
SET @@sql_mode =
statement, the full
mode
mode
value, including
NO_DIR_IN_CREATE
, is passed to
the receiving server. For this reason, replication of such a
statement may not be safe when STATEMENT
mode
is in use.
The storage_engine
system
variable is not replicated, regardless of the logging mode; this
is intended to facilitate replication between different storage
engines.
In statement-based replication, session variables are not
replicated properly when used in statements that update tables.
For example, SET max_join_size=1000
followed
by INSERT INTO mytable
VALUES(@@max_join_size)
will not insert the same data
on the master and the slave. This does not apply to the common
sequence of SET time_zone=...
followed by
INSERT INTO mytable
VALUES(CONVERT_TZ(...,...,@@time_zone))
.
Replication of session variables is not a problem when row-based replication is being used, in which case, session variables are always replicated safely. See Section 16.1.2, “Replication Formats”.
In MySQL 5.1.20 and later (and in MySQL 5.0.46 and later in MySQL 5.0, for backward compatibility), the following session variables are written to the binary log and honored by the replication slave when parsing the binary log, regardless of the logging format:
Even though session variables relating to character sets and collations are written to the binary log, replication between different character sets is not supported.
User Comments
Add your own comment.