MySQL多个Slave同一server_id的冲突原因分析-Mysql Tutorial-php.cn

Home

Database

Mysql Tutorial

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

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 07, 2016 pm 04:32 PM

mysqlserverconflictreasonMultiple

今天分析一个诡异问题，一个模拟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的实例，否则各种诡异的问题可是接踵而来。

Statement

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

What are some tools you can use to monitor MySQL performance?Apr 23, 2025 am 12:21 AM

How to effectively monitor MySQL performance? Use tools such as mysqladmin, SHOWGLOBALSTATUS, PerconaMonitoring and Management (PMM), and MySQL EnterpriseMonitor. 1. Use mysqladmin to view the number of connections. 2. Use SHOWGLOBALSTATUS to view the query number. 3.PMM provides detailed performance data and graphical interface. 4.MySQLEnterpriseMonitor provides rich monitoring functions and alarm mechanisms.

How does MySQL differ from SQL Server?Apr 23, 2025 am 12:20 AM

The difference between MySQL and SQLServer is: 1) MySQL is open source and suitable for web and embedded systems, 2) SQLServer is a commercial product of Microsoft and is suitable for enterprise-level applications. There are significant differences between the two in storage engine, performance optimization and application scenarios. When choosing, you need to consider project size and future scalability.

In what scenarios might you choose SQL Server over MySQL?Apr 23, 2025 am 12:20 AM

In enterprise-level application scenarios that require high availability, advanced security and good integration, SQLServer should be chosen instead of MySQL. 1) SQLServer provides enterprise-level features such as high availability and advanced security. 2) It is closely integrated with Microsoft ecosystems such as VisualStudio and PowerBI. 3) SQLServer performs excellent in performance optimization and supports memory-optimized tables and column storage indexes.

How does MySQL handle character sets and collations?Apr 23, 2025 am 12:19 AM

MySQLmanagescharactersetsandcollationsbyusingUTF-8asthedefault,allowingconfigurationatdatabase,table,andcolumnlevels,andrequiringcarefulalignmenttoavoidmismatches.1)Setdefaultcharactersetandcollationforadatabase.2)Configurecharactersetandcollationfor

What are triggers in MySQL?Apr 23, 2025 am 12:11 AM

A MySQL trigger is an automatically executed stored procedure associated with a table that is used to perform a series of operations when a specific data operation is performed. 1) Trigger definition and function: used for data verification, logging, etc. 2) Working principle: It is divided into BEFORE and AFTER, and supports row-level triggering. 3) Example of use: Can be used to record salary changes or update inventory. 4) Debugging skills: Use SHOWTRIGGERS and SHOWCREATETRIGGER commands. 5) Performance optimization: Avoid complex operations, use indexes, and manage transactions.

How do you create and manage user accounts in MySQL?Apr 22, 2025 pm 06:05 PM

The steps to create and manage user accounts in MySQL are as follows: 1. Create a user: Use CREATEUSER'newuser'@'localhost'IDENTIFIEDBY'password'; 2. Assign permissions: Use GRANTSELECT, INSERT, UPDATEONmydatabase.TO'newuser'@'localhost'; 3. Fix permission error: Use REVOKEALLPRIVILEGESONmydatabase.FROM'newuser'@'localhost'; then reassign permissions; 4. Optimization permissions: Use SHOWGRA

How does MySQL differ from Oracle?Apr 22, 2025 pm 05:57 PM

MySQL is suitable for rapid development and small and medium-sized applications, while Oracle is suitable for large enterprises and high availability needs. 1) MySQL is open source and easy to use, suitable for web applications and small and medium-sized enterprises. 2) Oracle is powerful and suitable for large enterprises and government agencies. 3) MySQL supports a variety of storage engines, and Oracle provides rich enterprise-level functions.

What are the disadvantages of using MySQL compared to other relational databases?Apr 22, 2025 pm 05:49 PM

The disadvantages of MySQL compared to other relational databases include: 1. Performance issues: You may encounter bottlenecks when processing large-scale data, and PostgreSQL performs better in complex queries and big data processing. 2. Scalability: The horizontal scaling ability is not as good as Google Spanner and Amazon Aurora. 3. Functional limitations: Not as good as PostgreSQL and Oracle in advanced functions, some functions require more custom code and maintenance.

See all articles

Hot AI Tools

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress images for free

Clothoff.io

AI clothes remover

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Assassin's Creed Shadows: Seashell Riddle Solution

3 weeks agoByDDD

What's New in Windows 11 KB5054979 & How to Fix Update Issues

2 weeks agoByDDD

Where to find the Crane Control Keycard in Atomfall

3 weeks agoByDDD

Roblox: Dead Rails - How To Complete Every Challenge

4 weeks agoByDDD

Atomfall guide: item locations, quest guides, and tips

4 weeks agoByDDD

Hot Tools

Zend Studio 13.0.1

Powerful PHP integrated development environment

SublimeText3 Chinese version

Chinese version, very easy to use

MinGW - Minimalist GNU for Windows

This project is in the process of being migrated to osdn.net/projects/mingw, you can continue to follow us there. MinGW: A native Windows port of the GNU Compiler Collection (GCC), freely distributable import libraries and header files for building native Windows applications; includes extensions to the MSVC runtime to support C99 functionality. All MinGW software can run on 64-bit Windows platforms.

PhpStorm Mac version

The latest (2018.2.1) professional PHP integrated development tool

SublimeText3 Mac version

God-level code editing software (SublimeText3)

Hot Topics

Where is the login entrance for gmail email?

7664

CakePHP Tutorial

1393

C# Tutorial

1205

What is the format of the account name of steam

win11 activation key permanent