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.eventG
*************************** 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.eventsG
*************************** 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.

SHOW VARIABLES LIKE 'expire_logs_days';

SET GLOBAL expire_logs_days=7;

#!/bin/bash

# This script assumes you have configured mysql to run without asking for username/password
# on both the master and slave hosts. (e.g. by putting credentials in ~/.my.cnf)

# Replace 'replication-server' with the hostname of your slave server.
SECONDS=$(echo 'SHOW SLAVE STATUSG' | ssh replication-server mysql | grep Seconds_Behind_Master | grep -o '[0-9]+' )

# This performs the actual purge on the Master DB
# It calculates what date the slave has replicated to, and then subtracts an additional 1 week
# buffer just in case.
echo "PURGE BINARY LOGS BEFORE DATE(NOW() - INTERVAL 1 WEEK - INTERVAL $SECONDS SECOND)" | mysql

#!/bin/bash

logfile=/tmp/mysqlscript.log

## Get current binary log replication file on slave
masterfile=$(mysql --defaults-extra-file=/opt/script/credentials-local.cnf -e "show slave status G" | grep Relay_Master_Log_File:)
## echo $masterfile >> $logfile

## Get number from from replication file
masternumber="$(echo $masterfile | cut -c34-40)"
## echo $masternumber >> $logfile

## Convert number to decimal value
masternumdecimal=$((10#$masternumber))
## echo $masternumdecimal >> $logfile

## Decrease replication file number to left 50 replication files on master
masternumfile=$(($masternumdecimal - 50))
## echo $masternumfile >> $logfile

## Convert decreased number to 6 digits number
printf -v purgenum "%06d" $masternumfile
## echo $purgenum >> $logfile

## Create binary log purge file name
purgefile=mysql-bin.$purgenum
## echo $purgefile

## Purge binary logs to purge file name on master
mysql --defaults-extra-file=/opt/script/credentials-master.cnf -e "purge binary logs to '$purgefile';"
echo Purged binary logs on master to $purgefile >> logfile