MySQL中server_id一致带来的问题

Dear 丶 2022-06-10 10:11 555阅读 0赞

简  介

我们都知道在MySQL搭建复制环境的时候，需要设置每个server的server\_id不一致，如果主库与从库的server\_id一致，那么复制会失败。但是最近在解决一个客户的问题的时候，遇到一个有意思的现象，客户环境有三台数据库服务器，一主两从，客户的两台从库设置了相同server\_id，在排查问题的过程中，查看MySQL错误日志，发现有很多奇怪的信息。   
我们模拟了客户的环境，并进行测试、分析，最终在代码中找到了我们想要的答案。下面就是我们测试、分析、总结的步骤以及内容。

测试步骤

环境介绍

*  主库   
    IP：192.168.1.130   
    server\_id：3656
 *  从库A   
    IP：192.168.1.36   
    server\_id：56
 *  从库B   
    IP：192.168.1.57   
    server\_id：56

三台主机除server\_id之外，其余配置如下：

> server\_id = 123
> 
> \[client\]
> 
> socket = /home/mysql/data/mysqldata5.5/sock/mysql.sock
> 
> \[mysqld\]
> 
> \#server\_id = 365
> 
> server\_id = 123
> 
> port = 3306
> 
> skip\_name\_resolve = 1
> 
> binlog\_format = ROW
> 
> \#binlog\_format = STATEMENT
> 
> basedir = /home/mysql/program/mysql5.5.36
> 
> datadir = /home/mysql/data/mysqldata5.5/mydata
> 
> socket = /home/mysql/data/mysqldata5.5/sock/mysql.sock
> 
> pid-file = /home/mysql/data/mysqldata5.5/sock/mysql.pid
> 
> tmpdir = /home/mysql/data/mysqldata5.5/tmpdir
> 
> log-error = /home/mysql/data/mysqldata5.5/log/error.log
> 
> slow\_query\_log
> 
> slow\_query\_log\_file = /home/mysql/data/mysqldata5.5/slowlog/slow-query.log
> 
> log-bin = /home/mysql/data/mysqldata5.5/binlog/mysql-bin
> 
> relay-log = /home/mysql/data/mysqldata5.5/relaylog/mysql-relay-bin
> 
> innodb\_data\_home\_dir = /home/mysql/data/mysqldata5.5/innodb\_ts
> 
> innodb\_log\_group\_home\_dir = /home/mysql/data/mysqldata5.5/innodb\_log
> 
> \#innodb\_undo\_directory = /home/mysql/data/mysqldata5.5/undo/
> 
> sync\_binlog=1
> 
> innodb\_file\_per\_table=1
> 
> \#skip\_grant\_tables
> 
> expire\_logs\_days = 1
> 
> log\_slave\_updates = ON
> 
> \#replicate-same-server-id=1
> 
> skip\_slave\_start
> 
> \#innodb\_undo\_tablespaces=1

5.5.36版本现象

初始搭建环境之后，查看各主机状态。搭建环境的步骤就省略。

#### 主库（192.168.1.130） ####

主库通过show processlist语句查看，只有一个dump线程，但是通过多次刷新，可以看到连接的是不同的服务器。可以看到每次通过show processlist语句显示的dump线程的Host字段中，IP:PORT的值是不断在更新的，说明dump线程在不断的重连，才会出现占用不同的端口的现象。

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1][]

####     ####

#### 从库A（192.168.1.36） ####

通过`show slave status\G`命令查看复制状态，多次执行可以看到`Slave_IO_Running`字段显示的内容，出现YES或者Connnecting两种状态。可以看到I/O线程在不断的进行重连。

并且通过`tail -f`命令查看error log，可以看到I/O线程一直在尝试重新连接。

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 1][]

可以看到在错误日志中打印的信息是，I/O线程连接 。

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 2][]

####     ####

#### 从库B（192.168.1.57） ####

从库B现象与从库A一致。

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 3][]

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 4][]

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 5][]

5.6.36版本现象

搭建环境步骤不在赘述。

####     ####

#### 主库（192.168.1.130） ####

show processlist查看有两个dump线程，并且多次刷新，发现Host字段中的IP:PORT并没有修改，说明dump线程一直保持连接。

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 6][]

####     ####

#### 从库A（192.168.1.36） ####

tail -f /home/mysql/data/mysqldata5.6/log/error.log查看错误日志，没有不断断开连接 。

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 7][]

####     ####

#### 从库B（192.168.1.57） ####

tail -f /home/mysql/data/mysqldata5.6/log/error.log查看错误日志，没有不断断开连接 。

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 8][]

原因分析

