(mysql.info) replication-implementation-details

Info Catalog

(mysql.info) replication-implementation

(mysql.info) replication

(mysql.info) replication-howto

 
 6.3 Replication Implementation Details
 ======================================
 

Menu

 
* master-thread-states         Replication Master Thread States
* slave-io-thread-states       Replication Slave I/O Thread States
* slave-sql-thread-states      Replication Slave SQL Thread States
* slave-logs                   Replication Relay and Status Files
 
 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 running -- that 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  replication-faq.
 

Info Catalog

(mysql.info) replication-implementation

(mysql.info) replication

(mysql.info) replication-howto