2

When exactly an event on the master(update/insert/alter) is logged on the binary log ?

  1. When it started on the master server, it will be written to the bin log
  2. When it started and completed on the master, then will be logged

In specific, I would like to know the behaviour in case of an ALTER If answer is 1, there won't be lag between master and slave in normal conditions only due to ALTERs started on the master.

ks1322
  • 33,961
  • 14
  • 109
  • 164
Uday
  • 1,480
  • 4
  • 27
  • 44

1 Answers1

1

Non-transactional statements are written to the binary log immediately. For MySQL 5+, this means any statement against a storage engine that doesn't support transactions are written immediately.

You shouldn't be executing statements against a non-transactional table inside a transaction though.

Transactional statements are written to the binary log when the transaction is committed. For MySQL 5+, this means any statement against a transactional table.

Currently, ALTER TABLE forces an implicit commit, so this will immediately be written to the binary log. According to MySQL documentation:

Binary logging is done immediately after a statement completes but before any locks are released or any commit is done. This ensures that the log is logged in execution order.

Marcus Adams
  • 53,009
  • 9
  • 91
  • 143