During cold backup restoration or database restarts, MySQL replication may fail to start with errors related to relay logs. This issue can also occur after changing the hostname since relay log filenames are often based on the host identifier.

Error Manifestation

While the database starts normally, the replication threads fail to initialize:

dba:(none)> start slave;
ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository

Relevant error log entries:

2021-07-15 14:52:06 23088 [ERROR] Failed to open the relay log './cpzz146-relay-bin.006407' (relay_log_pos 771293478).
2021-07-15 14:52:06 23088 [ERROR] Could not find target log file mentioned in relay log info in the index file './mysql-relay-bin.index' during relay log initialization.
2021-07-15 14:52:06 23088 [ERROR] Failed to initialize the master info structure

Root Cause Analysis

The error occurs because the system cannot locate the specified relay log file referenced in the index. When restoring from cold backups, metadata in the mysql.slave_relay_log_info table retains references to previous relay log files that no longer exist, causing initialization failures.

Understanding MySQL Relay Logs

In MySQL replication setups, slave servers maintain three types of log files to track binary log events and replication status:

1. Relay Log Files

These contain binary log events read by the I/O thread from the master server. The SQL thread executes these events to replicate data changes.

2. Master Info Repository

Stores connection parameters and status information for the master server. Prior to MySQL 5.6, this was stored in a master.info file. From version 5.6 onward, it can be configured to use the mysql.slave_master_info table via the --master-info-repository=TABLE setting.

3. Relay Log Info Repository

Maintains the current execution position within relay logs. Before MySQL 5.6, this used the relay-log.info file. Starting with 5.6, the --relay-log-info-repository=TABLE option enables storage in the mysql.slave_relay_log_info table, which is read during START SLAVE operations.

The transition to table-based repositories supports crash-safe replication. For reliability, these tables should use transactional storage engines. Since MySQL 5.6.6, they default to InnoDB:

ALTER TABLE mysql.slave_master_info ENGINE=InnoDB;
ALTER TABLE mysql.slave_relay_log_info ENGINE=InnoDB;

Warning: Never manually modify these system tables to avoid unexpected behavior.

Solution Implementation

The issue stems from stale replication metadata in mysql.slave_relay_log_info. Rather then manual cleanup, use MySQL's built-in RESET SLAVE command:

RESET SLAVE Operations

• Purges data from slave_master_info and slave_relay_log_info tables
• Removes existing relay log files and creates new ones
• Preserves GTID state and binary log positions

Resolution Steps

1. Reset slave configuration:

   mysql> RESET SLAVE;
   Query OK, 0 rows affected (0.00 sec)
   
   

2. Configure replication source with correct binary log coordinates:

   CHANGE MASTER TO 
   MASTER_HOST='master_ip',
   MASTER_PORT=3306,
   MASTER_USER='repl_user',
   MASTER_PASSWORD='password',
   MASTER_LOG_FILE='mysql-bin.005966',
   MASTER_LOG_POS=771293315;
   
   

   Obtain the log file and position from SHOW SLAVE STATUS output or shutdown logs.

3. Start replication:

   mysql> START SLAVE;
   Query OK, 0 rows affected (0.00 sec)
   
   

When restoring from backups, always execute RESET SLAVE before starting replication to clear obsolete metadata.

Distinguishing RESET Commands

RESET MASTER Functionality

Erases all binary log files and resets the index to begin new logs from sequence 000001. Key differences from PURGE BINARY LOGS:

• Resets log numbering sequence
• Not compatible with active slave connections

Usage Scenario: Initial master setup in new replication environments.

Critical Warning: Never use RESET MASTER on active masters with running slaves, as this breaks replication synchronization.

RESET SLAVE Functionality

Cleans up slave-side replication metadata:

• Deletes master.info and relay-log.info files
• Removes all relay logs including unprocesssed events
• Initializes fresh relay log files
• Resets master_delay to zero

Requires STOP SLAVE execution beforehand.

Usage Scenario: Reinitializing broken replication after restore operations or configuration changes.

For routine log cleanup, prefer OS-level deletion or PURGE commands. Use RESET commands specifically for replication configuration management.