Not the answer you need?
Register and ask your own question!

Exec_Master_Log_Pos less than Read_Master_Log_Pos after Slave I/O thread stopped

chris-cardchris-card ContributorCurrent User Role Novice
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

Comments

  • chris-cardchris-card Contributor Current User Role Novice
    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.pocklingtonlorraine.pocklington Percona Community Manager Legacy User Role Patron
    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-cardchris-card Contributor Current User Role Novice
    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.pocklingtonlorraine.pocklington Percona Community Manager Legacy User Role Patron
    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.pocklingtonlorraine.pocklington Percona Community Manager Legacy User Role Patron
    I had an update
    For the export, would this work for you?

    mysqldump --single-transaction --master-data=2 --etc --etc
  • chris-cardchris-card Contributor Current User Role Novice
    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.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.