11

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.

Improve this question
2
  • Please run these two commands: 1) head -1 mysql56_bin.index 2) tail -1 mysql56_bin.index. What is output from each command ?
    – RolandoMySQLDBA
    Mar 18, 2015 at 16:54
  • @RolandoMySQLDBA here's the output: -bash-4.1$ head -1 mysql56_bin.index ./mysql56_bin.000001 -bash-4.1$ tail -1 mysql56_bin.index ./mysql56_bin.000016
    – kriegu
    Mar 18, 2015 at 23:00

7 Answers 7

Reset to default
13

Seems like expire_logs_days 7 would be a lot simpler.

8.0 is moving toward binlog_expire_logs_seconds.

Improve this answer
1
  • Absolutely! I didn't know that variable!
    – kriegu
    Mar 14, 2015 at 1:13
2

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.

Improve this answer
2

I have yet another method for automatically purging logs on master based on what a slave has replicated.

Disclaimer: I haven't had this solution in production for very long, so proceed at your own risk

The following shell script should be run on the master server. It assumes that the slave can be accessed via SSH. You could easily adapt this to other scenarios.

#!/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 STATUS\G' | 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

I run this script on a daily cron schedule to ensure logs are purged regularly.

Improve this answer
2

Try with global variable

SHOW VARIABLES LIKE 'expire_logs_days';

SET GLOBAL expire_logs_days=7;

enter image description here

For more detail read here

Improve this answer
1
  • mysql 8 gives this exception: "The option expire_logs_days and binlog_expire_logs_seconds cannot be used together. Please use binlog_expire_logs_seconds to set the expire time (expire_logs_days is deprecated)"
    – habib
    Dec 10, 2022 at 9:25
1

Hi I've made a script that cleanup binary logs on mysql master/slave replication.

#!/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

This way I always have cca. 50 binary logs . I have this on slave server in crontab, and run it every few hours .

Cheers

Improve this answer
4
  • Nice! Do you check if those deleted binlogs have already been applied on the slave?
    – kriegu
    Nov 3, 2017 at 13:12
  • First command : masterfile=.... , checks current working binary file; than I extract file number and subtract number 50 (to leave last 50 binary logs) and delete rest.
    – Miro
    Nov 6, 2017 at 9:04
  • Why not use "Master_Log_File" instead of "Relay_Master_Log_File" from the SHOW SLAVE? (Master_Log_File is the current reading: dev.mysql.com/doc/refman/5.7/en/show-slave-status.html) If your sql_thread is "stuck" with some query and is working in "older" binlogs; you could end up with more than 50. Not the worst but could help
    – Mauricio Cacho
    Nov 6, 2017 at 16:45
  • you can use Master_Log_File , just be carefull, with "cut -c34-40" values ;) , you'll need to modify them. Major idea was, I don't want to forget about "purge-ing" binary logs , because database-es are really-really big, and binary logs files are increasing really fast.
    – Miro
    Nov 7, 2017 at 16:00
-1

setting binlog_expire_logs_seconds would be a lot simpler

show global variables like 'binlog_expire_logs_seconds'; SET GLOBAL binlog_expire_logs_seconds = 604800;

Improve this answer
-2

'expire_logs_days' is now depereciated in Mysql8 Instead please use 'binlog_expire_logs_seconds'.

Depends on your requirement but in general use below statement(change seconds value as per your application needs): SET GLOBAL binlog_expire_logs_seconds=3600;

Improve this answer
1
  • This isn't adding anything new to existing answers.
    – mustaccio
    Feb 17, 2021 at 21:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.