(mysql.info.gz) Replication Implementation Details

Info Catalog

(mysql.info.gz) Replication Implementation

(mysql.info.gz) Replication

(mysql.info.gz) Replication HOWTO

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

Menu

 
* Master thread states        Replication Master Thread States
* Slave I/O 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 `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.   Replication FAQ.
 

Info Catalog

(mysql.info.gz) Replication Implementation

(mysql.info.gz) Replication

(mysql.info.gz) Replication HOWTO