This topic describes how to configure policies that are used to delete binary log files from an ApsaraDB RDS for MySQL instance. Before you configure the policies, you must understand the rules based on which MySQL generates and deletes binary log files.
- Rules to generate binary log files
Note If your RDS instance runs MySQL 5.7 or 8.0 on RDS Enterprise Edition, it always writes data to a new binary log file when the size of the file reaches 500 MB.
- In most cases, after the size of a binary log file reaches 500 MB, your RDS instance writes data into a new binary log file. This means that in theory every binary log file is 500 MB in size. When your RDS instance continues to write data into the new binary log file, the old binary log file is asynchronously uploaded to Object Storage Service (OSS).
- Your RDS instance may stop writing data into a binary log file before the size of the file reaches 500 MB. For example, this occurs during command execution or system restart.
- Your RDS instance may continue to write data into a binary log file even after the size of the file reaches 500 MB. For example, this occurs if your RDS instance runs large transactions.
- Rules to delete binary log files
- By default, your RDS instance stores the binary log files that are generated over the last 18 hours.
- If the storage usage does not exceed 80% of the purchased storage capacity, your RDS instance does not delete the binary log files that are uploaded to OSS. Your RDS instance can store binary log files that occupy up to 30% of the purchased storage capacity.
- If the storage usage exceeds 80% of the purchased storage capacity, your RDS instance initiates requests to delete binary log files that are uploaded to OSS. The deletion of binary log files needs to be scheduled, which causes a certain amount of delay.
- If the storage usage for binary log files exceeds the specified limit but your RDS
instance does not initiate requests to delete them, your database client may encounter
errors or the tasks that are scheduled in Data Transmission Service (DTS) are blocked.
In these cases, check whether the following error is reported:
[Warning] file /home/mysql/data3001/mysql/mysql-bin.069435 was not purged because it was being readby thread number 17126285
You can terminate the blocked tasks based on the error information.
- Log on to the ApsaraDB RDS console.
- In the top navigation bar, select the region where your RDS instance resides.
- In the left-side navigation pane, click Backup and Restoration to open the Backup and Restoration page.
- Click the Local Log Settings tab.
- Click Edit. In the Local Binary Log Settings dialog box, configure the following parameters.
Parameter Description Retention Period The retention period of binary log files. Valid values: 0 to 168. Default value: 18. Unit: hours. For example, the value 18 allows ApsaraDB RDS to store binary log files that are generated over the last 18 hours. If you enable the log backup function, your RDS instance backs up the binary log files that are stored longer than 18 hours. Then, your RDS instance deletes these files. Max Storage Usage The maximum storage usage that is allowed for binary log files. Valid values: 0 to 50. Default value: 30. Unit: %. For example, the value 30 allows ApsaraDB RDS to delete earlier binary log files when the storage usage exceeds 30%. Retained Binlogs The number of binary log files that can be retained. Valid values: 6 to 100. Default value: 60. If the number of binary log files that are generated exceeds the value of this parameter, your RDS instance deletes earlier binary log files. Protect Available Storage Specifies whether to forcibly delete earlier binary log files. This occurs if the total storage usage exceeds 80% or the available storage space is less than 5 GB. This function is enabled by default.
- Click OK.