Analysis of the conflict between MySQL Slave and server_id

Today, a strange problem is analyzed. A program simulating the Slave thread is continuously killed by the Master Server. The final result is that two Slave instances use the same server id to connect to the Master Server, why do two Slave instances with the same server id be killed by the Master Server? The source code is analyzed, which is derived from the reconnection mechanism of MySQL Replication.

First, let's take a look at what will happen when a Slave registers to the Master. First, Slave needs to send a COM_REGISTER_SLAVE type request (SQL _parse.cc) command request to the Master, here, the Master will use the register_slave function to register an Slave to slave_list.

The Code is as follows:	Copy code
Case COM_REGISTER_SLAVE: { If (! Register_slave (thd, (uchar *) packet, packet_length )) My_ OK (thd ); Break; }

What will happen when registering the Slave thread? Let's skip useless code and look at the key points: (repl_failsafe.cc)

The Code is as follows:

Copy code

Int register_slave (THD * thd, uchar * packet, uint packet_length) { Int res; SLAVE_INFO * si; Uchar * p = packet, * p_end = packet + packet_length; ... // Omitted If (! (Si-> master_id = uint4korr (p ))) Si-> master_id = server_id; Si-> thd = thd; Pthread_mutex_lock (& LOCK_slave_list ); Unregister_slave (thd,); // here, the key is to cancel registering the same Slave thread as server_id. Res = my_hash_insert (& slave_list, (uchar *) si); // register the new Slave thread to slave_list Pthread_mutex_unlock (& LOCK_slave_list ); Return res; ..... }

What does this mean? This is the reconnection mechanism. slave_list is a Hash table and server_id is the Key. When registering each thread, You need to delete the Slave thread with the same server_id and add the new Slave thread to the slave_list table.

After the thread registers, it requests the Binlog and sends the COM_BINLOG_DUMP request. The Master sends the binlog to the Slave. The Code is as follows:

 

The Code is as follows:

Copy code

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); // continuously send logs to the slave end Unregister_slave (thd,); // clear the Slave thread after the message is sent, because the binlog dump thread is kill after the execution. /* Fake COM_QUIT -- if we get here, the thread needs to terminate */ Error = TRUE; Break; }

The mysql_binlog_send function is in SQL _repl.cc, which polls the Master binlog and sends it to Slave.

Let's take a look at what unregister_slave has done (repl_failsafe.cc ):

The Code is as follows:

Copy code

Void unregister_slave (THD * thd, bool only_mine, bool need_mutex) { If (thd-> server_id) { If (need_mutex) Pthread_mutex_lock (& LOCK_slave_list ); SLAVE_INFO * old_si; If (old_si = (SLAVE_INFO *) hash_search (& slave_list, (Uchar *) & thd-> server_id, 4 ))&& (! Only_mine | old_si-> thd = thd) // get the slave Value Hash_delete (& slave_list, (uchar *) old_si); // remove it from slave_list If (need_mutex) Pthread_mutex_unlock (& LOCK_slave_list ); } }

This explains why the same server_id is killed, because once registered, the Slave thread with the same server_id will be deleted and the current Slave will be added, this is because sometimes Slave is disconnected and a request is sent again. Of course, you need to kill the original thread. This is the thread reconnection mechanism.