This function is useful for control of master/slave synchronization. It blocks until the slave has read and applied all updates up to the specified position in the master log. The return value is the number of log events the slave had to wait for to advance to the specified position. The function returns NULL if the slave SQL thread is not started, the slave's master information is not initialized, the arguments are incorrect, or an error occurs. It returns -1 if the timeout has been exceeded. If the slave SQL thread stops while MASTER_POS_WAIT() is waiting, the function returns NULL. If the slave is past the specified position, the function returns immediately.
If a timeout value is specified, MASTER_POS_WAIT() stops waiting when timeout seconds have elapsed. timeout must be greater than 0; a zero or negative timeout means no timeout.
This is great for applications which auto promote masters, but one has to account for race conditions.
For example, dbmaster1 and dbmaster2 are in bi-directional replication displayed below.
dbmaster1 <----> dbmaster2
dbmaster2 has slaves called dbslaves1 and dbslaves2. We want to replace dbmaster2 with dbslave1.
On dbmaster1 issue:
On dbmaster2 stop external writes to it and execute
SELECT MASTER_POS_WAIT('dbmaster1-bin.000002', 4); SLAVE STOP;
db3>show slave status\G
*************************** 1. row ***************************
Notice Exec_Master_Log_Pos is at POS 805. This is a small window of events that passed. So, make sure to account for it.
Now on dbmaster2 issue:
Do the same steps for dbslave1-2 with MASTER_WAIT_POS but for the position on dbmaster2 not dbmaster1.
Make dbslave1 a master by adding
log-bin to my.cnf
Issue CHANGE MASTER TO on dbslave1 to dbmaster1 // this moves dbslave1 to dbmaster1
CHANGE MASTER TO MASTER_HOST='dbmaster1', MASTER_LOG_POS=805, MASTER_LOG_FILE='dbmaster1.000002';
Issue CHANGE MASTER TO on dbslave2 to dbslave1 // this moves dbslave2 to dbslave1
CHANGE MASTER TO MASTER_HOST='dbslave1', MASTER_LOG_POS=4, MASTER_LOG_FILE='dbslave1-bin.000001';
Enable writes on dbslave1
I wrote an application that does this all for me, using IPC, fork, db connections and the "algorithm" above. If your interested in it. I'll post it here.