http://www.penglixun.com/tech/database/mysql\_multi\_slave\_same\_serverid.html这是彭立勋写的关于多个slave使用相同server\_id时冲突的原因的一篇文章。按照彭大大的分析，我理解的是，slave的I/O线程连接上主库的时候，主库上会调用`register_slave()`这个函数，在这个函数中又调用了unregister\_slave()函数，会将之前使用相同server\_id的线程给注销掉。从而导致从库的I/O线程不断断开重连。   
但是仔细看了一下`unregister_slave()`函数的代码，并没有发现MySQL是根据server\_id来注销dump线程的。并且进一步比较了一下5.5.36和5.6.36版本的代码，并没有发现不同。而从库设置server\_id一致导致I/O线程不断重连的现象只在5.5版本中看到，在5.6版本中并没有这个现象，所以导致5.5现象的原因不是在unregister\_slave()函数中。   
进一步看了一下彭大大的文章，发现有人在下面评论，说主要是`kill_zombie_slave_threads()`函数导致的。于是看了一下`kill_zombie_slave_threads()`函数的逻辑，发现MySQL应该就是在这一步根据server\_id将线程kill了。

*  5.5.36版本   
    首先来看下5.5.36版本的`kill_zombie_dump_threads()`函数的代码。看到这个函数传入的参数是一个uint32类型的slave\_server\_id,在函数中做的事情是，遍历MySQL中的所有线程，如果遍历到一个线程是dump线程并且线程的server\_id是等于传入的参数值话，则跳出遍历循环，并对kill掉这个线程。

> void kill\_zombie\_dump\_threads(uint32 slave\_server\_id)                          
> 
> \{                                                                              
> 
>  mysql\_mutex\_lock(&LOCK\_thread\_count);                                        
> 
>  I\_List\_iterator<THD> it(threads);                                            
> 
>  THD \*tmp;                                                                    
> 
>  while ((tmp=it++))                                                            
> 
>  \{                                                                            
> 
>    if (tmp->command == COM\_BINLOG\_DUMP &&                                      
> 
>      tmp->server\_id == slave\_server\_id)                                      
> 
>    \{                                                                          
> 
>     mysql\_mutex\_lock(&tmp->LOCK\_thd\_data);    // Lock from delete            
> 
>     break;                                                                    
> 
>    \}                                                                          
> 
>  \}                                                                            
> 
>  mysql\_mutex\_unlock(&LOCK\_thread\_count);                                      
> 
>  if (tmp)                                                                      
> 
>  \{                                                                            
> 
>    /\*                                                                          
> 
>     Here we do not call kill\_one\_thread() as                                  
> 
>     it will be slow because it will iterate through the list                  
> 
>     again. We just to do kill the thread ourselves.                          
> 
>    \*/                                                                          
> 
>    tmp->awake(THD::KILL\_QUERY);                                                
> 
>    mysql\_mutex\_unlock(&tmp->LOCK\_thd\_data);                                    
> 
>  \}                                                                            
> 
> \}

*  5.6.35版本   
    再来看一下5.6.36版本的`kill_zombie_dump_threads()`函数的代码实现，与5.5.36大不相同。首先传入的参数是一THD类型的指针，在函数中实现的逻辑同样是遍历MySQL中的所有线程，如果找到dump线程，首先看一下这个线程有没有uuid字段（因为uuid是在5.6之后的版本才有的，这边是为了兼容5.5），如果有uuid则用uuid进行比较，如果没有uuid，则用server\_id进行比较。

