In-depth understanding of MySQL master-slave replication thread state transition

Preface

The basic principle of MySQL master-slave replication is that the slave database connects to the master database, and the master database generates a master database DUMP thread. The main task of the DUMP thread is to keep mining binlog logs and then send them to the slave database's IO thread. After receiving the log stream, the IO thread writes it to the relay log. Another thread, the SQL thread, reads the relay log content and then replays the SQL statement.

This article mainly introduces the relevant content about the state transition of MySQL master-slave replication thread. Let's take a look at the detailed introduction.

1. Main library thread state value

The following list shows the most common statuses you might see in the State column of the Binlog Dump thread of a master server in master-slave replication ( SHOW PROCESSLIST ). If the Binlog Dump thread is not seen on the master server, this means that replication is not running, that is, no slave hosts are currently connected.

Sending binlog event to slave

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

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 has sent them to the slave server. The thread is now idle, waiting for new events to appear in the binary log resulting from new updates on the master.

Waiting to finalize termination

A very simple state that occurs when a thread is stopped.

2. Slave I/O Thread State Value

Connecting to master

The thread is trying to connect to the primary server.

Checking master version

A temporary state that occurs immediately after a connection with the primary server is established.

Registering slave on master

A temporary state that occurs immediately after a connection with the primary server is established.

Requesting binlog dump

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

Waiting to reconnect after a failed binlog dump request

If the binary log dump request fails (due to no connection), the thread goes to sleep and then periodically tries to reconnect. The interval between retries can be specified 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 binary log events to arrive. If the primary server is idle, it may last longer. If the wait lasts for slave_read_timeout seconds, a timeout occurs. At this point, the thread considers the connection to be broken and attempts to reconnect.

Queueing master event to the relay log

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

Waiting to reconnect after a failed master event read

If an error occurs while reading (due to no connection), the thread will sleep for master-connect-retry seconds before attempting to reconnect.

Reconnecting after a failed master event read

The thread is trying to reconnect to the master server. When the connection is reestablished, the state changes to Waiting for master to send event.

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

A non-zero relay_log_space_limit value is being used, and the relay logs have grown so that their combined size exceeds that value. The I/O thread is waiting until the SQL thread processes the relay log contents and deletes some relay log files to free up enough space.

Waiting for slave mutex on exit

A very simple state that occurs when a thread is stopped.

3. Slave SQL thread state value

Reading event from the relay log

The thread has read an event from the relay log and can process 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 very simple state that occurs when a thread is stopped.

4. State value of slave connection thread

These thread states occur on replication slaves but are associated with the connection thread, not the I/O or SQL thread.

Changing master

The thread is processing a CHANGE MASTER TO statement.

Killing slave

The thread is processing a 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

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support for 123WORDPRESS.COM.