System variables are not replicated correctly when using
STATEMENT mode, except for the following
variables when they are used with session scope:
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
Mixed Binary Logging Format.
sql_mode is also replicated
except for the
NO_DIR_IN_CREATE mode; the
replica always preserves its own value for
of changes to it on the source. This is true for all replication
However, when mysqlbinlog parses a
SET @@sql_mode =
statement, the full
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
is in use.
read_only system variable
is not replicated. In addition, the enabling this variable has
different effects with regard to temporary tables, table
locking, and the
statement in different MySQL versions.
variable is not replicated. Increasing the value of this
variable on the source without doing so on the replica can lead
eventually to Table is full errors on the
replica when trying to execute
INSERT statements on a
MEMORY table on the source that is
thus permitted to grow larger than its counterpart on the
replica. For more information, see
Section 4.1.20, “Replication and MEMORY Tables”.
In statement-based replication, session variables are not replicated properly when used in statements that update tables. For example, the following sequence of statements do not insert the same data on the source and the replica:
SET max_join_size=1000; INSERT INTO mytable VALUES(@@max_join_size);
This does not apply to the common sequence:
SET time_zone=...; 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 2.2, “Replication Formats”.
In MySQL 5.6, the following session variables are written to the binary log and honored by the replica 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.
To help reduce possible confusion, we recommend that you always
use the same setting for the
variable on both source and replica, especially when you are
running MySQL on platforms with case-sensitive file systems.
In previous versions of MySQL, when a case-sensitive file system was in use, setting this variable to 1 on the replica and to a different value on the source could lead to replication failure. This issue is fixed in MySQL 5.6.1. (Bug #37656)