Documentation Home
MySQL 9.2 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 40.5Mb
PDF (A4) - 40.6Mb
Man Pages (TGZ) - 259.6Kb
Man Pages (Zip) - 366.8Kb
Info (Gzip) - 4.1Mb
Info (Zip) - 4.1Mb


MySQL 9.2 Reference Manual  /  ...  /  Monitoring Replication Main Threads

19.2.3.1 Monitoring Replication Main Threads

The SHOW PROCESSLIST statement provides information that tells you what is happening on the source and on the replica regarding replication. For information on source states, see Section 10.14.4, “Replication Source Thread States”. For replica states, see Section 10.14.5, “Replication I/O (Receiver) Thread States”, and Section 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 from SHOW PROCESSLIST.

On the source server, the output from SHOW PROCESSLIST looks like this:

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

Here, thread 2 is a Binlog Dump thread that services a connected replica. The State 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 no Binlog 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 from SHOW PROCESSLIST looks like this:

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

The State 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 that SHOW PROCESSLIST was run, both threads were idle, waiting for further updates.

The value in the Time column can show how late the replica is compared to the source. See Section A.14, “MySQL 9.2 FAQ: Replication”. If sufficient time elapses on the source side without activity on the Binlog 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 of net_write_timeout and net_retry_count; for more information about these, see Section 7.1.8, “Server System Variables”.

The SHOW REPLICA STATUS statement provides additional information about replication processing on a replica server. See Section 19.1.7.1, “Checking Replication Status”.

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

Press CTRL+C to copy
SELECT * FROM performance_schema.threads WHERE PROCESSLIST_COMMAND LIKE "Binlog Dump%"

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