Rechercher dans le manuel MySQL
17.1.3.6 Restrictions on Replication with GTIDs
Because GTID-based replication is dependent on transactions, some features otherwise available in MySQL are not supported when using it. This section provides information about restrictions on and limitations of replication with GTIDs.
Updates involving nontransactional storage engines.
When using GTIDs, updates to tables using nontransactional
storage engines such as MyISAM
cannot be made in the same statement or transaction as updates
to tables using transactional storage engines such as
InnoDB
.
This restriction is due to the fact that updates to tables that use a nontransactional storage engine mixed with updates to tables that use a transactional storage engine within the same transaction can result in multiple GTIDs being assigned to the same transaction.
Such problems can also occur when the master and the slave use different storage engines for their respective versions of the same table, where one storage engine is transactional and the other is not. Also be aware that triggers that are defined to operate on nontransactional tables can be the cause of these problems.
In any of the cases just mentioned, the one-to-one correspondence between transactions and GTIDs is broken, with the result that GTID-based replication cannot function correctly.
CREATE TABLE ... SELECT statements.
CREATE
TABLE ... SELECT
statements are not allowed when using
GTID-based replication. When
binlog_format
is set to
STATEMENT, a CREATE TABLE ... SELECT
statement is recorded in the binary log as one transaction with
one GTID, but if ROW format is used, the statement is recorded
as two transactions with two GTIDs. If a master used STATEMENT
format and a slave used ROW format, the slave would be unable to
handle the transaction correctly, therefore the CREATE
TABLE ... SELECT
statement is disallowed with GTIDs to
prevent this scenario.
Temporary tables.
When binlog_format
is set to
STATEMENT
,
CREATE TEMPORARY
TABLE
and
DROP TEMPORARY
TABLE
statements cannot be used inside transactions,
procedures, functions, and triggers 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. From MySQL
8.0.13, when binlog_format
is
set to ROW
or MIXED
,
CREATE TEMPORARY
TABLE
and
DROP TEMPORARY
TABLE
statements are allowed inside a transaction,
procedure, function, or trigger when GTIDs are in use. The
statements are not written to the binary log and are therefore
not replicated to slaves. The use of row-based replication means
that the slaves remain in sync without the need to replicate
temporary tables. If the removal of these statements from a
transaction results in an empty transaction, the transaction is
not written to the binary log.
Preventing execution of unsupported statements.
To prevent execution of statements that would cause GTID-based
replication to fail, all servers must be started with the
--enforce-gtid-consistency
option
when enabling GTIDs. This causes statements of any of the types
discussed previously in this section to fail with an error.
Note that
--enforce-gtid-consistency
only
takes effect if binary logging takes place for a statement. If
binary logging is disabled on the server, or if statements are not
written to the binary log because they are removed by a filter,
GTID consistency is not checked or enforced for the statements
that are not logged.
For information about other required startup options when enabling GTIDs, see Section 17.1.3.4, “Setting Up Replication Using GTIDs”.
Skipping transactions.
sql_slave_skip_counter
is not
supported when using GTIDs. If you need to skip transactions,
use the value of the master's
gtid_executed
variable instead;
see Injecting empty transactions, for more
information.
Ignoring servers.
The IGNORE_SERVER_IDS option of the CHANGE
MASTER TO
statement is deprecated when using GTIDs,
because transactions that have already been applied are
automatically ignored. Before starting GTID-based replication,
check for and clear all ignored server ID lists that have
previously been set on the servers involved. The
SHOW SLAVE STATUS
statement,
which can be issued for individual channels, displays the list
of ignored server IDs if there is one. If there is no list, the
Replicate_Ignore_Server_Ids
field is blank.
GTID mode and mysqldump. It is possible to import a dump made using mysqldump into a MySQL server running with GTID mode enabled, provided that there are no GTIDs in the target server's binary log.
GTID mode and mysql_upgrade.
Prior to MySQL 8.0.16, when the server is running with global
transaction identifiers (GTIDs) enabled
(gtid_mode=ON
), do not enable
binary logging by mysql_upgrade (the
--write-binlog
option). As
of MySQL 8.0.16, the server performs the entire MySQL upgrade
procedure, but disables binary logging during the upgrade, so
there is no issue.
Deutsche Übersetzung
Sie haben gebeten, diese Seite auf Deutsch zu besuchen. Momentan ist nur die Oberfläche übersetzt, aber noch nicht der gesamte Inhalt.Wenn Sie mir bei Übersetzungen helfen wollen, ist Ihr Beitrag willkommen. Alles, was Sie tun müssen, ist, sich auf der Website zu registrieren und mir eine Nachricht zu schicken, in der Sie gebeten werden, Sie der Gruppe der Übersetzer hinzuzufügen, die Ihnen die Möglichkeit gibt, die gewünschten Seiten zu übersetzen. Ein Link am Ende jeder übersetzten Seite zeigt an, dass Sie der Übersetzer sind und einen Link zu Ihrem Profil haben.
Vielen Dank im Voraus.
Dokument erstellt 26/06/2006, zuletzt geändert 26/10/2018
Quelle des gedruckten Dokuments:https://www.gaudry.be/de/mysql-rf-replication-gtids-restrictions.html
Die Infobro ist eine persönliche Seite, deren Inhalt in meiner alleinigen Verantwortung liegt. Der Text ist unter der CreativeCommons-Lizenz (BY-NC-SA) verfügbar. Weitere Informationen auf die Nutzungsbedingungen und dem Autor.
Referenzen
Diese Verweise und Links verweisen auf Dokumente, die während des Schreibens dieser Seite konsultiert wurden, oder die zusätzliche Informationen liefern können, aber die Autoren dieser Quellen können nicht für den Inhalt dieser Seite verantwortlich gemacht werden.
Der Autor Diese Website ist allein dafür verantwortlich, wie die verschiedenen Konzepte und Freiheiten, die mit den Nachschlagewerken gemacht werden, hier dargestellt werden. Denken Sie daran, dass Sie mehrere Quellinformationen austauschen müssen, um das Risiko von Fehlern zu reduzieren.