Maison  >  Article  >  base de données  >  MySQL多个Slave同一server_id的冲突原因分析

MySQL多个Slave同一server_id的冲突原因分析

WBOY
WBOYoriginal
2016-06-07 16:32:071128parcourir

本文内容遵从CC版权协议, 可以随意转载, 但必须以超链接形式标明文章原始出处和作者信息及版权声明网址: http://www.penglixun.com/tech/database/mysql_multi_slave_same_serverid.html 今天分析一个诡异问题,一个模拟Slave线程的程序,不断的被Master Ser

本文内容遵从CC版权协议, 可以随意转载, 但必须以超链接形式标明文章原始出处和作者信息及版权声明网址: http://www.penglixun.com/tech/database/mysql_multi_slave_same_serverid.html

今天分析一个诡异问题,一个模拟Slave线程的程序,不断的被Master Server给kill掉,最终发现是因为有两个Slave使用同样一个server id去连接Master Server,为什么两个Slave用同一个server id会被Master Server给Kill呢?分析了源码,这源于MySQL Replication的重连机制

我们首先看看一个Slave注册到Master会发生什么,首先Slave需要向Master发送一个COM_REGISTER_SLAVE类型的请求(sql_parse.cc)命令请求,这里Master会使用register_slave函数注册一个Slave到slave_list。

  <span style="color: #0000ff;">case</span> COM_REGISTER_SLAVE<span style="color: #008080;">:</span>
  <span style="color: #008000;">&#123;</span>
    <span style="color: #0000ff;">if</span> <span style="color: #008000;">&#40;</span><span style="color: #000040;">!</span>register_slave<span style="color: #008000;">&#40;</span>thd, <span style="color: #008000;">&#40;</span>uchar<span style="color: #000040;">*</span><span style="color: #008000;">&#41;</span>packet, packet_length<span style="color: #008000;">&#41;</span><span style="color: #008000;">&#41;</span>
      my_ok<span style="color: #008000;">&#40;</span>thd<span style="color: #008000;">&#41;</span><span style="color: #008080;">;</span>
    <span style="color: #0000ff;">break</span><span style="color: #008080;">;</span>
  <span style="color: #008000;">&#125;</span>

在注册Slave线程的时候会发生什么呢?我们略去无用的代码直接看重点:(repl_failsafe.cc)

<span style="color: #0000ff;">int</span> register_slave<span style="color: #008000;">&#40;</span>THD<span style="color: #000040;">*</span> thd, uchar<span style="color: #000040;">*</span> packet, uint packet_length<span style="color: #008000;">&#41;</span>
<span style="color: #008000;">&#123;</span>
  <span style="color: #0000ff;">int</span> res<span style="color: #008080;">;</span>
  SLAVE_INFO <span style="color: #000040;">*</span>si<span style="color: #008080;">;</span>
  uchar <span style="color: #000040;">*</span>p<span style="color: #000080;">=</span> packet, <span style="color: #000040;">*</span>p_end<span style="color: #000080;">=</span> packet <span style="color: #000040;">+</span> packet_length<span style="color: #008080;">;</span>
.... <span style="color: #666666;">//省略</span>
  <span style="color: #0000ff;">if</span> <span style="color: #008000;">&#40;</span><span style="color: #000040;">!</span><span style="color: #008000;">&#40;</span>si<span style="color: #000040;">-</span><span style="color: #000080;">></span>master_id<span style="color: #000080;">=</span> uint4korr<span style="color: #008000;">&#40;</span>p<span style="color: #008000;">&#41;</span><span style="color: #008000;">&#41;</span><span style="color: #008000;">&#41;</span>
    si<span style="color: #000040;">-</span><span style="color: #000080;">></span>master_id<span style="color: #000080;">=</span> server_id<span style="color: #008080;">;</span>
  si<span style="color: #000040;">-</span><span style="color: #000080;">></span>thd<span style="color: #000080;">=</span> thd<span style="color: #008080;">;</span>
  pthread_mutex_lock<span style="color: #008000;">&#40;</span><span style="color: #000040;">&</span>LOCK_slave_list<span style="color: #008000;">&#41;</span><span style="color: #008080;">;</span>
  unregister_slave<span style="color: #008000;">&#40;</span>thd,<span style="color: #0000dd;">0</span>,<span style="color: #0000dd;">0</span><span style="color: #008000;">&#41;</span><span style="color: #008080;">;</span> <span style="color: #666666;">//关键在这里,先取消注册server_id相同的Slave线程</span>
  res<span style="color: #000080;">=</span> my_hash_insert<span style="color: #008000;">&#40;</span><span style="color: #000040;">&</span>slave_list, <span style="color: #008000;">&#40;</span>uchar<span style="color: #000040;">*</span><span style="color: #008000;">&#41;</span> si<span style="color: #008000;">&#41;</span><span style="color: #008080;">;</span> <span style="color: #666666;">//把新的Slave线程注册到slave_list</span>
  pthread_mutex_unlock<span style="color: #008000;">&#40;</span><span style="color: #000040;">&</span>LOCK_slave_list<span style="color: #008000;">&#41;</span><span style="color: #008080;">;</span>
  <span style="color: #0000ff;">return</span> res<span style="color: #008080;">;</span>
