Why MySQL Relay Log Settings Cause Duplicate Key Errors and How to Fix Them

Background

MySQL provides expire_logs_days to control how long binary logs (binlogs) are retained. Proper binlog cleanup is essential for storage management and for ensuring that replica servers have the necessary logs to stay in sync with the primary.

SQL Thread and Relay‑Log‑Info Handling

The SQL thread reads the relay log and applies events to the replica. It records the last applied position in relay‑info.log. However, the file relay‑log.info is not written after every event; instead, a parameter (often set to write after 10,000 events) determines the write frequency. If a replica crashes before this information is flushed, the relay‑log.info file may still point to an earlier position.

When the replica restarts, it re‑executes events that were already applied, leading to duplicate‑key errors such as MySQL error 1062. Setting relay‑log‑info‑repository=TABLE forces the position to be stored in InnoDB tables, guaranteeing consistency between the database state and the log files.

I/O Thread and Master‑Info Inconsistency

The I/O thread fetches binlog events from the primary and writes them to the relay log. Its progress is recorded in master‑info.log. Unlike the SQL thread, merely setting master_info_repository=TABLE does not solve the inconsistency problem when the replica crashes.

If the I/O thread has received up to event 2 but master‑info.log still records only event 1, a crash will cause the replica to think it has only processed event 1 after restart, causing it to re‑receive event 2 and potentially duplicate writes. The error ultimately surfaces in the SQL thread.

Critical Parameter for Cleaning Relay Logs

The most important setting highlighted is the parameter that clears the current relay log, forces the SQL thread’s applied position to be reset, and then re‑fetches the relay log from the primary. This ensures that the primary’s binlog is retained (some organizations keep a month‑long binlog for delayed replication) while the replica starts from a clean state.

Additional Settings

MySQL 5.7 introduces the super_read_only system variable. When set to ON, even users with DBA privileges cannot write to the replica, providing an extra safeguard against accidental changes.