Analysis and Reproduction of MySQL Binlog Replication Error 1236 (Could Not Open Log File)

1. Fault Description

The client reported replication error 1236, indicating that the slave could not read a binlog file that does not exist on the master, causing replication to stop.

2. Fault Analysis

Testing was performed on a MySQL 5.7.31 environment with GTID enabled. The slave’s I/O thread reported a fatal error 1236 and stopped at GTID c582a82e-b985-11ec-adf5-02000aba3e89:828415. Examination of the master’s binlog index revealed two entries for mysql-bin.000006 while only one actual file existed, indicating a missing binlog.

Further inspection showed that mysql-bin.000005 ended with GTID c582a82e-b985-11ec-adf5-02000aba3e89:828415, whereas the first transaction in mysql-bin.000006 started at GTID c582a82e-b985-11ec-adf5-02000aba3e89:855706, a gap of over 20,000 GTIDs, confirming transaction loss.

3. Fault Reproduction

3.1 Reproducing Duplicate Index Entries

In a controlled environment, the active binlog on the master was manually removed (rm) and FLUSH LOGS was executed. The master regenerated the missing binlog, but the mysql-bin.index file now contained two identical entries for the removed binlog, matching the client’s situation.

3.2 Reproducing the 1236 Error

With a replication lag, the master’s next binlog ( mysql-bin.000007) was manually removed while still being held open by the server. The slave successfully read mysql-bin.000006 but failed when attempting to read the now‑missing mysql-bin.000007, resulting in error 1236.

3.3 Other Scenarios

Additional cases were observed, such as the slave losing GTID continuity without error when the master regenerated the missing binlog, and the slave reporting error 1236 after the master flushed a new binlog that started with a low event number.

4. Recommendations

To avoid similar replication failures:

Avoid direct deletion or compression of active binlog files; instead adjust MySQL’s binlog retention parameters.

When manual binlog cleanup is necessary, use the PURGE BINARY LOGS command within MySQL.