Understanding MySQL Binlog and Relaylog: Viewing Events, Formats, and Replication Mechanics

Introduction

After learning the basics of MySQL master‑slave replication, this chapter dives into the binlog and relaylog, explaining how to view their events and understand the replication workflow.

1. Viewing Binlog and Relaylog

Use the following MySQL commands to list and inspect binlog files: mysql> show binary logs; To see the latest binlog position: mysql> show master status; To display binlog events: mysql> show binlog events; Relaylog events can be inspected with: mysql> show relaylog events; Relaylog parameters are queried via:

mysql> show variables like '%relay%';

2. Relaylog‑Info and Slave‑Master‑Info

Relaylog‑info and master‑info can be stored as FILE, TABLE, or DUMMY. The storage mode is controlled by relay_log_info_repository and master_info_repository. Important parameters include max_relay_log_size, relay_log, relay_log_index, relay_log_info_file, relay_log_purge, relay_log_recovery, relay_log_space_limit, sync_relay_log, and sync_relay_log_info.

3. Binlog File Format Parsing

The binary format can be examined with hexdump -C mysql-bin.000024 or mysqlbinlog --base64-output='decode-rows' mysql-bin.000024. Example hexdump output shows the magic header 0xFE 'bin', timestamp, event type, server ID, event size, log position, flags, and other fields.

00000000  fe 62 69 6e 41 93 92 5f  0f 0a 00 00 00 79 00 00  |.binA.._.....y..|
00000010  00 7d 00 00 00 01 00 04  00 38 2e 30 2e 32 30 2d  |.}.......8.0.20-|
...

The event‑type header length (41 in MySQL 8.0.20) corresponds to the enum Log_event_type defined in binlog_event.h, which lists all possible replication events.

enum Log_event_type {
  UNKNOWN_EVENT = 0,
  START_EVENT_V3 = 1,
  QUERY_EVENT = 2,
  STOP_EVENT = 3,
  ROTATE_EVENT = 4,
  ...
  FORMAT_DESCRIPTION_EVENT = 15,
  XID_EVENT = 16,
  TABLE_MAP_EVENT = 19,
  WRITE_ROWS_EVENT = 30,
  UPDATE_ROWS_EVENT = 31,
  DELETE_ROWS_EVENT = 32,
  GTID_LOG_EVENT = 33,
  PREVIOUS_GTIDS_LOG_EVENT = 35,
  ...
  ENUM_END_EVENT
};

4. MySQL Master‑Slave Synchronization Source Code

The replication thread receives the COM_BINLOG_DUMP command and invokes com_binlog_dump, which calls mysql_binlog_send to transmit binlog data.

void mysql_binlog_send(THD *thd, char *log_ident, my_off_t pos,
                       Gtid_set *slave_gtid_executed, uint32 flags) {
  Binlog_sender sender(thd, log_ident, pos, slave_gtid_executed, flags);
  sender.run();
}

During sending, Binlog_sender::check_event_type validates events such as ANONYMOUS_GTID_LOG_EVENT and GTID_LOG_EVENT, handling GTID mode mismatches.

bool Binlog_sender::check_event_type(Log_event_type type,
                                     const char *log_file, my_off_t log_pos) {
  if (type == binary_log::ANONYMOUS_GTID_LOG_EVENT) {
    if (m_using_gtid_protocol) {
      // error handling for anonymous GTID with auto‑position
    }
    else if (get_gtid_mode_from_copy(GTID_MODE_LOCK_NONE) == GTID_MODE_ON) {
      // error handling for anonymous GTID with GTID_MODE=ON
    }
  } else if (type == binary_log::GTID_LOG_EVENT) {
    if (get_gtid_mode_from_copy(GTID_MODE_LOCK_NONE) == GTID_MODE_OFF) {
      // error handling for GTID event with GTID_MODE=OFF
    }
  }
  return false;
}

Writing to binlog uses Log_event::write (declared in log_event.h). Master‑info and relay‑log‑info are flushed via functions like Master_info::write_info and Relay_log_info::write_info, as shown in typical LLDB backtraces.

(lldb) bt
* thread #36, stop reason = breakpoint 8.1
  * frame #0: 0x0000000109a10c80 mysqld`Master_info::write_info(this=0x..., to=0x...) at rpl_mi.cc:666
  ...
(lldb) bt
* thread #41, stop reason = breakpoint 2.1
  * frame #0: 0x000000010c36990 mysqld`Relay_log_info::write_info(this=0x..., to=0x...) at rpl_rli.cc:2200
  ...

Typical debugging breakpoints for master and slave sides include:

Sending binlog: b mysql_binlog_send Sending binlog packet: b Binlog_sender::send_packet Reading event header on slave: b Binlog_event_data_istream::read_event_header Applying relay log event:

b exec_relay_log_event

Conclusion

Use show binary logs; to list binlog files.

Use show relaylog events; to view relaylog events.

MySQL 8.0.20 defines 41 event types; MySQL 5.1 defines 39.

Row‑based replication primarily uses TABLE_MAP_EVENT, WRITE_ROWS_EVENT / UPDATE_ROWS_EVENT / DELETE_ROWS_EVENT, and ROWS_QUERY_EVENT.

Relaylog‑info and master‑info can be stored as files or tables.