Master-slave switching between master_pos_wait and MySQL

Background
 
Master-slave switchover is a necessary step for the high-availability MySQL architecture (that is, the use does not occur, but must be prepared ). Generally, it is set to double M (M1, M2). If the current status is M1 and M2 is read-only, the general process of switching is as follows:
 
1. Stop the application from writing M1, and set M1 to read-only.
2. Check the slave status of M2 until it catches up with M1
3. Set M1 to writable
 
 
 
The details are as follows:
A) show master status on M1; get the binlog position P, because it has been set to read-only and will not change
B) cyclically check the execution position on M2. If P is not reached, check the execution position in a few seconds. Loop until the slave database catches up.
 
Here we will introduce a function to simplify step B.
 
 
 
Function master_pos_wait
 
Syntax select master_pos_wait (file, pos [, timeout]).
Here, the file and pos correspond to the value obtained by the master database show master status, representing the execution position. The function logic is to wait until the current slave database reaches this position and return the number of transactions executed during the return period.
The timeout parameter is optional. If it is set to the default value, there is no limit to wait. If it is set to timeout <= 0, it is the same as the default logic. If it is a positive number, wait for so many seconds, and the timeout function returns-1.
Other return values: if the current slave is terminated during startup or waiting, NULL is returned. If the specified value has been reached before, 0 is returned.
 
 
 
Master_pos_wait implementation Logic
 
After you call this function, you can call pthread_cond_timedwait or pthread_cond_wait based on input parameters. The SQL _THREAD thread will trigger the relay info update every time after an event is applied, and notify the preceding waiting thread. Because multiple users may wait, broadcast is used.
 
The calculation of the number of events is complex. If you are interested, you can read this article. However, the return value of positive numbers is not important in this article.
 
 
 
Summary
 
Use master_pos_wait to implement step B above, which can be simplified:
B ') execute select master_pos_wait (file, pos) on M. If the returned value is greater than or equal to 0, the master-slave synchronization is completed.
 
 
 
The advantage is that
1) Simplify the logic and do not need to be judged by the application script.
2) You will be able to perceive it as soon as you catch up. Otherwise, it may take several seconds to wait.