From my understanding, once commit() is received from client, transactions are written to binlog first before any commit is done to InnoDB.

In this case, is it possible for slaves to be ahead of master if master is suffering from some performance issues?

  • Interesting. Relevant part from the docs: Within an uncommitted transaction, all updates (UPDATE, DELETE, or INSERT) that change transactional tables such as InnoDB tables are cached until a COMMIT statement is received by the server. At that point, mysqld writes the entire transaction to the binary log before the COMMIT is executed – Nanne Sep 11 '19 at 15:28

On one occasion, I helped a company that had a replica ahead of its master. It was a very odd situation. It isn't supposed to be possible.

What caused it was that their master server's hard drives were failing, and data was not able to be written to disk. So the data committed to the InnoDB redo log and tablespace wasn't being saved.

But the transactions were committed as far as MySQL knew, and it was writing to the binary logs, which used buffered I/O, so the operating system still had a portion of the file in RAM, trying to sync to disk.

Then the replica requested the latest binary log events, and the master sent them, and the replica faithfully applied them to its database. Since the replica's disk was not having faults, it saved to disk fine.

The master server finally rebooted after many disk I/O faults. It came back up and was able to start processes. MySQL read its InnoDB redo log file, trying to do crash recovery, but the latest transactions had never been written to the disk, so they were not able to be recovered.

But the replica had gotten those events!

Thus the replica was slightly ahead of its master.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.