Mysql – Automatically purging binary logs

MySQL

I'm currently trying to create an scheduled event on MySQL to purge binary logs every week.

The create event statement is as follows:

CREATE EVENT purgebinlogs
ON SCHEDULE EVERY 1 WEEK
STARTS CONCAT(CURRENT_DATE + INTERVAL 7 - WEEKDAY(CURRENT_DATE) DAY,' 01:00:00')
DO
PURGE BINARY LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 7 DAY);

It should run every monday at 01:00. However if i query mysql.event table i get the following output:

mysql> select * from mysql.event\G
*************************** 1. row ***************************
                  db: mysql
                name: purgebinlogs
                body: PURGE BINARY LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 7 DAY)
             definer: root@localhost
          execute_at: NULL
      interval_value: 7
      interval_field: WEEK
             created: 2015-03-13 17:45:20
            modified: 2015-03-13 17:51:43
       last_executed: NULL
              starts: 2015-03-16 07:00:00
                ends: NULL
              status: ENABLED
       on_completion: DROP
            sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
             comment:
          originator: 1
           time_zone: SYSTEM
character_set_client: cp850
collation_connection: cp850_general_ci
        db_collation: utf8_general_ci
           body_utf8: PURGE BINARY LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 7 DAY)

As you can see, the starts column says:

starts: 2015-03-16 07:00:00

The date is OK, but the time… It should run at 01:00 not 07:00

I think this could be a timezone issue, cause my timezone is "-06:00"

mysql> show global variables like 'time_z%';
+------------------+----------+
| Variable_name    | Value    |
+------------------+----------+
| time_zone        | -06:00   |
+------------------+----------+
2 rows in set, 1 warning (0.01 sec)

mysql> SELECT TIMEDIFF(NOW(), UTC_TIMESTAMP);
+--------------------------------+
| TIMEDIFF(NOW(), UTC_TIMESTAMP) |
+--------------------------------+
| -06:00:00                      |
+--------------------------------+
1 row in set (0.00 sec)

But i can't guess how to fix this. I want to run the scheduled event at 01:00

EDIT

when querying information_schema.events it displays the correct information

mysql> select * from information_schema.events\G
*************************** 1. row ***************************
       EVENT_CATALOG: def
        EVENT_SCHEMA: mysql
          EVENT_NAME: purgebinlogs
             DEFINER: root@localhost
           TIME_ZONE: SYSTEM
          EVENT_BODY: SQL
    EVENT_DEFINITION: PURGE BINARY LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 7 DAY)
          EVENT_TYPE: RECURRING
          EXECUTE_AT: NULL
      INTERVAL_VALUE: 1
      INTERVAL_FIELD: WEEK
            SQL_MODE: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
              STARTS: 2015-03-16 01:00:00
                ENDS: NULL
              STATUS: ENABLED
       ON_COMPLETION: NOT PRESERVE
             CREATED: 2015-03-13 17:45:20
        LAST_ALTERED: 2015-03-13 17:56:32
       LAST_EXECUTED: NULL
       EVENT_COMMENT:
          ORIGINATOR: 1
CHARACTER_SET_CLIENT: cp850
COLLATION_CONNECTION: cp850_general_ci
  DATABASE_COLLATION: utf8_general_ci

So I guess that mysql.event displays information in UTC Time not in the local TimeZone

EDIT

As suggested by @Rick James i changed dynamically the value of expire_logs_days with the following command:

set global expire_logs_days=7

But it seems it's not working….

mysql> show global variables like 'expire%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 7     |
+------------------+-------+

However, if i list the binlog files i get the following list:

 Mar  5 19:30 mysql56_bin.000001
 Mar  5 19:30 mysql56_bin.000002
 Mar 10 13:53 mysql56_bin.000003
 Mar 11 11:59 mysql56_bin.000004
 Mar 11 17:44 mysql56_bin.000005
 Mar 12 12:53 mysql56_bin.000006
 Mar 12 12:58 mysql56_bin.000007
 Mar 12 13:02 mysql56_bin.000008
 Mar 12 13:07 mysql56_bin.000009
 Mar 12 13:12 mysql56_bin.000010
 Mar 12 13:16 mysql56_bin.000011
 Mar 12 13:20 mysql56_bin.000012
 Mar 12 13:24 mysql56_bin.000013
 Mar 17 10:38 mysql56_bin.000014
 Mar 17 18:27 mysql56_bin.000015
 Mar 17 18:28 mysql56_bin.000016
 Mar 17 18:28 mysql56_bin.index

As you can see there are binlogs older than a week ago.

Best Answer

Seems like expire_logs_days 7 would be a lot simpler.

8.0 is moving toward binlog_expire_logs_seconds.