Automatically purging binary logs

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) |
+--------------------------------+
| -

For MySQL 8 there is another option: binlog_expire_logs_seconds - unfortunately I don't find an entry in the documentation. Only this task entry.

Add this setting to the /etc/my.cnf file [Linux version].

Also, expire_logs_days is deprecated in MYSQL 8.

StackExchange Database Administrators Q#95253, answer score: 2