High Storage Usage Due to Uncleared Old Binlogs

Scenario

The storage usage of a read replica or primary instance was high. By running SHOW BINARY LOGS or SHOW MASTER LOGS, it was found that a large number of old binlogs were not cleared.

Possible Causes

In most cases, if the binlog retention period is configured, the binlogs that have been backed up to OBS but exceed the retention period will be automatically deleted. If binlogs are not deleted for a long period of time, check whether it is caused by replication exceptions.

Troubleshooting:

1. Check error logs of the instance for any records about binlog purging failures.

   2022-01-18T05:39:03.139207+08:00 29 [Warning] file ./mysql-bin.106259 was not purged because it was being readby thread number 27490757 

2. Check whether a local standby database exists or whether tools such as Canal are used to listen to binlogs of the instance. If the primary database does not receive the information that the binlogs have been obtained by the standby database or tool, the binlogs will not be deleted. As a result, binlogs are stacked.
3. Based on the records obtained in 1, analyze the logs of the local standby database or Canal and check network connectivity to identify the cause.

Solution

• Temporarily stop the binlog listening task of the instance so that the instance can automatically clear binlogs.
• If there is a local standby database, re-establish the replication relationship.
• If a tool such as Canal is used, re-create a binlog pull task.