Rechercher dans le manuel MySQL
17.4.1.33 Replication and Transaction Inconsistencies
Inconsistencies in the sequence of transactions that have been executed from the relay log can occur depending on your replication configuration. This section explains how to avoid inconsistencies and solve any problems they cause.
The following types of inconsistencies can exist:
Half-applied transactions. A transaction which updates non-transactional tables has applied some but not all of its changes.
Gaps. A gap is a transaction that has not been fully applied, even though some transaction later in the sequence has been applied. Gaps can only appear when using a multithreaded slave. To avoid gaps occurring, set
slave_preserve_commit_order=1
, which requiresslave_parallel_type=LOGICAL_CLOCK
, and that binary logging (thelog_bin
system variable) and slave update logging (the--log-slave-updates
) are also enabled. Note thatslave_preserve_commit_order=1
does not preserve the order of non-transactional DML updates, so these might commit before transactions that precede them in the relay log, which might result in gaps.Master log position lag. Even in the absence of gaps, it is possible that transactions after
Exec_master_log_pos
have been applied. That is, all transactions up to pointN
have been applied, and no transactions afterN
have been applied, butExec_master_log_pos
has a value smaller thanN
. In this situation,Exec_master_log_pos
is a “low-water mark” of the transactions applied, and lags behind the position of the most recently applied transaction. This can only happen on multithreaded slaves. Enablingslave_preserve_commit_order
does not prevent master log position lag.
The following scenarios are relevant to the existence of half-applied transactions, gaps, and master log position lag:
While slave threads are running, there may be gaps and half-applied transactions.
mysqld shuts down. Both clean and unclean shutdown abort ongoing transactions and may leave gaps and half-applied transactions.
KILL
of replication threads (the SQL thread when using a single-threaded slave, the coordinator thread when using a multithreaded slave). This aborts ongoing transactions and may leave gaps and half-applied transactions.Error in applier threads. This may leave gaps. If the error is in a mixed transaction, that transaction is half-applied. When using a multithreaded slave, workers which have not received an error complete their queues, so it may take time to stop all threads.
STOP SLAVE
when using a multithreaded slave. After issuingSTOP SLAVE
, the slave waits for any gaps to be filled and then updatesExec_master_log_pos
. This ensures it never leaves gaps or master log position lag, unless any of the cases above applies, in other words, beforeSTOP SLAVE
completes, either an error happens, or another thread issuesKILL
, or the server restarts. In these cases,STOP SLAVE
returns successfully.If the last transaction in the relay log is only half-received and the multithreaded slave coordinator has started to schedule the transaction to a worker, then
STOP SLAVE
waits up to 60 seconds for the transaction to be received. After this timeout, the coordinator gives up and aborts the transaction. If the transaction is mixed, it may be left half-completed.STOP SLAVE
when using a single-threaded slave. If the ongoing transaction only updates transactional tables, it is rolled back andSTOP SLAVE
stops immediately. If the ongoing transaction is mixed,STOP SLAVE
waits up to 60 seconds for the transaction to complete. After this timeout, it aborts the transaction, so it may be left half-completed.
The global variable
rpl_stop_slave_timeout
is
unrelated to the process of stopping the replication threads. It
only makes the client that issues STOP
SLAVE
return to the client, but the replication
threads continue to try to stop.
If a replication channel has gaps, it has the following consequences:
The slave database is in a state that may never have existed on the master.
The field
Exec_master_log_pos
inSHOW SLAVE STATUS
is only a “low-water mark”. In other words, transactions appearing before the position are guaranteed to have committed, but transactions after the position may have committed or not.CHANGE MASTER TO
statements for that channel fail with an error, unless the applier threads are running and theCHANGE MASTER TO
statement only sets receiver options.If mysqld is started with
--relay-log-recovery
, no recovery is done for that channel, and a warning is printed.If mysqldump is used with
--dump-slave
, it does not record the existence of gaps; thus it printsCHANGE MASTER TO
withRELAY_LOG_POS
set to the “low-water mark” position inExec_master_log_pos
.After applying the dump on another server, and starting the replication threads, transactions appearing after the position are replicated again. Note that this is harmless if GTIDs are enabled (however, in that case it is not recommended to use
--dump-slave
).
If a replication channel has master log position lag but no gaps, cases 2 to 5 above apply, but case 1 does not.
The master log position information is persisted in binary
format in the internal table
mysql.slave_worker_info
.
START SLAVE
[SQL_THREAD]
always consults this information so that
it applies only the correct transactions. This remains true even
if slave_parallel_workers
has
been changed to 0 before START
SLAVE
, and even if START
SLAVE
is used with UNTIL
clauses.
START SLAVE UNTIL
SQL_AFTER_MTS_GAPS
only applies as many transactions
as needed in order to fill in the gaps. If
START SLAVE
is used with
UNTIL
clauses that tell it to stop before it
has consumed all the gaps, then it leaves remaining gaps.
RESET SLAVE
removes the relay
logs and resets the replication position. Thus issuing
RESET SLAVE
on a slave with
gaps means the slave loses any information about the gaps,
without correcting the gaps.
Nederlandse vertaling
U hebt gevraagd om deze site in het Nederlands te bezoeken. Voor nu wordt alleen de interface vertaald, maar nog niet alle inhoud.Als je me wilt helpen met vertalingen, is je bijdrage welkom. Het enige dat u hoeft te doen, is u op de site registreren en mij een bericht sturen waarin u wordt gevraagd om u toe te voegen aan de groep vertalers, zodat u de gewenste pagina's kunt vertalen. Een link onderaan elke vertaalde pagina geeft aan dat u de vertaler bent en heeft een link naar uw profiel.
Bij voorbaat dank.
Document heeft de 26/06/2006 gemaakt, de laatste keer de 26/10/2018 gewijzigd
Bron van het afgedrukte document:https://www.gaudry.be/nl/mysql-rf-replication-features-transaction-inconsistencies.html
De infobrol is een persoonlijke site waarvan de inhoud uitsluitend mijn verantwoordelijkheid is. De tekst is beschikbaar onder CreativeCommons-licentie (BY-NC-SA). Meer info op de gebruiksvoorwaarden en de auteur.
Referenties
Deze verwijzingen en links verwijzen naar documenten die geraadpleegd zijn tijdens het schrijven van deze pagina, of die aanvullende informatie kunnen geven, maar de auteurs van deze bronnen kunnen niet verantwoordelijk worden gehouden voor de inhoud van deze pagina.
De auteur Deze site is als enige verantwoordelijk voor de manier waarop de verschillende concepten, en de vrijheden die met de referentiewerken worden genomen, hier worden gepresenteerd. Vergeet niet dat u meerdere broninformatie moet doorgeven om het risico op fouten te verkleinen.