.....
<span style="color: #008000;">&#125;</span>

这是什么意思呢?这就是重连机制,slave_list是一个Hash表,server_id是Key,每一个线程注册上来,需要删掉同样server_id的Slave线程,再把新的Slave线程加到slave_list表中。

线程注册上来后,请求Binlog,发送COM_BINLOG_DUMP请求,Master会发送binlog给Slave,代码如下:

  <span style="color: #0000ff;">case</span> COM_BINLOG_DUMP<span style="color: #008080;">:</span>
    <span style="color: #008000;">&#123;</span>
      ulong pos<span style="color: #008080;">;</span>
      ushort flags<span style="color: #008080;">;</span>
      uint32 slave_server_id<span style="color: #008080;">;</span>
 
      status_var_increment<span style="color: #008000;">&#40;</span>thd<span style="color: #000040;">-</span><span style="color: #000080;">></span>status_var.<span style="color: #007788;">com_other</span><span style="color: #008000;">&#41;</span><span style="color: #008080;">;</span>
      thd<span style="color: #000040;">-</span><span style="color: #000080;">></span>enable_slow_log<span style="color: #000080;">=</span> opt_log_slow_admin_statements<span style="color: #008080;">;</span>
      <span style="color: #0000ff;">if</span> <span style="color: #008000;">&#40;</span>check_global_access<span style="color: #008000;">&#40;</span>thd, REPL_SLAVE_ACL<span style="color: #008000;">&#41;</span><span style="color: #008000;">&#41;</span>
        <span style="color: #0000ff;">break</span><span style="color: #008080;">;</span>
 
      <span style="color: #ff0000; font-style: italic;">/* TODO: The following has to be changed to an 8 byte integer */</span>
      pos <span style="color: #000080;">=</span> uint4korr<span style="color: #008000;">&#40;</span>packet<span style="color: #008000;">&#41;</span><span style="color: #008080;">;</span>
      flags <span style="color: #000080;">=</span> uint2korr<span style="color: #008000;">&#40;</span>packet <span style="color: #000040;">+</span> <span style="color: #0000dd;">4</span><span style="color: #008000;">&#41;</span><span style="color: #008080;">;</span>
      thd<span style="color: #000040;">-</span><span style="color: #000080;">></span>server_id<span style="color: #000080;">=</span><span style="color: #0000dd;">0</span><span style="color: #008080;">;</span> <span style="color: #ff0000; font-style: italic;">/* avoid suicide */</span>
      <span style="color: #0000ff;">if</span> <span style="color: #008000;">&#40;</span><span style="color: #008000;">&#40;</span>slave_server_id<span style="color: #000080;">=</span> uint4korr<span style="color: #008000;">&#40;</span>packet<span style="color: #000040;">+</span><span style="color: #0000dd;">6</span><span style="color: #008000;">&#41;</span><span style="color: #008000;">&#41;</span><span style="color: #008000;">&#41;</span> <span style="color: #666666;">// mysqlbinlog.server_id==0</span>
        kill_zombie_dump_threads<span style="color: #008000;">&#40;</span>slave_server_id<span style="color: #008000;">&#41;</span><span style="color: #008080;">;</span>
      thd<span style="color: #000040;">-</span><span style="color: #000080;">></span>server_id <span style="color: #000080;">=</span> slave_server_id<span style="color: #008080;">;</span>
 
      general_log_print<span style="color: #008000;">&#40;</span>thd, command, <span style="color: #FF0000;">"Log: '%s'  Pos: %ld"</span>, packet<span style="color: #000040;">+</span><span style="color: #0000dd;">10</span>,
                      <span style="color: #008000;">&#40;</span><span style="color: #0000ff;">long</span><span style="color: #008000;">&#41;</span> pos<span style="color: #008000;">&#41;</span><span style="color: #008080;">;</span>
      mysql_binlog_send<span style="color: #008000;">&#40;</span>thd, thd<span style="color: #000040;">-</span><span style="color: #000080;">></span>strdup<span style="color: #008000;">&#40;</span>packet <span style="color: #000040;">+</span> <span style="color: #0000dd;">10</span><span style="color: #008000;">&#41;</span>, <span style="color: #008000;">&#40;</span>my_off_t<span style="color: #008000;">&#41;</span> pos, flags<span style="color: #008000;">&#41;</span><span style="color: #008080;">;</span> <span style="color: #666666;">//不断的发送日志给slave端</span>
      unregister_slave<span style="color: #008000;">&#40;</span>thd,<span style="color: #0000dd;">1</span>,<span style="color: #0000dd;">1</span><span style="color: #008000;">&#41;</span><span style="color: #008080;">;</span> <span style="color: #666666;">//发送完成后清理Slave线程,因为执行到这一步肯定是binlog dump线程被kill了</span>
      <span style="color: #ff0000; font-style: italic;">/*  fake COM_QUIT -- if we get here, the thread needs to terminate */</span>
      error <span style="color: #000080;">=</span> TRUE<span style="color: #008080;">;</span>
      <span style="color: #0000ff;">break</span><span style="color: #008080;">;</span>
    <span style="color: #008000;">&#125;</span>

