Movatterモバイル変換

TheSHOW PROCESSLIST statement provides information that tells you what is happening on the source and on the replica regarding replication. For information on source states, seeSection 10.14.4, “Replication Source Thread States”. For replica states, seeSection 10.14.5, “Replication I/O (Receiver) Thread States”, andSection 10.14.6, “Replication SQL Thread States”.

The following example illustrates how the three main replication threads, the binary log dump thread, replication I/O (receiver) thread, and replication SQL (applier) thread, show up in the output fromSHOW PROCESSLIST.

On the source server, the output fromSHOW PROCESSLIST looks like this:

mysql> SHOW PROCESSLIST\G*************************** 1. row ***************************     Id: 2   User: root   Host: localhost:32931     db: NULLCommand: Binlog Dump   Time: 94  State: Has sent all binlog to slave; waiting for binlog to         be updated   Info: NULL

Here, thread 2 is aBinlog Dump thread that services a connected replica. TheState information indicates that all outstanding updates have been sent to the replica and that the source is waiting for more updates to occur. If you see noBinlog Dump threads on a source server, this means that replication is not running; that is, no replicas are currently connected.

On a replica server, the output fromSHOW PROCESSLIST looks like this:

mysql> SHOW PROCESSLIST\G*************************** 1. row ***************************     Id: 10   User: system user   Host:     db: NULLCommand: Connect   Time: 11  State: Waiting for master to send event   Info: NULL*************************** 2. row ***************************     Id: 11   User: system user   Host:     db: NULLCommand: Connect   Time: 11  State: Has read all relay log; waiting for the slave I/O         thread to update it   Info: NULL

TheState information indicates that thread 10 is the replication I/O (receiver) thread that is communicating with the source server, and thread 11 is the replication SQL (applier) thread that is processing the updates stored in the relay logs. At the time thatSHOW PROCESSLIST was run, both threads were idle, waiting for further updates.

The value in theTime column can show how late the replica is compared to the source. SeeSection A.14, “MySQL 9.4 FAQ: Replication”. If sufficient time elapses on the source side without activity on theBinlog Dump thread, the source determines that the replica is no longer connected. As for any other client connection, the timeouts for this depend on the values ofnet_write_timeout andnet_retry_count; for more information about these, seeSection 7.1.8, “Server System Variables”.

TheSHOW REPLICA STATUS statement provides additional information about replication processing on a replica server. SeeSection 19.1.7.1, “Checking Replication Status”.

You can also retrieve information on the source'sBinlog Dump threads with the following:

        SELECT * FROM performance_schema.threads WHERE PROCESSLIST_COMMAND LIKE "Binlog Dump%"

Binlog Dump% is used to retrieve eitherBinlog Dump orBinlog Dump GTID, depending on which mode binlog dumping is in.