> void kill\_zombie\_dump\_threads(THD \*thd)                                                                                                  
> 
> \{                                                                              
> 
>  String slave\_uuid;                                                            
> 
>  get\_slave\_uuid(thd, &slave\_uuid);                                            
> 
>  if (slave\_uuid.length() == 0 && thd->server\_id == 0)                          
> 
>    return;                                                                    
> 
>   
> 
> 
>  mysql\_mutex\_lock(&LOCK\_thread\_count);                                        
> 
>  THD \*tmp= NULL;                                                              
> 
>  Thread\_iterator it= global\_thread\_list\_begin();                              
> 
>  Thread\_iterator end= global\_thread\_list\_end();                                
> 
>  bool is\_zombie\_thread= false;                                                
> 
>  for (; it != end; ++it)                                                      
> 
>  \{                                                                            
> 
>    if ((\*it) != thd && ((\*it)->get\_command() == COM\_BINLOG\_DUMP || (\*it)->get\_command() == COM\_BINLOG\_DUMP\_GTID))        
> 
>    \{                                                                          
> 
>     String tmp\_uuid;                                                          
> 
>     get\_slave\_uuid((\*it), &tmp\_uuid);                                        
> 
>     if (slave\_uuid.length())                                                  
> 
>     \{                                                                        
> 
>       is\_zombie\_thread= (tmp\_uuid.length() && !strncmp(slave\_uuid.c\_ptr(),                        
> 
>                                         tmp\_uuid.c\_ptr(), UUID\_LENGTH));
> 
>   
> 
> 
>     else                                                                      
> 
>     \{                                                                        
> 
>       /\*                                                                      
> 
>       ¦ Check if it is a 5.5 slave's dump thread i.e., server\_id should be    
> 
>       ¦ same && dump thread should not contain 'UUID'.                        
> 
>       \*/                                                                      
> 
>       is\_zombie\_thread= (((\*it)->server\_id == thd->server\_id) && !tmp\_uuid.length());                                
> 
>     \}                                                                        
> 
>     if (is\_zombie\_thread)                                                    
> 
>     \{                                                                        
> 
>       tmp= \*it;                                                              
> 
>       mysql\_mutex\_lock(&tmp->LOCK\_thd\_data);  // Lock from delete            
> 
>       break;                                                                  
> 
>     \}                                                                        
> 
>    \}                                                                          
> 
>  \}                                                                            
> 
>  mysql\_mutex\_unlock(&LOCK\_thread\_count);                                      
> 
>  if (tmp)                                                                      
> 
>  \{                                                                            
> 
>    /\*                                                                          
> 
>    ¦ Here we do not call kill\_one\_thread() as                                  
> 
>    ¦ it will be slow because it will iterate through the list                  
> 
>    ¦ again. We just to do kill the thread ourselves.                          
> 
>    \*/                                                                          
> 
>    if (log\_warnings > 1)                                                      
> 
>    \{                                                                          
> 
>     if (slave\_uuid.length())                                                  
> 
>     \{                                                                      
> 
> sql\_print\_information("While initializing dump thread for slave with "  
> 
> "UUID <%s>, found a zombie dump thread with the "
> 
> "same UUID. Master is killing the zombie dump "  
> 
> "thread(%lu).", slave\_uuid.c\_ptr(),              
> 
> tmp->thread\_id);                                  
> 
>     \}                                                                        
> 
>     else                                                                      
> 
>     \{                                                                        
> 
>       sql\_print\_information("While initializing dump thread for slave with "  
> 
> "server\_id <%u>, found a zombie dump thread with the "
> 
> "same server\_id. Master is killing the zombie dump "
> 
> "thread(%lu).", thd->server\_id,                  
> 
> tmp->thread\_id);                                  
> 
>     \}                                                                        
> 
>    \}                                                                          
> 
>    tmp->duplicate\_slave\_id= true;                                              
> 
>    tmp->awake(THD::KILL\_QUERY);                                                
> 
>    mysql\_mutex\_unlock(&tmp->LOCK\_thd\_data);                                    
> 
>  \}                                                                            
> 
> \}

函数调用

*  知道了`kill_zombie_dump_threads()`线程实现的逻辑，那MySQL是在什么地方会调用这个函数的呢。看了一下函数是在`case COM_BINLOG_DUMP`中被调用的。   
    在5.5.36版本中是在

> case COM\_BINLOG\_DUMP:                                                        
> 
> \{                                                                          
> 
> ulong pos;                                                                
> 
> ushort flags;                                                            
> 
> uint32 slave\_server\_id;                                                                                                                                
> 
> status\_var\_increment(thd->status\_var.com\_other);                          
> 
> thd->enable\_slow\_log= opt\_log\_slow\_admin\_statements;                      
> 
> if (check\_global\_access(thd, REPL\_SLAVE\_ACL))                            
> 
> break;                                                                                                                                                
> 
> /\* TODO: The following has to be changed to an 8 byte integer \*/          
> 
> pos = uint4korr(packet);                                                  
> 
> flags = uint2korr(packet + 4);                                            
> 
> thd->server\_id=0; /\* avoid suicide \*/                                    
> 
> if ((slave\_server\_id= uint4korr(packet+6))) // mysqlbinlog.server\_id==0  
> 
> kill\_zombie\_dump\_threads(slave\_server\_id);                                  
> 
> thd->server\_id = slave\_server\_id;                                                                                                                    
> 
> general\_log\_print(thd, command, "Log: '%s'  Pos: %ld", packet+10,  (long) pos);                                              
> 
> mysql\_binlog\_send(thd, thd->strdup(packet + 10), (my\_off\_t) pos, flags);  
> 
> unregister\_slave(thd,1,1);                                                
> 
> /\*  fake COM\_QUIT -- if we get here, the thread needs to terminate \*/    
> 
> error = TRUE;                                                            
> 
> break;                                                                    
> 
> \}

在5.6.36版本中也是在`case COM_BINLOG_DUMP`中，只不过是将之前的逻辑封装在了`com_binlog_dump()`函数中了，`kill_zombie_dump_threads()`也是在`com_binlog_dump()`函数中调用的。

> case COM\_BINLOG\_DUMP:                                                        
> 
> error= com\_binlog\_dump(thd, packet, packet\_length);                                                                                  
> 
> break;

`case COM_BINLOG_DUMP`中所进行的操作就是将dump线程通知I/O线程拉取新的binlog。

总  结

整理下来的话，基本上可以确定主要是因为`kill_zombie_dump_threads()`函数导致在5.6之前的版本中，如果是一主多从的架构中，如果在从库之间的server\_id如果设置为一样，会出现从开I/O线程不断断开重连的现象。因为在5.6之前的版本中，还没有UUID的概念，MySQL使用server\_id来区分是否是同一台机器，而在5.6之后的版本是使用的UUID来区分。   
总结一句，就是数据库之间的server\_id不要设置成一样，不然可能会有一些不可预知的错误。

简  介

测试步骤

环境介绍

*  主库   
    IP：192.168.1.130   
    server\_id：3656
 *  从库A   
    IP：192.168.1.36   
    server\_id：56
 *  从库B   
    IP：192.168.1.57   
    server\_id：56

三台主机除server\_id之外，其余配置如下：

5.5.36版本现象

初始搭建环境之后，查看各主机状态。搭建环境的步骤就省略。

#### **主库（192.168.1.130）** ####

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1][]

