Why are binlogs on by default in MySQL 8 and will they be deleted automatically?
We recently discoverd that in MySQL 8 default configurations, binlog.* files clutter up in /var/lib/mysql on Ubuntu 20.04 machines.
i.e. we have a 4.2 GB database size and around 500 MB binlogs per day.
Because of this we run low in disk space.
Sure we can disabled and delete binlogs, but we are wondering what is the reason for this default configuration and if they every will be deleted (and we just have to increase the storage a bit on our machines).
We guess they should not be kept forever in the default config because then inifite storage would be needed.
Solution 1:
Configure binlog_expire_logs_seconds. 86400 would say to purge logs every day. I like to extend it to a week or two.
Meanwhile, check max_binlog_size. If you want the binlogs purged daily, and you are generating 5MB/day, then I suggest setting this to 100M.
Purging after a day and 100M would keep the disk usage between 500 and 600M (or maybe it is 500M and 1100M, I don't know the exact algorithm).
500M per day seems like a lot. Are you frequently Updating every row in a large table? Or something else?
A agree that the default configuration may not be the best. There are several default situations to consider, and the installation does not ask you which you want:
- Replication (needing the binlog for that). 'Expire' could have been defaulted.
- Want 'point-in-time-recovery'. Again, need binlog, but what is a good default?
- No need for binlog.
- other?
Solution 2:
Intro
This is actually an upstream change. Some distributions might change the setting back, but it is likely that most will just follow upstream.
The codebase is hosted on https://github.com/mysql/mysql-server The actual file where binary logging is made default ON, is sql/sys_vars.cc
Digging through the blames, eventually I nailed down the commit that changed the default: https://github.com/mysql/mysql-server/commit/9fa9504e5aaf68661aef2d735cecbd3c58eb7790
It mentions a Worklog Item for the mysql team: #10470. You can look them up here: https://dev.mysql.com/worklog/
The Rationale section of that Worklog item offers this:
Rationale
Nearly all production installations have the binary log enabled as it's used for replication and point-in-time recovery.
Given that, we should have it enabled by default for the following reasons:
- We eliminate one configuration step for users. 1A. Enabling it later requires a mysqld restart.
- We get more production-like internal testing of the server.
- We can better know and face the performance impact of the binary log
Expiration
In mysql 8.0 the default expiration for logfiles is 30 days, governed by the variable binlog_expire_logs_seconds, which defaults to 2592000 seconds. For a purge to actually occur, there has to be a flushing of the logs. According to the documentation a log flush automatically happens when one binary log file is closed and a new one started. The maximum size of individual files can be governed by max_binlog_size which is max 1GB. However there is a caveat that transactions are not split across log files, and they could theoretically be up to 4GB. You could also issue a flush logs or a purge binary logs statement daily yourself.
- Purge binary logs
- Expire logs
- Server logs