mysql bin logs expiry to not to fill up disk space

Question

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.

Here is the screenshot after 24 hours (a day)

Answer 1

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:

• https://www.percona.com/blog/percona-server-for-mysql-highlights-binlog_space_limit/
• https://docs.percona.com/percona-server/8.0/flexibility/binlog_space.html

Answer 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

• expire_logs_days to be deprecated
• binlog_expire_logs_seconds
• binlog_expire_logs_auto_purge

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)