WL#9237: Add a new variable binlog_expire_logs_seconds

Affects: Server-8.0   —   Status: Complete   —   Priority: Medium

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

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 

3. Modify the test cases that test expire_logs_days, to test 
   binlog_expire_logs_seconds as well.