DBA > Job Interview Questions > MySQL and SQL

MySQL - Why do I sometimes see more than one Bin

More DBA job interview questions and answers at http://dba.fyicenter.com/Interview-Questions/

(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 kill.
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...)

Other Job Interview Questions