MySQL replication capabilities are implemented using three threads (one on the master server and two on the slave). When START SLAVE is issued, the slave creates an I/O thread. The I/O thread connects to the master and asks it to send the statements recorded in its binary logs. The master creates a thread to send the binary log contents to the slave. This thread can be identified as the Binlog Dump thread in the output of SHOW PROCESSLIST on the master. The slave I/O thread reads what the master Binlog Dump thread sends and simply copies it to some local files in the slave's data directory called relay logs. The third thread is the SQL thread, which the slave creates to read the relay logs and execute the updates they contain.
In the preceding description, there are three threads per slave. For a master that has multiple slaves, it creates one thread for each currently connected slave, and each slave has its own I/O and SQL threads.
For versions of MySQL before 4.0.2, replication involves only two threads (one on the master and one on the slave). The slave I/O and SQL threads are combined as a single thread, and no relay log files are used.
The advantage of using two slave threads is that statement reading and execution are separated into two independent tasks. The task of reading statements is not slowed down if statement execution is slow. For example, if the slave server has not been running for a while, its I/O thread can quickly fetch all the binary log contents from the master when the slave starts, even if the SQL thread lags far behind and may take hours to catch up. If the slave stops before the SQL thread has executed all the fetched statements, the I/O thread has at least fetched everything so that a safe copy of the statements is locally stored in the slave's relay logs for execution when next the slave starts. This allows the binary logs to be purged on the master, because it no longer need wait for the slave to fetch their contents.
The SHOW PROCESSLIST statement provides information that tells you what is happening on the master and on the slave regarding replication.
The following example illustrates how the three threads show up in SHOW PROCESSLIST. The output format is that used by SHOW PROCESSLIST as of MySQL version 4.0.15, when the content of the State column was changed to be more meaningful compared to earlier versions.
On the master server, the output from SHOW PROCESSLIST looks like this:
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 replication thread for a connected slave. The information indicates that all outstanding updates have been sent to the slave and that the master is waiting for more updates to occur.
On the slave server, the output from SHOW PROCESSLIST looks like this:
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
This information indicates that thread 10 is the I/O thread that is communicating with the master server, and thread 11 is the SQL thread that is processing the updates stored in the relay logs. Currently, both threads are idle, waiting for further updates.
Note that the value in the Time column can tell how late the slave is compared to the master. See the section called “Replication FAQ”.
The following list shows the most common states you will see in the State column for the master's Binlog Dump thread. If you don't see any Binlog Dump threads on a master server, replication is not running. That is, no slaves currently are connected.
Binary logs consist of events, where an event is usually an update statement plus some other information. The thread has read an event from the binary log and is sending it to the slave.
The thread has finished reading a binary log file and is opening the next one to send to the slave.
The thread has read all outstanding updates from the binary logs and sent them to the slave. It is idle, waiting for new events to appear in the binary log resulting from new update statements being executed on the master.
A very brief state that occurs as the thread is stopping.
The following list shows the most common states you will see in the State column for a slave server I/O thread. Beginning with MySQL 4.1.1, this state also appears in the Slave_IO_State column displayed by the SHOW SLAVE STATUS statement. This means that you can get a good view of what is happening by using only SHOW SLAVE STATUS.
The thread is attempting to connect to the master.
A very brief state that occurs just after the connection to the master is established.
A very brief state that occurs just after the connection to the master is established.
A very brief state that occurs just after the connection to the master is established. The thread sends to the master a request for the contents of its binary logs, starting from the requested binary log filename and position.
If the binary log dump request failed (due to disconnection), the thread goes into this state while it sleeps, then tries to reconnect periodically. The interval between retries can be specified using the --master-connect-retry option.
The thread is trying to reconnect to the master.
The thread has connected to the master and is waiting for binary log events to arrive. This can last for a long time if the master is idle. If the wait lasts for slave_read_timeout seconds, a timeout will occur. At that point, the thread will consider the connection to be broken and make an attempt to reconnect.
The thread has read an event and is copying it to the relay log so that the SQL thread can process it.
An error occurred while reading (due to disconnection). The thread is sleeping for master-connect-retry seconds before attempting to reconnect.
The thread is trying to reconnect to the master. When connection is established again, the state will become Waiting for master to send event.
You are using a non-zero relay_log_space_limit value, and the relay logs have grown so much that their combined size exceeds this value. The I/O thread is waiting until the SQL thread frees enough space by processing relay log contents so that it can delete some relay log files.
A very brief state that occurs as the thread is stopping.
The following list shows the most common states you will see in the State column for a slave server SQL thread:
The thread has read an event from the relay log so that it can process it.
The thread has processed all events in the relay log files and is waiting for the I/O thread to write new events to the relay log.
A very brief state that occurs as the thread is stopping.
The State column for the I/O thread may also show the text of a statement. This indicates that the thread has read an event from the relay log, extracted the statement from it, and is executing it.
By default, relay logs are named using filenames of the form host_name-relay-bin.nnn, where host_name is the name of the slave server host and nnn is a sequence number. Successive relay log files are created using successive sequence numbers, beginning with 000001 (001 in MySQL 4.0 or older). The slave keeps track of relay logs currently in use in an index file. The default relay log index filename is host_name-relay-bin.index. By default, these files are created in the slave's data directory. The default filenames may be overridden with the --relay-log and --relay-log-index server options. See the section called “Replication Startup Options”.
Relay logs have the same format as binary logs, so you can use mysqlbinlog to read them. A relay log is automatically deleted by the SQL thread as soon as it has executed all its events and no longer needs it). There is no explicit mechanism for deleting relay logs, because the SQL thread takes care of doing so. However, from MySQL 4.0.14, FLUSH LOGS rotates relay logs, which will influence when the SQL thread deletes them.
A new relay log is created under the following conditions:
When the I/O thread starts for the first time after the slave server starts. (In MySQL 5.0, a new relay log is created each time the I/O thread starts, not just the first time.)
When the logs are flushed; for example, with FLUSH LOGS or mysqladmin flush-logs. (This creates a new relay log only as of MySQL 4.0.14.)
When the size of the current relay log file becomes too large. The meaning of “too large” is determined as follows:
max_relay_log_size, if max_relay_log_size > 0
max_binlog_size, if max_relay_log_size = 0 or MySQL is older than 4.0.14
A slave replication server creates two additional small files in the data directory. These are status files and are named master.info and relay-log.info by default. They contain information like that shown in the output of the SHOW SLAVE STATUS statement (see the section called “SQL Statements for Controlling Slave Servers” for a description of this statement). As disk files, they survive a slave server's shutdown. The next time the slave starts up, it reads these files to determine how far it has proceeded in reading binary logs from the master and in processing its own relay logs.
The master.info file is updated by the I/O thread. Before MySQL 4.1, the correspondence between the lines in the file and the columns displayed by SHOW SLAVE STATUS is as follows:
| Line | Description |
| 1 | Master_Log_File |
| 2 | Read_Master_Log_Pos |
| 3 | Master_Host |
| 4 | Master_User |
| 5 | Password (not shown by SHOW SLAVE STATUS) |
| 6 | Master_Port |
| 7 | Connect_Retry |
As of MySQL 4.1, the file includes a line count and information about SSL options:
| Line | Description |
| 1 | Number of lines in the file |
| 2 | Master_Log_File |
| 3 | Read_Master_Log_Pos |
| 4 | Master_Host |
| 5 | Master_User |
| 6 | Password (not shown by SHOW SLAVE STATUS) |
| 7 | Master_Port |
| 8 | Connect_Retry |
| 9 | Master_SSL_Allowed |
| 10 | Master_SSL_CA_File |
| 11 | Master_SSL_CA_Path |
| 12 | Master_SSL_Cert |
| 13 | Master_SSL_Cipher |
| 14 | Master_SSL_Key |
The relay-log.info file is updated by the SQL thread. The correspondence between the lines in the file and the columns displayed by SHOW SLAVE STATUS is as follows:
| Line | Description |
| 1 | Relay_Log_File |
| 2 | Relay_Log_Pos |
| 3 | Relay_Master_Log_File |
| 4 | Exec_Master_Log_Pos |
When you back up your slave's data, you should back up these two small files as well, along with the relay log files. They are needed to resume replication after you restore the slave's data. If you lose the relay logs but still have the relay-log.info file, you can check it to determine how far the SQL thread has executed in the master binary logs. Then you can use CHANGE MASTER TO with the MASTER_LOG_FILE and MASTER_LOG_POS options to tell the slave to re-read the binary logs from that point. This requires that the binary logs still exist on the master server.
If your slave is subject to replicating LOAD DATA INFILE statements, you should also back up any SQL_LOAD-* files that exist in the directory that the slave uses for this purpose. The slave needs these files to resume replication of any interrupted LOAD DATA INFILE operations. The directory location is specified using the --slave-load-tmpdir option. Its default value, if not specified, is the value of the tmpdir variable.