More DBA job interview questions and answers at
(Continued from previous question...)
MySQL - Why do I sometimes see more than one Binlog_Dump thread on the master after I have restarted the slave?
Binlog_Dump is a continuous process that is handled by the server in the following way:
Catch up on the updates.
Once there are no more updates left, go into pthread_cond_wait(), from which we can be awakened either by an update or a
On wake up, check the reason. If we are not supposed to die, continue the Binlog_dump loop.
If there is some fatal error, such as detecting a dead client, terminate the loop.
So if the slave thread stops on the slave, the corresponding Binlog_Dump thread on the master will not notice it until after
at least one update to the master (or a kill), which is needed to wake it up from pthread_cond_wait(). In the meantime, the
slave could have opened another connection, which resulted in another Binlog_Dump thread.
The above problem should not be present in Version 3.23.26 and later versions. In Version 3.23.26 we added server-id to each
replication server, and now all the old zombie threads are killed on the master when a new replication thread connects from
the same slave
(Continued on next question...)