Question
Why has my connector required a full re-sync about once every week due to binary log failures?
Failure With Task: Could not find first log file name in binary log index
file
Environment
Connector: Aurora MySQL
Answer
Increase your binary log retention period to 168 hours or greater.
Cause
When Aurora MySQL database has low activity, Aurora will delete old binary log files based on their creation date and not the latest event. The oldest file is deleted immediately during the rotation of the binary log files. This occurs even though events at the end of the binary log file are from the last few hours or fewer.
You can test this by looking at the output of SHOW BINARY LOGS in your source DB. You need to always have at least two binary log files present so that we can survive rotation that happens between sync cycles.