Rechercher dans le manuel MySQL
17.4.1.31 Replication and Temporary Tables
In MySQL 8.0, when
binlog_format
is set to
ROW
or MIXED
, statements
that exclusively use temporary tables are not logged on the
master, and therefore the temporary tables are not replicated.
Statements that involve a mix of temporary and nontemporary
tables are logged on the master only for the operations on
nontemporary tables, and the operations on temporary tables are
not logged. This means that there are never any temporary tables
on the slave to be lost in the event of an unplanned shutdown by
the slave. For more information about row-based replication and
temporary tables, see
Row-based logging of temporary tables.
When binlog_format
is set to
STATEMENT
, operations on temporary tables are
logged on the master and replicated on the slave, provided that
the statements involving temporary tables can be logged safely
using statement-based format. In this situation, loss of
replicated temporary tables on the slave can be an issue. In
statement-based replication mode,
CREATE TEMPORARY
TABLE
and
DROP TEMPORARY
TABLE
statements cannot be used inside a transaction,
procedure, function, or trigger when GTIDs are in use on the
server (that is, when the
enforce_gtid_consistency
system
variable is set to ON
). They can be used
outside these contexts when GTIDs are in use, provided that
autocommit=1
is set.
Because of the differences in behavior between row-based or
mixed replication mode and statement-based replication mode
regarding temporary tables, you cannot switch the replication
format at runtime, if the change applies to a context (global or
session) that contains any open temporary tables. For more
details, see the description of the
binlog_format
option.
Safe slave shutdown when using temporary tables. In statement-based replication mode, temporary tables are replicated except in the case where you stop the slave server (not just the slave threads) and you have replicated temporary tables that are open for use in updates that have not yet been executed on the slave. If you stop the slave server, the temporary tables needed by those updates are no longer available when the slave is restarted. To avoid this problem, do not shut down the slave while it has temporary tables open. Instead, use the following procedure:
Issue a
STOP SLAVE SQL_THREAD
statement.Use
SHOW STATUS
to check the value of theSlave_open_temp_tables
variable.If the value is not 0, restart the slave SQL thread with
START SLAVE SQL_THREAD
and repeat the procedure later.When the value is 0, issue a mysqladmin shutdown command to stop the slave.
Temporary tables and replication options.
By default, with statement-based replication, all temporary
tables are replicated; this happens whether or not there are
any matching --replicate-do-db
,
--replicate-do-table
, or
--replicate-wild-do-table
options in effect. However, the
--replicate-ignore-table
and
--replicate-wild-ignore-table
options are honored for temporary tables. The exception is
that to enable correct removal of temporary tables at the end
of a session, a replication slave always replicates a
DROP TEMPORARY TABLE IF EXISTS
statement,
regardless of any exclusion rules that would normally apply
for the specified table.
A recommended practice when using statement-based replication is
to designate a prefix for exclusive use in naming temporary
tables that you do not want replicated, then employ a
--replicate-wild-ignore-table
option to match that prefix. For example, you might give all
such tables names beginning with norep
(such
as norepmytable
,
norepyourtable
, and so on), then use
--replicate-wild-ignore-table=norep%
to prevent them from being replicated.
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-temptables.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.