1

I have to manually write

purge binary logs bin to "mysql-bin.somenumbers" ;

Once in a while, I need to reduce the spaces that binlogs are taking on my web server. Is it possible to purge binary logs bins automatically every two weeks? probably calling a bash script in crontab?

I have MySQL 5.5 running on CentOS 6.5

  • I found myself an answer. We can use mysql variable expire_logs_days to remove the bin logs automatically. – Zhenyu Jan 29 '15 at 9:12
2

I am glad you found your own answer.

I am sure you found out you can add this to my.cnf

[mysqld]
expire_logs_days = 14

You do not have to restart mysql. You login as root@localhost and run this:

mysql> SET GLOBAL expire_logs_days = 14;

To trigger the auto rotate immediately, run

mysql> FLUSH LOGS;

If you want to zap binary logs from the mysql console that is older that two weeks, use the PURGE BINARY LOGS BEFORE syntax. For example, here is a query to give you midnight two weeks ago from today's date:

SELECT DATE(NOW() - INTERVAL 2 WEEK) + INTERVAL 0 SECOND;

Just apply that query to PURGE BINARY LOGS BEFORE

PURGE BINARY LOGS BEFORE (DATE(NOW() - INTERVAL 2 WEEK) + INTERVAL 0 SECOND);
|improve this answer|||||
  • Why the "+ INTERVAL 0 SECOND"? – Rick James Jan 30 '15 at 5:15

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.