mysql_binlog_send函数在sql_repl.cc,里面是轮询Master binlog,发送给Slave。

再来简单看看unregister_slave做了什么(repl_failsafe.cc):

<span style="color: #0000ff;">void</span> unregister_slave<span style="color: #008000;">&#40;</span>THD<span style="color: #000040;">*</span> thd, <span style="color: #0000ff;">bool</span> only_mine, <span style="color: #0000ff;">bool</span> need_mutex<span style="color: #008000;">&#41;</span>
<span style="color: #008000;">&#123;</span>
  <span style="color: #0000ff;">if</span> <span style="color: #008000;">&#40;</span>thd<span style="color: #000040;">-</span><span style="color: #000080;">></span>server_id<span style="color: #008000;">&#41;</span>
  <span style="color: #008000;">&#123;</span>
    <span style="color: #0000ff;">if</span> <span style="color: #008000;">&#40;</span>need_mutex<span style="color: #008000;">&#41;</span>
      pthread_mutex_lock<span style="color: #008000;">&#40;</span><span style="color: #000040;">&</span>LOCK_slave_list<span style="color: #008000;">&#41;</span><span style="color: #008080;">;</span>
 
    SLAVE_INFO<span style="color: #000040;">*</span> old_si<span style="color: #008080;">;</span>
    <span style="color: #0000ff;">if</span> <span style="color: #008000;">&#40;</span><span style="color: #008000;">&#40;</span>old_si <span style="color: #000080;">=</span> <span style="color: #008000;">&#40;</span>SLAVE_INFO<span style="color: #000040;">*</span><span style="color: #008000;">&#41;</span>hash_search<span style="color: #008000;">&#40;</span><span style="color: #000040;">&</span>slave_list,
                                           <span style="color: #008000;">&#40;</span>uchar<span style="color: #000040;">*</span><span style="color: #008000;">&#41;</span><span style="color: #000040;">&</span>thd<span style="color: #000040;">-</span><span style="color: #000080;">></span>server_id, <span style="color: #0000dd;">4</span><span style="color: #008000;">&#41;</span><span style="color: #008000;">&#41;</span> <span style="color: #000040;">&&</span>
        <span style="color: #008000;">&#40;</span><span style="color: #000040;">!</span>only_mine <span style="color: #000040;">||</span> old_si<span style="color: #000040;">-</span><span style="color: #000080;">></span>thd <span style="color: #000080;">==</span> thd<span style="color: #008000;">&#41;</span><span style="color: #008000;">&#41;</span> <span style="color: #666666;">//拿到slave值</span>
    hash_delete<span style="color: #008000;">&#40;</span><span style="color: #000040;">&</span>slave_list, <span style="color: #008000;">&#40;</span>uchar<span style="color: #000040;">*</span><span style="color: #008000;">&#41;</span>old_si<span style="color: #008000;">&#41;</span><span style="color: #008080;">;</span> <span style="color: #666666;">//从slave_list中拿掉</span>
 
    <span style="color: #0000ff;">if</span> <span style="color: #008000;">&#40;</span>need_mutex<span style="color: #008000;">&#41;</span>
      pthread_mutex_unlock<span style="color: #008000;">&#40;</span><span style="color: #000040;">&</span>LOCK_slave_list<span style="color: #008000;">&#41;</span><span style="color: #008080;">;</span>
  <span style="color: #008000;">&#125;</span>
<span style="color: #008000;">&#125;</span>

这就可以解释同样的server_id为什么会被kill,因为一旦注册上去,就会现删除相同server_id的Slave线程,然后把当前的Slave加入,这是因为有时Slave断开了,重新请求上来,当然需要踢掉原来的线程,这就是线程重连机制。

切记,一个MySQL集群中,绝不可以出现相同server_id的实例,否则各种诡异的问题可是接踵而来。

Déclaration:
Le contenu de cet article est volontairement contribué par les internautes et les droits d'auteur appartiennent à l'auteur original. Ce site n'assume aucune responsabilité légale correspondante. Si vous trouvez un contenu suspecté de plagiat ou de contrefaçon, veuillez contacter admin@php.cn