Understanding MySQL slave_relay_log_info, Relay Log Recovery, and Master Auto Position

The mysql.slave_relay_log_info table stores the working position of the slave SQL thread. A typical query shows fields such as Number_of_lines , Relay_log_name , Relay_log_pos , Master_log_name , Master_log_pos , and others.

When a replica starts, it reads the persisted position from this table and uses the values to fill the Relay_Log_File and Relay_Log_Pos fields shown by SHOW SLAVE STATUS , allowing the next START SLAVE to continue replaying the relay log from that point.

The table holds a durable state, while SHOW SLAVE STATUS reflects the current in‑memory state; therefore the two outputs may differ. Stopping the slave or shutting down mysqld flushes the in‑memory state to the table, and on startup the persisted state is loaded back into memory.

The slave I/O thread reads the master’s binlog using the Master_Log_File and Read_Master_Log_Pos stored in slave_master_info , writes it to the local relay log, and the slave SQL thread replays the relay log using the Relay_Log_Name and Relay_Log_Pos values.

Because a transaction’s position in the relay log does not match its position in the master binlog, slave_relay_log_info records the mapping via the Master_log_name and Master_log_pos columns.

Accurate I/O thread positioning is crucial; any duplication or omission in reading the master binlog will be reflected in the SQL thread’s replay.

Before MySQL 5.6, replication coordinates were stored only in the master.info file, updated periodically (default every 10,000 transactions) and not fully crash‑safe. The sync_relay_log_info variable controlled how often the file was flushed.

Starting with MySQL 5.6, the option --relay-log-info-repository=TABLE can store the slave SQL thread’s position in the mysql.slave_relay_log_info table. When the table uses a transactional engine (e.g., InnoDB), it is updated after each transaction, guaranteeing consistency.

Update mechanism examples:

sync_relay_log_info = 0

If the repository is FILE , the server relies on the OS to flush the file; if TABLE and the engine is transactional, the table is updated after each transaction (the setting is ignored). If the engine is non‑transactional, the table is never updated.

sync_relay_log_info = N > 0

With FILE , the relay‑log.info file is flushed after every N transactions; with TABLE and a transactional engine, the table is still updated after each transaction.

Operational best practice is to set relay_log_info_repository=TABLE . Although the default sync_relay_log_info=10000 would normally delay updates, using a transactional table causes an update on every transaction, ensuring durability when combined with innodb_flush_log_at_trx_commit=1 .

For the master side, setting master_info_repository=TABLE and sync_master_info=1 provides per‑event durability but incurs significant write amplification.

The master_auto_position feature lets a replica locate the correct master binlog position based on its executed GTID set, but the slave still relies on slave_relay_log_info and slave_master_info for the actual file/position values, especially after a reset.

When relay_log_recovery is enabled, the server creates a new relay log on startup, updates the Relay_Log_Name to the latest file and sets Relay_Log_Pos to a fixed offset (4). The in‑memory state shown by SHOW SLAVE STATUS is also updated, and the I/O thread’s master position fields are refreshed from the slave_relay_log_info table.

The mapping between relay log and master binlog positions is maintained: Relay_log_name ↔ Master_log_name and Relay_log_pos ↔ Master_log_pos always correspond to the same transaction.

Conclusions: With GTID and master_auto_position enabled, setting relay_log_recovery=1 ensures consistency even if the slave_relay_log_info table is stale, because duplicate transactions are skipped based on GTID. Without GTID, both relay_log_info_repository=TABLE and relay_log_recovery=1 are required for crash‑safe replication.