Understanding the Source and Disorder of MySQL Binlog Timestamps

In daily MySQL operations you may notice that timestamps in the binary log are not always ordered; this article explains why.

Each binlog event inherits from Log_event, and its header time is set from the THD thread's start_time in the constructor. The THD::set_time() function initializes start_time either from a user‑provided user_time or from the current microsecond time.

The function is invoked before executing a SQL statement, when the session variable timestamp is set, and during slave replay of certain events.

Because the event time reflects the start of the statement, a long‑running transaction can be followed by shorter transactions whose events have later timestamps, causing apparent disorder in the binlog.

When a transaction triggers a binlog rotate (leader thread commits and file size exceeds the limit), the rotate and the first events of the new file have their header time initially zero; Log_event::get_time() then fills it with the current thread’s start_time, which may differ from earlier events.

An experiment with two concurrent sessions demonstrates both out‑of‑order timestamps and the effect of rotate on event times.

The article concludes that binlog timestamps originate from the thread’s start time and that they may appear unordered due to overlapping transaction execution and rotate handling.