5.4. Replication Implementation Details
MySQL replication capabilities are implemented using three threads (one on the master server and two on the slave). When a START SLAVE statement is issued on a slave server, the slave creates an I/O thread, which connects to the master and asks it to send the updates 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 the updates that the master Binlog Dump tHRead sends and copies them to local files, known as relay logs, in the slave's data directory. The third thread is the SQL thread, which the slave creates to read the relay logs and to execute the updates they contain.
In the preceding description, there are three threads per master/slave connection. A master that has multiple slaves creates one thread for each currently-connected slave, and each slave has its own I/O and SQL threads.
The slave uses two threads so that reading updates from the master and executing them can be separated into two independent tasks. Thus, 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. 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 stored locally in the slave's relay logs, ready for execution the next time that the slave starts. This enables the master server to purge its binary logs sooner because it no longer needs to 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 the output from SHOW PROCESSLIST.
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 Binlog Dump replication thread for a connected slave. The State information indicates that all outstanding updates have been sent to the slave and that the master is waiting for more updates to occur. If you see no Binlog Dump threads on a master server, this means that replication is not runningthat is, that no slaves are currently connected.
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. At the time that the SHOW PROCESSLIST was run, both threads were idle, waiting for further updates.
The value in the Time column can show how late the slave is compared to the master. See Section 5.11, "Replication FAQ."
5.4.1. Replication Master Thread States
The following list shows the most common states you may see in the State column for the master's Binlog Dump thread. If you see no Binlog Dump threads on a master server, this means that replication is not runningthat is, that no slaves are currently connected.
5.4.2. Replication Slave I/O Thread States
The following list shows the most common states you see in the State column for a slave server I/O thread. This state also appears in the Slave_IO_State column displayed by SHOW SLAVE STATUS, so by using that statement, you can get a good view of what is happening.
5.4.3. Replication Slave SQL Thread States
The following list shows the most common states you may see in the State column for a slave server SQL thread:
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.
5.4.4. Replication Relay and Status Files
By default, relay logs filenames have the form host_name-relay-bin. nnnnnn, where host_name is the name of the slave server host and nnnnnn is a sequence number. Successive relay log files are created using successive sequence numbers, beginning with 000001. The slave uses an index file to track the relay log files currently in use. The default relay log index filename is host_name-relay-bin.index. By default, the slave server creates relay log files in its data directory. The default filenames can be overridden with the --relay-log and --relay-log-index server options. See Section 5.9, "Replication Startup Options."
Relay logs have the same format as binary logs and can be read using mysqlbinlog. The SQL thread automatically deletes each relay log file as soon as it has executed all events in the file and no longer needs it. There is no explicit mechanism for deleting relay logs because the SQL thread takes care of doing so. However, FLUSH LOGS rotates relay logs, which influences when the SQL thread deletes them.
A slave replication server creates two additional small files in the data directory. These status files are named master.info and relay-log.info by default. Their names can be changed by using the --master-info-file and --relay-log-info-file options. See Section 5.9, "Replication Startup Options."
The two status files contain information like that shown in the output of the SHOW SLAVE STATUS statement. Because the status files are stored on disk, they survive a slave server's shutdown. The next time the slave starts up, it reads the two files to determine how far it has proceeded in reading binary logs from the master and in processing its own relay logs.
The I/O thread updates the master.info file. The following table shows the correspondence between the lines in the file and the columns displayed by SHOW SLAVE STATUS.
The SQL thread updates the relay-log.info file. The following table shows the correspondence between the lines in the file and the columns displayed by SHOW SLAVE STATUS.
When you back up the slave's data, you should back up these two status 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. Of course, 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. If this option is not specified, the directory location is the value of the tmpdir system variable.