6.3. Replication Implementation Details

MySQL 5.0

previous page next page

6.3. Replication Implementation Details

MySQL replication capabilities are implemented using three threads (one on the master server and two on the slave). When a 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 thread in the output of on the master. The slave I/O thread reads the updates that the master 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 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 .

On the master server, the output from looks like this: 

mysql> 
*************************** 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. If you see no threads on a master server, this means that replication is not running — that is, that no slaves are currently connected.

On the slave server, the output from looks like this: 

mysql> 
*************************** 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 was run, both threads were idle, waiting for further updates.

The value in the column can show how late the slave is compared to the master. See Section 6.10, “Replication FAQ”.

6.3.1. Replication Master Thread States

The following list shows the most common states you may see in the column for the master's thread. If you see no threads on a master server, this means that replication is not running — that is, that no slaves are currently connected.

  • Binary logs consist of events, where an event is usually an update plus some other information. The thread has read an event from the binary log and is now 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. The thread is now idle, waiting for new events to appear in the binary log resulting from new updates occurring on the master.

  • A very brief state that occurs as the thread is stopping.

6.3.2. Replication Slave I/O Thread States

The following list shows the most common states you see in the column for a slave server I/O thread. This state also appears in the column displayed by , so you can get a good view of what is happening by using that statement.

  • The thread is attempting to connect to the master.

  • A state that occurs very briefly, after the connection to the master is established.

  • A state that occurs very briefly after the connection to the master is established.

  • A state that occurs very briefly, 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 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 seconds, a timeout occurs. At that point, the thread considers the connection to be broken and makes 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 seconds before attempting to reconnect.

  • The thread is trying to reconnect to the master. When connection is established again, the state becomes .

  • You are using a non-zero value, and the relay logs have grown large enough 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 state that occurs briefly as the thread is stopping.

6.3.3. Replication Slave SQL Thread States

The following list shows the most common states you may see in the column for a slave server SQL thread:

  • The thread has read an event from the relay log so that the event can be processed.

  • The thread has processed all events in the relay log files, and is now 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 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.

6.3.4. Replication Relay and Status Files

By default, relay logs filenames have the form -relay-bin., where is the name of the slave server host and is a sequence number. Successive relay log files are created using successive sequence numbers, beginning with . The slave uses an index file to track the relay log files currently in use. The default relay log index filename is -relay-bin.index. By default, the slave server creates relay log files in its data directory. The default filenames can be overridden with the and server options. See Section 6.8, “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, rotates relay logs, which influences when the SQL thread deletes them.

A slave server creates a new relay log file under the following conditions:

  • Each time the I/O thread starts.

  • When the logs are flushed; for example, with or mysqladmin flush-logs.

  • When the size of the current relay log file becomes too large. The meaning of “too large” is determined as follows:

    • If the value of is greater than 0, that is the maximum relay log file size.

    • If the value of is 0, determines the maximum relay log file size.

A slave replication server creates two additional small files in the data directory. These status files are named and by default. Their names can be changed by using the and options. See Section 6.8, “Replication Startup Options”.

The two status files contain information like that shown in the output of the statement, which is discussed in Section 13.6.2, “SQL Statements for Controlling Slave Servers”. 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 file. The following table shows the correspondence between the lines in the file and the columns displayed by .

Line Description
1 Number of lines in the file
2
3
4
5
6 Password (not shown by )
7
8
9
10
11
12
13
14

The SQL thread updates the file. The following table shows the correspondence between the lines in the file and the columns displayed by .

Line Description
1
2
3
4

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 file, you can check it to determine how far the SQL thread has executed in the master binary logs. Then you can use with the and 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 statements, you should also back up any files that exist in the directory that the slave uses for this purpose. The slave needs these files to resume replication of any interrupted operations. The directory location is specified using the option. If this option is not specified, the directory location is the value of the system variable.

previous page start next page