3

I am using 5.7.37-log MySQL Community Server. my mysqld.cnf looks below:

server_id = 11
log_bin = bin.log
log-bin-index = bin-log.index
binlog_format = row
max_binlog_size = 100M
socket = mysql.sock
expire_logs_days = 1

my expectation with expire_logs_days = 1 is that after 1 day the log will be purged. and i wont be able to see the older logs.. but when i check logs after a week, i can still see bin.000001, when the current log name is bin.000006. and I observed the old logs are getting purged when I restart mysql server and a new log is being created.

But I dont want to restart the server to get my old files deleted. they should be deleted automatically. and i fear about filling up disk space when i accumulate older logs (i dont need them for sure) and i dont want to restart sql server for this purpose.

I also had seen in some forms suggesting to run PURGE BINARY LOGS BEFORE NOW() - INTERVAL 1 DAY; But I dont understand the point in manually running above statement when we already set the config.

Please help me if my understanding and expectations were correct or not. and how do i test or make sure that the logs are getting deleted automatically ? and no harm to disk space.

attaching screenshot of my generated bin logs.

enter image description here

Here is the screenshot after 24 hours (a day) still bin.000003 is there

2
  • See if when a binlog is _ created after_ Apr 25 16:18, 000003 goes away.
    – Rick James
    Apr 25, 2023 at 15:53
  • If you are worried about disk space, lower max_binlog_size.
    – Rick James
    Apr 25, 2023 at 15:54

2 Answers 2

4

One tricky thing about binlog expiration you have to keep in mind is that it doesn't happen until the MySQL Server opens a new binlog file. So as long as it is currently writing to bin.000006, the oldest files won't be expired. When it rolls over to bin.000007, then MySQL Server checks to see which (if any) older binlog files need to be expired.

So in theory, if your rate of writes to bin.000006 is slow enough, it takes many days to fill up and roll over to bin.000007, so your old binlog files would hang around for many days.

You don't have to restart MySQL Server to force it to open a new binlog, and you don't have to wait until the binlog file fills up to its maximum size. You can run FLUSH LOGS; any time. This will close open log files, including the binary log, and open them again. In the case of binary logs, it also opens a new file, even if the last file is not full yet.

It's also possible that you could have a surge of writes in one day, so many new binlog files are rapidly opened and filled, and yet the oldest binlog file is still less than the 1 day old, so it's not expired. Thus you can still fill up disk space before expiring older files.

What's missing from MySQL is an expiration based on total space of binlogs, instead of expiration based on time.

Percona Server is a branch of MySQL. In version 8.0, they introduced an option binlog_space_limit which sets an absolute cap on the total storage used by binlogs. See:

2
  • your explaination cleared the clouds. and i can also sense it kind of tricky to just consider the property "expire_logs_days" alone. so it works in conjuction with other property "max_binlog_size". old logs will be removed only when these both conditions met. i am trying to make this happen automatically, so my options are 1. reduce the max_binlog_size to somewhat close to my daily chages size 2. automate a script to run 'Flush logs' daily. Please let me know if my understanding is correct or if there is any other option to make this automated..
    – santhosh
    Apr 25, 2023 at 19:56
  • 1
    I think your understanding is correct. It would be more reliable to FLUSH LOGS daily, in case you can't predict exactly what your daily log size is. Apr 25, 2023 at 19:59
2

Here is something your must realize about binary logs expiring

Back on July 13 2017, I answered MySQL Still purging binary logs and ignoring expire_logs_days. I had mentioned how MySQL 5.0.x used to delete binary logs up to midnight.

Since then, expire_logs_days operates on seconds granularity for 5.7.

Now, MySQL 8.0 has more variables to fine tune binary log purging

However, there is something else you must realize

  • binlog.00006 has the timestamp Apr 25 09:12
  • binlog.00003 has the timestamp Apr 24 16:08

So why did binlog.00003 not disappear yet ???

24 hours before Apr 25 09:12 (binlog.00006 timestamp) is Apr 24 09:12. If the UNIX timestamp for Apr 24 09:12 is embedded in binlog.00003, then that binlog must remain in place.

Once you have binlog.00006 reach the UNIX timestamp for Apr 25 16:08, then binlog.00003 will disappear the next time mysqld rotates from binlog.00006 to binlog.00007 or if you manually execute FLUSH BINARY LOGS; at Apr 25 16:09.

If you want to see the UNIX timestamps inside binlog.00003 just run

mysqlbinlog binlog.00003 > binlog_events.txt
vi -R binlog_events.txt

and see the timestamps (both UNIX Timestamp value and human readable)

4
  • Hi @rolando. Thank you for your answer. So I waited till the 16:08 for the bin.000003 to disappear, but somehow it still there. Please find current screenshot added to the main question
    – santhosh
    Apr 25, 2023 at 16:14
  • 1
    I did look at the screenshot. It will not disappear for the reason I just said in my answer. The timestamp 24 hours ago is still inside bin.000003. The file timestamp indicates the last time it was written but the timestamp 24 hours ago is PHYSICALLY INSIDE the binlog. Run the mysqlbinlog command to see the contents of the binlog. Apr 25, 2023 at 17:15
  • 1
    Wait for 000007 to be started.
    – Rick James
    Apr 25, 2023 at 17:54
  • 1
    I said that in my answer as well (Once you have binlog.00006 reach the UNIX timestamp for Apr 25 16:08, then binlog.00003 will disappear the next time mysqld rotates from binlog.00006 to binlog.00007 or if you manually execute FLUSH BINARY LOGS; at Apr 25 16:09.) Apr 25, 2023 at 18:00

Your Answer

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

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