Rechercher dans le manuel MySQL

17.4.1.33 Replication and Transaction Inconsistencies

Inhaltsverzeichnis

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 requires slave_parallel_type=LOGICAL_CLOCK, and that binary logging (the log_bin system variable) and slave update logging (the --log-slave-updates) are also enabled. Note that slave_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 point N have been applied, and no transactions after N have been applied, but Exec_master_log_pos has a value smaller than N. 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. Enabling slave_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:

  1. While slave threads are running, there may be gaps and half-applied transactions.

  2. mysqld shuts down. Both clean and unclean shutdown abort ongoing transactions and may leave gaps and half-applied transactions.

  3. 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.

  4. 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.

  5. STOP SLAVE when using a multithreaded slave. After issuing STOP SLAVE, the slave waits for any gaps to be filled and then updates Exec_master_log_pos. This ensures it never leaves gaps or master log position lag, unless any of the cases above applies, in other words, before STOP SLAVE completes, either an error happens, or another thread issues KILL, or the server restarts. In these cases, STOP SLAVE returns successfully.

  6. 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.

  7. STOP SLAVE when using a single-threaded slave. If the ongoing transaction only updates transactional tables, it is rolled back and STOP 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:

  1. The slave database is in a state that may never have existed on the master.

  2. The field Exec_master_log_pos in SHOW 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.

  3. CHANGE MASTER TO statements for that channel fail with an error, unless the applier threads are running and the CHANGE MASTER TO statement only sets receiver options.

  4. If mysqld is started with --relay-log-recovery, no recovery is done for that channel, and a warning is printed.

  5. If mysqldump is used with --dump-slave, it does not record the existence of gaps; thus it prints CHANGE MASTER TO with RELAY_LOG_POS set to the “low-water mark” position in Exec_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.

Warning

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.


Suchen Sie im MySQL-Handbuch

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-features-transaction-inconsistencies.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

  1. Zeigen Sie - html-Dokument Sprache des Dokuments:en Manuel MySQL : https://dev.mysql.com/

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.

Inhaltsverzeichnis Haut