Exec_Master_Log_Pos less than Read_Master_Log_Pos after Slave I/O thread stopped

[chris-card]

We are running a Percona 5.5.22 master replicating to Percona 5.5.62 slaves on CentOS 6.9.
We have a database backup script which runs on the slaves and before taking a backup the script attempts to ensure that replication is in sync on the slave.
It does this by stopping the Slave I/O thread and waiting until Exec_Master_Log_Pos = Read_Master_Log_Pos.
Most of the time this works fine, but occasionally Exec_Master_Log_Pos never reaches Read_Master_Log_Pos - it just sticks at a value lower than Read_Master_Log_Pos, even after 5 minutes, and so the backup doesn’t get run.
Any idea what is going on? Is this a valid way of checking that replication has caught up?

Chris

[chris-card]

An actual example:
after stopping the slave i/o thread, the Read_Master_Log_Pos was 1007861939 and the Exec_Master_Log_Pos was 1007861863.
Decoding the corresponding binary log showed this:

at 1007861863

#200108 9:05:00 server id 172032277 end_log_pos 1007861939 Query thread_id=585405709 exec_time=0 error_code=0
SET TIMESTAMP=1578474300/!/;
BEGIN
/!/;

at 1007861939

So it appears that replication has read the SET TIMESTAMP …;BEGIN event, but doesn’t exec it. Is that expected?

[lorraine.pocklington]

Hi chris-card
Can I just check for the sake of clarity if you are using Percona XtraDB Backup in your backup scripts and if so which version?
Also, I know that you probably already realize this (sorry for the added hassle factor) but please be aware that 5.5 is end-of-life software…
Let me know on PXB and I will see if anyone is about to advise on this question.

https://www.percona.com/services/policies/percona-software-platform-lifecycle#lifecycle

[chris-card]

Hi Lorraine,
in this case we aren’t using XtraDB Backup, we’re using mysqldump. We may use XtraDB Backup in the future, though I had the impression that the resulting backups are bigger because the ibdata1 file is copied, and that could be a problem for us.
We also plan to upgrade to 5.6 at some point, but we’re upgrading hardware at the moment and we didn’t want to change too much at once.

Chris

[lorraine.pocklington]

All good, just wanted to make sure of the scenario.
Can I just get you to check if either of these articles help you out here:
https://www.percona.com/blog/2014/05/02/how-to-identify-and-cure-mysql-replication-slave-lag/
https://www.percona.com/blog/2013/04/17/reset-slave-vs-reset-slave-all-disconnecting-a-replication-slave-is-easier-with-mysql-5-5/
However, I’ll also see if I can engage someone that might have a straight answer.
Thanks!

[lorraine.pocklington]

I had an update
For the export, would this work for you?

mysqldump --single-transaction --master-data=2 --etc --etc

[chris-card]

The problem isn’t the backup, but detecting that replication is up-to-date before running the backup. It seems that relying on Read_Master_Log_Pos == Exec_Master_Log_Pos is not correct, so I am asking if there is a more reliable test.