WL#9237: Add a new variable binlog_expire_logs_seconds
Affects: Server-8.0 — Status: Complete
EXECUTIVE SUMMARY ================= This worklog adds a global server variable binlog_expire_logs_seconds in addition to the existing expire_logs_days variable. The ultimate requirement is that the user can set expire periods smaller than one day by providing another extra variable. The new variable binlog_expire_logs_seconds, can be used in those cases where the expire period is not an integral multiple of days like 1 day 2 hours and 32 minute. This worklog was created to address feature request: BUG#71697 . User Stories ============ - As a MySQL DBA I want to be able to set the retention policies of binary logs to a value smaller than one day so I can lower the storage footprint of binlogs. - As a MySQL DBA I want to set the retention policy of binary logs to 1 and a half days, so I can meet two requirements for the infrastructure I maintain: (1) keep below the maximum storage quota assigned to my system; (2) keep 24 hours of binary logs always available.
Functional Requirements: ======================== F1. Introduce a user variable. NAME: binlog_expire_logs_seconds SCOPE: global TYPE: numeric RANGE: [0, 4294967295] DEFAULT: 0 COMMAND-LINE-FORMAT: --binlog_expire_logs_seconds=# DESCRIPTION: the variable is used to specify the number of seconds for automatic binary log file removal. Possible removal happens at start up and when the binary log is flushed. F2. No error SHALL be returned by MySQL when setting expire_logs_days or setting expire_log_days and binlog_expire_logs_seconds together. F3. When both binlog_expire_logs_seconds and expire_logs_days are set to '0' no auto-purge SHALL happen. F4. The purge SHALL only happen when the next binlog rotation happens, it will not happen while setting these variables, this is in accordance with the existing behavior. F5. The total time in seconds for expiring logs(the age of a binary log is calculated from the time since it was last modified) will be (binlog_expire_logs_seconds + 86400 * expire_logs_days). To set time period longer than 1 day, expire_logs_days along with binlog_expire_logs_seconds or just binlog_expire_logs_seconds > 86400 can be used. Non-Functional Requirements: ============================ NF1. Adding this new variable binlog_expire_logs_seconds SHALL NOT introduce any performance degradation.
Backwards Compatibility ======================= Users will still be able to set the old variable such as: SET GLOBAL expire_logs_days= 1; In addition to the new variable like: SET GLOBAL expire_logs_days= 1; SET GLOBAL binlog_expire_logs_seconds= 7200; Those applications that read the value of expire_logs_days, such as: SELECT @@expire_logs_days; might not give correct value when used with 8.0, because starting from 8.0 you would need to read both binlog_expire_logs_seconds and expire_logs_days to find the total value to expire log. Although those application will work fine if used with mysql-server version < 8 Notes: ===== To view the total time in HH:MM:SS for purge we can document a recipe like SELECT SEC_TO_TIME(binlog_expire_logs_seconds + 86400 * expire_logs_days)
The following low level changes are required: 1. Add a new variable binlog_expire_logs_seconds of type unsigned long. 2. Add a new Sys_var for binlog_expire_logs_seconds and set the type as Sys_var_ulong. 3. Modify the test cases that test expire_logs_days, to test binlog_expire_logs_seconds as well.
Copyright (c) 2000, 2020, Oracle Corporation and/or its affiliates. All rights reserved.