Mysql – How soon after updating expire_logs_days param and restarting sql will old binlogs get deleted

binlogMySQLmysql-5mysql-5.1replication

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?

Best Answer

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.

Related Question