Binlog retention in MySQL
Last updated
Was this helpful?
Last updated
Was this helpful?
The binary log keeps track of all modifications made to the databases' data and structure. It comprises an index and a collection of binary log files. Binlog retention is the period for which a binary log is retained by MySQL, after that period the logs are deleted and are inaccessible.
The engine calculates the difference between the last committed event and the current time. This delay will increase if we didn't get any event in the data source, or we ingest events slower than the speed they are generated. Some possible causes might be:
There are no new events in the tracked tables
The cdc engine fails to start
The connection to the database is slow or throttled
To read CDC in MySQL Upsolver reads the binary log and parses the events.
If we experience delays we keep trying to read the older binlog files we were not able to read before. If the binlog files that the system is trying to read are deleted by MySQL (due to the binlog retention setting), the CDC input will become stuck and won't be able to advance and read further messages. The missing files can't be skipped since it will lead to an inconsistant state with missing, duplicate, and/or invalid events. The only way to recover from this situation is to re-create the Data Source.
Because of this ensuring the binlog delay doesn't exceed the binlog retention is essential for the stability and health of the Data Source and any outputs that depend on it.
Log in to the source database and run the following SQL statement to set the retention period of binlog:
The value n indicates an integer from 1 to 168 (7 days).
Our support team is available to assist with troubleshooting in case of delays.
Connect your monitoring system to Upsolver via the page.
Use the upsolver.binlog-delay to monitor the binlog delay of your CDC Data Sources.