一文揭秘MySQL复制之Seconds_Behind_Master的计算

# 位于rpl_mi.h中对定义clock_diff_with_master附近# 从源码注释上来看，复制延迟的计算公式为 clock_of_slave - last_timestamp_executed_by_SQL_thread - clock_diff_with_master# 该公式的含义为：从库的当前系统（主机）时间 - 从库 SQL 线程正在执行的event的时间戳 - 主从库的系统（主机）之间的时间差  /*     The difference in seconds between the clock of the master and the clock of     the slave (second - first). It must be signed as it may be <0 or >0.     clock_diff_with_master is computed when the I/O thread starts; for this the     I/O thread does a SELECT UNIX_TIMESTAMP() on the master.     "how late the slave is compared to the master" is computed like this:     clock_of_slave - last_timestamp_executed_by_SQL_thread - clock_diff_with_master  */# clock_diff_with_master 值为主从服务器的主机时间差，该值只在I/O线程启动时计算一次，后续每次计算Seconds_Behind_Master字段值时，是直接复用这个计算结果，每次重启I/O线程时该值会重新计算  long clock_diff_with_master;  
# master_row[0] 为从库在主库上执行SELECT UNIX_TIMESTAMP()的操作  mi->clock_diff_with_master=      (long) (time((time_t*) 0) - strtoul(master_row[0], 0, 10));
# 从rpl_slave.cc 文件中启动 I/O 线程时可以看出：      start_slave_thread-> # 启动start slave            handle_slave_io-> # 启动start io thread                get_master_version_and_clock # 获取当前slave和主机之间的时间差(clock_diff_with_master)

/*   The pseudo code to compute Seconds_Behind_Master:  # 阐明这是一段注释关于如何计算Seconds_Behind_Master的伪代码   if (SQL thread is running)  # 如果SQL线程正在运行，则进入这个if判断内，假设这里标记为if one   {     if (SQL thread processed all the available relay log)  # 如果SQL线程应用完成了所有可用的relay log，则进入这个if判断内，假设这里标记为if two     {       if (IO thread is running)  # 如果I/O线程正在运行，则进入这个if判断内，假设这里标记为if three          print 0;  # 如果if one/two/three三个条件都为真，则延迟值判定为0       else          print NULL;  # 如果if one/two为真，if three为假，则延迟值判定为NULL     }      else        compute Seconds_Behind_Master;  # 如果if one为真，if two为假，则执行公式计算延迟值    }    else     print NULL;  # 如果if one为假，则延迟值判定为NULL*/
if (mi->rli->slave_running){  /*     Check if SQL thread is at the end of relay log     Checking should be done using two conditions     condition1: compare the log positions and     condition2: compare the file names (to handle rotation case)  */  if ((mi->get_master_log_pos() == mi->rli->get_group_master_log_pos()) &&      (!strcmp(mi->get_master_log_name(), mi->rli->get_group_master_log_name())))  {    if (mi->slave_running == MYSQL_SLAVE_RUN_CONNECT)      protocol->store(0LL);    else      protocol->store_null();  }  else  {    long time_diff= ((long)(time(0) - mi->rli->last_master_timestamp)                     - mi->clock_diff_with_master);    /*      Apparently on some systems time_diff can be <0. Here are possible      reasons related to MySQL:      - the master is itself a slave of another master whose time is ahead.      - somebody used an explicit SET TIMESTAMP on the master.      Possible reason related to granularity-to-second of time functions      (nothing to do with MySQL), which can explain a value of -1:      assume the master's and slave's time are perfectly synchronized, and      that at slave's connection time, when the master's timestamp is read,      it is at the very end of second 1, and (a very short time later) when      the slave's timestamp is read it is at the very beginning of second      2. Then the recorded value for master is 1 and the recorded value for      slave is 2. At SHOW SLAVE STATUS time, assume that the difference      between timestamp of slave and rli->last_master_timestamp is 0      (i.e. they are in the same second), then we get 0-(2-1)=-1 as a result.      This confuses users, so we don't go below 0: hence the max(). 
      last_master_timestamp == 0 (an "impossible" timestamp 1970) is a      special marker to say "consider we have caught up".    */    protocol->store((longlong)(mi->rli->last_master_timestamp ?                                 max(0L, time_diff) : 0));  # 这里time_diff其实就是最终计算的Seconds_Behind_Master 值，如果为负数，则直接归零  }}

if ((mi->get_master_log_pos() == mi->rli->get_group_master_log_pos()) &&      (!strcmp(mi->get_master_log_name(), mi->rli->get_group_master_log_name())))

long time_diff= ((long)(time(0)                     - mi->rli->last_master_timestamp)                     - mi->clock_diff_with_master);