Rechercher dans le manuel MySQL
17.2.2 Replication Implementation Details
MySQL replication capabilities are implemented using three threads, one on the master server and two on the slave:
Binlog dump thread. The master creates a thread to send the binary log contents to a slave when the slave connects. This thread can be identified in the output of
SHOW PROCESSLIST
on the master as theBinlog Dump
thread.The binary log dump thread acquires a lock on the master's binary log for reading each event that is to be sent to the slave. As soon as the event has been read, the lock is released, even before the event is sent to the slave.
Slave I/O thread. When a
START SLAVE
statement is issued on a slave server, the slave creates an I/O thread, which connects to the master and asks it to send the updates recorded in its binary logs.The slave I/O thread reads the updates that the master's
Binlog Dump
thread sends (see previous item) and copies them to local files that comprise the slave's relay log.The state of this thread is shown as
Slave_IO_running
in the output ofSHOW SLAVE STATUS
or asSlave_running
in the output ofSHOW STATUS
.Slave SQL thread. The slave creates an SQL thread to read the relay log that is written by the slave I/O thread and execute the events contained therein.
In the preceding description, there are three threads per master/slave connection. A master that has multiple slaves creates one binary log dump thread for each currently connected slave, and each slave has its own I/O and SQL threads.
A slave uses two threads to separate reading updates from the master and executing them into independent tasks. Thus, the task of reading statements is not slowed down if statement execution is slow. For example, if the slave server has not been running for a while, its I/O thread can quickly fetch all the binary log contents from the master when the slave starts, even if the SQL thread lags far behind. If the slave stops before the SQL thread has executed all the fetched statements, the I/O thread has at least fetched everything so that a safe copy of the statements is stored locally in the slave's relay logs, ready for execution the next time that the slave starts.
The SHOW PROCESSLIST
statement
provides information that tells you what is happening on the
master and on the slave regarding replication. For information on
master states, see Section 8.14.3, “Replication Master Thread States”. For
slave states, see Section 8.14.4, “Replication Slave I/O Thread States”, and
Section 8.14.5, “Replication Slave SQL Thread States”.
The following example illustrates how the three threads show up in
the output from SHOW PROCESSLIST
.
On the master server, the output from SHOW
PROCESSLIST
looks like this:
Here, thread 2 is a Binlog Dump
replication
thread that services a connected slave. The
State
information indicates that all
outstanding updates have been sent to the slave and that the
master is waiting for more updates to occur. If you see no
Binlog Dump
threads on a master server, this
means that replication is not running; that is, no slaves are
currently connected.
On a slave server, the output from SHOW
PROCESSLIST
looks like this:
- mysql> SHOW PROCESSLIST\G
- *************************** 1. row ***************************
- Id: 10
- Host:
- db: NULL
- Command: Connect
- Time: 11
- State: Waiting for master to send event
- Info: NULL
- *************************** 2. row ***************************
- Id: 11
- Host:
- db: NULL
- Command: Connect
- Time: 11
- Info: NULL
The State
information indicates that thread 10
is the I/O thread that is communicating with the master server,
and thread 11 is the SQL thread that is processing the updates
stored in the relay logs. At the time that
SHOW PROCESSLIST
was run, both
threads were idle, waiting for further updates.
The value in the Time
column can show how late
the slave is compared to the master. See
Section A.13, “MySQL 8.0 FAQ: Replication”. If sufficient time elapses on
the master side without activity on the Binlog
Dump
thread, the master determines that the slave is no
longer connected. As for any other client connection, the timeouts
for this depend on the values of
net_write_timeout
and
net_retry_count
; for more information about
these, see Section 5.1.8, “Server System Variables”.
The SHOW SLAVE STATUS
statement
provides additional information about replication processing on a
slave server. See
Section 17.1.7.1, “Checking Replication Status”.
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-implementation-details.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.