JIYIK CN >

Previously we explored the master server in master-slave replication. Now let's look at how the slave server works in the entire system.

In the article Master Server Exploration, we mentioned that three threads are needed in a master-slave replication process: Binlog dump thread, Slave I/O thread, and Slave SQL thread. The Binlog dump thread is on the master server, and the remaining two threads work on the slave server.

The workflow of these two threads on the slave server is shown in the following figure
 

For these two threads, as the slave is turned on from the server,

mysql> START SLVAE;

Then use

MySQL> SHOW SLAVE STATUS\G

Check these two threads

……
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 1264
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 878
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
……

In the above results, Slave_IO_Running:Yes and Slave_SQL_Running:Yes indicate that these two threads are running.

Then we use the command on the slave server

mysql> SHOW PROCESSLIAT\G

The following results are displayed (recorded as result one)

*************************** 1. row ***************************
     Id: 22
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 4
  State: Waiting for master to send event
   Info: NULL
*************************** 2. row ***************************
     Id: 23
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 4
  State: Slave has read all relay log; waiting for the slave I/O thread to update it
   Info: NULL

From the State information, we can see that Id 22 is the I/O thread, which is waiting for the master server to send updated content; Id 23 is the Slave SQL thread, which has read all updated content in the relay log file and is waiting for the I/O thread to update the file.

Use the command to stop the slave mechanism

mysql> STOP SLVAE;

Then we check again and find the results are as follows

……
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 1264
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 878
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: No
Slave_SQL_Running: No
……

This means that the two threads have stopped running. If you use the SHOW PROCESSLIST\G command again, no results will be displayed.

Slave I/O Thread

The slave I/O thread connects to the master server's Binlog dump thread and asks it to send the updated operations recorded in the binlog log. It then copies the data sent by the Binlog dump thread to the file relay log on the slave server (that is, locally).

Of course, to check whether this thread is running, in addition to the methods described above, you can also use

mysql> SHOW SLAVE LIKE 'Slave_running';

At this time, if the following result appears, it means that the thread is running

+------------------+-------------------+
| Variable_name | Value |
+------------------+-------------------+
| Slave_running | ON |
+------------------+-------------------+

在上述结果一中我们可以看到1.row即是Slave I/O线程的信息，其State: Waiting for master to send event 表示正在等待主服务器发送内容。当然State不止这一个值，它还有其它的值，下面列出了State的所有的值

1. Waiting for master update
在连接到主服务器之前的初始状态

2. Connecting to master
该线程正在连接主服务器，当然如果我们的网络环境优异的话，此状态我们几乎是看不到的

3. Checking master version
这个状态发生的时间也非常短暂，该状态在该线程和主服务器建立连接之后发生。

4. Registering slave on master
在主服务器上面注册从服务器，每当有新的从服务器连接进来以后都要在主服务器上面进行注册

5. Requesting binlog dump
向主服务器请求binlog日志的拷贝

6. Waiting to reconnect after a failed binlog dump request
如果5中失败，则该线程进入睡眠状态，此时State就是这个值，等待着去定期重新连接主服务器，那这个周期的大小可以通过CHANGE MASTER TO 来指定

7. Reconnecting after a failed binlog dump request
去重新连接主服务器

8. Waiting for master to send event
此值就是我们上述结果所显示的，正常情况下我们查看的时候一般都是这个值。其具体表示是这个线程已经和主服务器建立了连接，正在等待主服务器上的binlog 有更新，如果主服务器的Binlog dump线程一直是空闲的状态的话，那此线程会等待很长一段时间。当然也不是一直等待下去，如果时间达到了slave_net_timeout规定的时间，会发生等待超时的情况，在这种情况下I/O线程会重新去连接主服务器

9. Queueing master event to the relay log
该线程已经读取了Binlog dump线程发送的一个更新事件并且正在将其拷贝到relay log文件中

10. Waiting to reconnect after a failed master event read
当该线程读取Binlog dump 线程发送的更新事件失败时，该线程进入睡眠状态等待去重新连接主服务器，这个等待的时间默认是60秒，当然这个值也可以通过CHANGE MASTER TO来设置

11. Reconnecting after a failed master event read
该线程去重新连接主服务器，当连接成功以后，那这个State的值会改变为 Waiting for master to send event

12. Waiting for the Slave SQL thread to free enough relay log space
relay log space的大小是通过relay_log_space_limit来设定的，随着relay logs变得越来越大所有的大小合起来会超过这个设定值。这时该线程会等待SQL线程释放足够的空间删除一些relay log文件

13. Waiting for slave mutex on exit
当线程停止的时候会短暂的出现该情况

以上就是State可能会出现的值，以及都是在什么情况下出现。

Slave SQL线程

Slave SQL线程是在从服务器上面创建的，主要负责读取由Slave I/O写的relay log文件并执行其中的事件

在上述结果一中2.row即是Slave SQL线程的信息，同样有一个State表示该线程的当前状态。

下面也列出了State所有可能出现的情况

1. Waiting for the next event in relay log
该状态是读取relay log之前的初始状态
2. Reading event from the relay log
该状态表示此线程已经在relay log中读取了一个事件准备执行
3. Making temp file
该状态表示此线程正在执行LOAD_DATA_INFILE并且正在创建一个临时文件来保存从服务器将要读取的数据
4. Slave has read all relay log; waiting for the slave I/O thread to update it
该线程已经处理完了relay log中的所有事件，现在正在等待slave I/O线程更新relay log文件
5. Waiting for slave mutex on exit
当线程停止的时候会短暂的出现该情况

上面是对从服务器上的两个线程的简单的介绍，在运行过程中我们会发现这两个线程都离不开的文件就是relay log文件，下面我们简单介绍一下relay log文件。

relay log文件

relay log 和 主服务器上的bin log很相似，都是一系列的文件，这些文件包括那些包含描述数据库改变的操作事件的文件和索引文件，这个索引文件是relay logs文件的名称集合。

relay log 文件和 bin log文件一样，也是二进制文件，不能直接查看，需要使用mysql自带工具mysqlbinlog查看。

] # mysqlbinlog mysql安装路径/data/relay-log文件

当然其索引文件的内容我们是可以直接使用 vim查看的。

The default naming convention for relay logs files is host_name-relay-bin.nnnnnn. In my case, the default file name is localhost-relay-bin.000001. The default naming convention for index files is host_name-relay-bin.index. In my case, the default file name is localhost-relay-bin.index. These two names can be changed using --relay-log and --relay-log-index. The usage is as follows:

# mysqld_safe –user=mysql –relay-log=filename –relay-log-index=new index filename &

If you change these two names here, you may get errors such as "cannot open relay log" and "cannot find target log during relay log initialization". This is a bug in MySQL design and there is no good solution. If we don't want to use the default file name, the only way is to use the above two options to specify the file name when you start initializing the slave server, expecting that the slave server's host name may change in the future. This way, the file name will no longer depend on the server's host name.

These relay log files are not constantly increasing. When the Slave SQL thread has executed all the events in a relay log file and no longer needs it, it will delete the relay log file. Since the Slave SQL thread does these things, there is no clear rule to specify how to delete the relay log file.

All the above contents roughly describe the main workflow of the slave server in the master-slave replication system.

Reference content MySQL official documentation