In depth understanding of MySQL master slave replication thread state transition

Preface

The basic principle of master-slave replication of MySQL is that the slave library is connected to the master library, and the master library generates a master library DUMP thread. The main tasks of this DUMP thread are
1 mining the binlog log directly and sending it to the IO thread from the library. After the IO thread receives the log stream, it writes to relay log, another line
The SQL thread reads the relay log contents and then plays back the sql statement.

This article mainly introduces the related contents about MySQL master-slave replication thread state transition, and the following words are not much to say, let's take a look at the detailed introduction

1. Main library thread state (State) value

The following list shows the most common states that can be seen in the State column of the Binlog Dump thread of the master server in master-slave replication (SHOW PROCESSLIST). If the Binlog Dump thread is not visible on the primary server, this means that replication is not running, that is, no Slave host is currently connected.

Sending binlog event to slave

The binary log consists of various events, and one event is usually one update plus one other information. The thread has read 1 event from the binary log and is sending it to the slave server.

Finished reading one binlog; switching to next binlog

The thread has finished reading the binary log file and is opening the next log file to send to the slave server.

Has sent all binlog to slave; waiting for binlog to be updated

The thread has read all major updates from the binary log and sent them to the slave server. The thread is now idle, waiting for new events to appear in the binary log due to new updates on the primary server.

Waiting to finalize termination

A simple state that occurs when a thread stops.

2. Thread state (State) values from the library I/O

Connecting to master

The thread is trying to connect to the master server.

Checking master version

The state that occurs immediately after the connection with the master server is established.

Registering slave on master

The state that occurs immediately after the connection with the master server is established.

Requesting binlog dump

The state that occurs immediately after the connection with the master server is established. The thread sends a request to the master server for the contents of the binary log starting with the requested binary log file name and location.

Waiting to reconnect after a failed binlog dump request

If the binary log dump request fails (because there is no connection), the thread goes to sleep and then periodically attempts to reconnect. You can specify the interval between retries using the master-connect-retry option.

Reconnecting after a failed binlog dump request

The thread is trying to reconnect to the primary server.

Waiting for master to send event

The thread has connected to the primary server and is waiting for the binary log event to arrive. If the primary server is idle, it will last for a long time. If the wait lasts for slave_read_timeout seconds, a timeout occurs. At this point, the thread considers the connection broken and attempts to reconnect.

Queueing master event to the relay log

The thread has read 1 event and is copying it to the relay log for processing by the SQL thread.

Waiting to reconnect after a failed master event read

An error occurred while reading (because there is no connection), and the thread will sleep master-connect-retry seconds before attempting to reconnect.

Reconnecting after a failed master event read

The thread is attempting to reconnect to the primary server, and when the connection is reestablished, the status changes to Waiting for master to send event.

Waiting for the slave SQL thread to free enough relay log space

One non-zero relay_log_space_limit value is being used and the relay log has grown to its combined size beyond this value. The I/O thread is waiting until the SQL thread processes the relay log contents and deletes part of the relay log file to free up enough space.

Waiting for slave mutex on exit

A simple state that occurs when a thread stops.

3. Slave library SQL thread state (State) value

Reading event from the relay log

The thread has read 1 event from the relay log and can handle the event.

Has read all relay log; waiting for the slave I/O thread to update it

The thread has processed all events in the relay log file and is now waiting for the I/O thread to write new events to the relay log.

Waiting for slave mutex on exit

A simple state that occurs when a thread stops.

4. Connecting thread state (State) values from libraries

These thread states occur on replicated slave libraries, but are associated with connection threads and not with I/O or SQL threads.

Changing master

The thread is processing an CHANGE MASTER TO statement.

Killing slave

The thread is processing an STOP SLAVE statement.

Opening master dump table

This state occurs after Creating table from master dump.

Reading master dump table data

This state occurs after Opening master dump table.

Rebuilding the index on master dump table

This state occurs after Reading master dump table data.

Summarize