Checking if a Slave Has Applied a Transaction from the Master




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.

As an alternative, MySQL 5.6+ makes use of relay_log_info_repository configuration. If we set this variable to TABLE, MySQL stores the slave status information in the  slave_relay_log_info table under mysql database. We must configure the sync_relay_log_info variable, and set to 1 in case we use non-transactional tables such as MyISAM. It forces  slave_relay_log_info  to sync after each statement. So edit my.cnf on slaves:

Now we can query slave_relay_log_info directly to see if the slave we are connected to already applied the transaction we need:


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.

Share this post

Leave a Reply