Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

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.

share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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