In this blog post, we will discuss how we can verify if an application transaction executed on the master has been applied to the slaves.
In summary, is a good practice to alleviate the load on the master by doing reads on slaves. It is acceptable in most of the cases to just connect on slaves and issue selects. But there are some cases we need to ensure that the data we just applied on our master has been applied on the slaves before we query it.
Blocking the connection until the slave is in sync with the coordinate passed as a parameter on MASTER_POS_WAIT might not be affordable to all applications, however.
You can use relay_log_info_repository as a replacement for MASTER_POS_WAIT to check if a slave has applied a particular transaction. Since it won’t block your thread (in case the slave is not in sync) you will be able to either abort the operation or disconnect and move to the next slave.
If you are using GTID’s, you can use WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS as a replacement for MASTER_POS_WAIT. It works very similar to the non-GTID function: you need to pass a gtid_set as a parameter, and the function will return the cursor once the slave has received all GTIDs from the given parameter.