master_pos_wait function and MySQL master-slave switch

Background

Master-Slave Switching is a necessary step for a highly available MySQL architecture (i.e., use does not occur and is prepared). Generally set to double m (M1, M2), assuming the current state is write M1, and M2 read-only, the approximate process of switching is as follows:

1. Stop applying write M1 and set M1 to read-only
2, check M2 slave status until catch up with M1
3. Set the M1 to writable

In which the 2nd step was finely
A) Show master status on M1, get Binlog position p because it is set to read-only and will not change
b) Cyclic detection of the execution position on the M2, if not to P, then a few seconds to check again. Loop until it catches up from the library.

A function is introduced here to simplify step b.

function master_pos_wait

Syntax Select master_pos_wait (file, pos[, timeout]).
The file and Pos here correspond to the value obtained by the main library show master status, which represents the execution location. The function logic is to wait for the current from the library to return to this position, the number of transactions executed during the return.
Parameter timeout is optional, and if the default is infinite wait, the timeout<=0 is the same as the default logic. If it is positive, wait so many seconds for the timeout function to return-1.
Other return value: Returns null if the current slave is started or terminated during wait, returns 0 if the specified value has been reached before

The implementation logic of master_pos_wait

After the user calls the function, pthread_cond_timedwait or pthread_cond_wait are called according to the incoming parameters. The sql_thread thread triggers an update of relay info each time an event is completed and notifies the waiting thread. Because there may be multiple users waiting, it is broadcast.

The calculation of the number of events is more complex, interested students can read this, but in this article discussed in this issue, the positive return value is not important.

Summary

Using master_pos_wait to implement step B above can be simplified to:
B ') executes the Select master_pos_wait (file, POS) on M, and after returning to determine the return value >=0 the master-slave synchronization is completed.

Advantage is
1) Simplify the logic without having to judge in the application script
2) in the first time to catch up can perceive, otherwise it may wait a few seconds

master_pos_wait function and MySQL master-slave switch