####     ####

#### **从库A（192.168.1.36）** ####

并且通过`tail -f`命令查看error log，可以看到I/O线程一直在尝试重新连接。

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 1][]

可以看到在错误日志中打印的信息是，I/O线程连接 。

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 2][]

####     ####

#### **从库B（192.168.1.57）** ####

从库B现象与从库A一致。

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 3][]

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 4][]

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 5][]

5.6.36版本现象

搭建环境步骤不在赘述。

####     ####

#### **主库（192.168.1.130）** ####

show processlist查看有两个dump线程，并且多次刷新，发现Host字段中的IP:PORT并没有修改，说明dump线程一直保持连接。

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 6][]

####     ####

#### **从库A（192.168.1.36）** ####

tail -f /home/mysql/data/mysqldata5.6/log/error.log查看错误日志，没有不断断开连接 。

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 7][]

####     ####

#### **从库B（192.168.1.57）** ####

tail -f /home/mysql/data/mysqldata5.6/log/error.log查看错误日志，没有不断断开连接 。

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 8][]

原因分析

*  **5.5.36版本 **  
    首先来看下5.5.36版本的`kill_zombie_dump_threads()`函数的代码。看到这个函数传入的参数是一个uint32类型的slave\_server\_id,在函数中做的事情是，遍历MySQL中的所有线程，如果遍历到一个线程是dump线程并且线程的server\_id是等于传入的参数值话，则跳出遍历循环，并对kill掉这个线程。

*  **5.6.35版本 **  
    再来看一下5.6.36版本的`kill_zombie_dump_threads()`函数的代码实现，与5.5.36大不相同。首先传入的参数是一THD类型的指针，在函数中实现的逻辑同样是遍历MySQL中的所有线程，如果找到dump线程，首先看一下这个线程有没有uuid字段（因为uuid是在5.6之后的版本才有的，这边是为了兼容5.5），如果有uuid则用uuid进行比较，如果没有uuid，则用server\_id进行比较。

**函数调用 **

`case COM_BINLOG_DUMP`中所进行的操作就是将dump线程通知I/O线程拉取新的binlog。

总  结

[640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1]: /images/20220610/84b1748d2121487cad5b291eeb68ca56.png
[640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 1]: /images/20220610/b5fa152871814a6d89d32b3731da91a2.png
[640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 2]: /images/20220610/5be03d5a41b34310a092c5c10a32c6aa.png
[640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 3]: /images/20220610/8503e1c1c41e4947affa465891b0ffbb.png
[640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 4]: /images/20220610/dc5ef025943b4816a9f8a8be42852289.png
[640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 5]: /images/20220610/51d9848e83d7425daf76e57508acd1fb.png
[640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 6]: /images/20220610/13248ca3eb34472a8d4e287d6cd57d09.png
[640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 7]: /images/20220610/e4625b844e764f2fbb37072f1776e497.png
[640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 8]: /images/20220610/826c06f3419a462cb6ce3e9c9088128d.png