Rechercher dans le manuel MySQL
17.4.1.38 Replication and Variables
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.4.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 default_storage_engine
system variable is not replicated, regardless of the logging
mode; this is intended to facilitate replication between
different storage engines.
The 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 SET PASSWORD
statement in different MySQL versions.
The max_heap_table_size
system
variable is not replicated. Increasing the value of this
variable on the master without doing so on the slave can lead
eventually to Table is full errors on the
slave when trying to execute
INSERT
statements on a
MEMORY
table on the master that is
thus permitted to grow larger than its counterpart on the slave.
For more information, see
Section 17.4.1.21, “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 will not insert the same data on the master and the slave:
This does not apply to the common sequence:
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 17.2.1, “Replication Formats”.
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.
To help reduce possible confusion, we recommend that you always
use the same setting for the
lower_case_table_names
system
variable on both master and slave, especially when you are
running MySQL on platforms with case-sensitive file systems. The
lower_case_table_names
setting
can only be configured when initializing the server.
Traduction non disponible
Le manuel MySQL n'est pas encore traduit en français sur l'infobrol. Seule la version anglaise est disponible pour l'instant.
Document créé le 26/06/2006, dernière modification le 26/10/2018
Source du document imprimé : https://www.gaudry.be/mysql-rf-replication-features-variables.html
L'infobrol est un site personnel dont le contenu n'engage que moi. Le texte est mis à disposition sous licence CreativeCommons(BY-NC-SA). Plus d'info sur les conditions d'utilisation et sur l'auteur.
Références
Ces références et liens indiquent des documents consultés lors de la rédaction de cette page, ou qui peuvent apporter un complément d'information, mais les auteurs de ces sources ne peuvent être tenus responsables du contenu de cette page.
L'auteur de ce site est seul responsable de la manière dont sont présentés ici les différents concepts, et des libertés qui sont prises avec les ouvrages de référence. N'oubliez pas que vous devez croiser les informations de sources multiples afin de diminuer les risques d'erreurs.