Issue

The connector is breaking repeatedly with the following binlog error: Could not find first log file name in binary log index file.

Environment

Connector: Aurora MySQL

Answer

The solution to prevent this error is to force the binary log files to rotate earlier by executing FLUSH BINARY LOGS;

To do this follow these steps:

The parameter event_scheduler needs to be modified and be set to ON. This replaces running SET GLOBAL event_scheduler = ON. This article explains how to modify parameter groups.

Next, run the following commands from a user that has flush privileges, e.g., root.

DROP EVENT IF EXISTS myevent;
CREATE EVENT myevent
ON SCHEDULE EVERY 24 hour DO FLUSH BINARY LOGS;

You can confirm the event was created with this command:

SHOW EVENTS;

Once completed, there should be a new binlog file created every 24 hours. This can be confirmed as shown below:

SHOW BINARY LOGS;

There should be one file per day with a size that is generally smaller than the max binlog size, as shown below:

mysql-bin.000197 7631
mysql-bin.000198 7631
mysql-bin.000199 7631
mysql-bin.000200 7631

Cause

It is likely that one of the following issues caused the error:

1. The binary log file has a maximum of 134MB.
2. There are very little changes done to the database to fill up 134MB in less than the time Auroras default settings rotate the file.
3. The file is rotated without keeping the old one.
4. We lose track of where the files were located.

You can check how currently there is only one file with the statement SHOW BINARY LOGS;