大网数据广告活动
服务器运维问答

Why are binlogs on by default in MySQL 8 and will they be deleted automatically?

· 52CE 我爱测

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…

问题原文

原帖链接 · Alex · CC BY-SA 4.0

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.

社区高分解答

原帖链接 · Gerrit · CC BY-SA 4.0

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:

  1. We eliminate one configuration step for users. 1A. Enabling it later requires a mysqld restart.
  2. We get more production-like internal testing of the server.
  3. 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.

摘自公开归档,保留原文语言,版式经整理,外部媒体与邮箱地址已移除。

资料来源与适用范围

资料来源
Server Fault
适用范围
以原帖系统、软件版本与发布日期为准(2021-11-19)
内容语言
中文;命令、路径和错误标识保留技术原文
本地收录日期
2026-11-15
授权说明
CC BY-SA 4.0

正文与所需资源由本站本地存储。查看来源及更新记录 ↗

请按适用版本和实际日志确认原因;本站没有对每一种系统或设备逐项执行文中的操作。