15

MySQL 5.1.x | InnoDB | Windows

My mysql data directory is starting to fill up with bin logs.

I currently have the following settings configured in my windows mysql server:

[mysqld]
log-bin
server-id=1
binlog-do-db=foodb1
binlog-do-db=foodb2
expire_logs_days=25

I'm planning to change the expire_logs_days setting to expire_logs_days=10 and bounce the mysql service. How soon after making this change can I expect the old bin logs to get clear out.

Is this only done as part of a nightly scheduled task? Or should this be immediate?

28

It will be handled immediately on mysql startup.

You do not have to wait for a mysql restart.

First, set expire_logs_days to be 10 in /etc/my.cnf

[mysqld]
log-bin
server-id=1
binlog-do-db=foodb1
binlog-do-db=foodb2
expire_logs_days=10

Next, log in to mysql and run this

PURGE BINARY LOGS BEFORE (date(now()) + interval 0 second - interval 10 day);

Note the query's date and time

mysql> select date(now()) + interval 0 second - interval 10 day;
+---------------------------------------------------+
| date(now()) + interval 0 second - interval 10 day |
+---------------------------------------------------+
| 2012-12-11 00:00:00                               |
+---------------------------------------------------+
1 row in set (0.00 sec)

mysql>

Thus, the PURGE BINARY LOGS command will delete all binlogs whose datetime stamp predates 2012-12-11 00:00:00.

Finally, run this command

SET GLOBAL expire_logs_days = 10;

That's it. No restart needed for installing expire_logs_days.

As for the replication parameters, yes restart is required.

  • Brilliant response. Thank you. I appreciate the detail. – Mike B Dec 22 '12 at 0:53
  • Please refresh, I corrected a typo. – RolandoMySQLDBA Dec 22 '12 at 0:55
  • 1
    Just to note that expire_logs_days is now deprecated in MySQL 8.0 (per the detail in the link given by @RolandoMySQLDBA). Use binlog_expire_logs_seconds instead going forward. – John Rix May 3 '18 at 10:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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