Technology Tales

Understanding and Managing MySQL Binary Log Files

11^th March 2026

When a MySQL server begins filling a disk with files named along the lines of server-bin.n or mysql-bin.00000n, where n is an incrementing number, the immediate concern is usually storage pressure. These files typically live in /var/lib/mysql and can accumulate steadily or very quickly depending on workload. While they may look obscure at first glance, they are neither random nor a sign of corruption. They are MySQL binary log files, commonly shortened to binlogs, and they exist for specific operational reasons.

What Binary Logs Are and What They Do

Understanding what these files do is the first step towards deciding whether they should remain enabled. The binary log contains all statements that update data or that potentially could have updated it, including operations such as DELETE or UPDATE where no rows were matched. Rather than being a plain text history, the log stores events describing those modifications and also includes information about how long each data-changing statement took to execute. In practical terms, the binary log forms a continuous stream of change activity on the server.

That design serves two important purposes. The first is data recovery: after a backup file has been restored, the events recorded in the binary log after that backup was made are re-executed, bringing the databases up to date from the point of the backup. The second is high availability and replication: the binary log is used on primary replication servers as a record of the statements to be sent to secondary servers. It is worth noting that it is actually the replica that pulls log data from the primary, rather than the primary pushing it outward. Without binlogs, that style of replication cannot function at all.

Before Making Any Changes

Because of those uses, any change to binary logging should be made with care. Changes to MySQL server settings and to binlog files should be made only when verified backups already exist, as mistakes can lead to data corruption or loss. If a server is important, caution matters more than convenience.

It is also worth noting that from MySQL 8.0 onwards, binary logging is enabled by default, with the log_bin system variable set to ON whether or not the --log-bin option is specified explicitly. This is a significant change from earlier versions, where binary logging had to be switched on manually. Commenting out log_bin may not switch it off in newer versions, which is consistent with this change in default behaviour and is one reason why simply commenting out that directive may not always produce the expected result.

Disabling Binary Logging

On systems where replication is not in use and point-in-time recovery is not needed, disabling binary logging may be a reasonable choice. The conventional approach is to open /etc/my.cnf or /etc/mysql/my.cnf and find the line that reads log_bin, then remove or comment it out:

#log_bin = /var/log/mysql/mysql-bin.log

The following related settings for automatic expiry and maximum log file size can also be commented out if binary logging is being disabled entirely:

#expire_logs_days = 10
#max_binlog_size  = 100M

Once the file is saved, the server needs to be restarted. On many Linux systems, that means running one of the following:

service mysql restart

or:

systemctl restart mysql

Following a restart, it is worth confirming the service came back cleanly:

systemctl status mysql

If disabling binary logging does not produce the expected result, the safest course is to check the active MySQL version and review the matching vendor documentation for that release, as configuration behaviour is not always identical across versions and distributions. In MySQL 8.0 and later, the preferred startup options for disabling binary logging are --skip-log-bin or --disable-log-bin rather than commenting out log_bin alone.

Replication and the Case for Keeping Binlogs

For servers that are using replication, disabling binary logs is not a realistic option. If the primary does not log, there is nothing for the replica to download and apply, and replication cannot proceed. In those cases, the question is not whether to keep the files but how to manage their growth sensibly, and that is where retention settings and manual purging come in.

MySQL provides built-in ways to expire old binlogs automatically. With expire_logs_days = 10 in the configuration file, logs older than ten days are purged automatically by the MySQL server, removing the need for manual intervention under normal circumstances. Automatic expiry is often the simplest route on systems where a fixed retention window is acceptable and where replication lag, or backup policies do not require logs to be kept for longer.

Purging Binary Logs Manually

Manual purging remains useful in several situations: storage may already be tight, log growth may have exceeded expectations, or a one-off clean-up may be needed after a burst of activity. For those cases, the MySQL client can issue purge commands directly. The first example removes everything before a named log file, and the second removes everything before a given date and time:

mysql -u root -p 'MyPassword' -e "PURGE BINARY LOGS TO 'mysql-bin.03';"

mysql -u root -p 'MyPassword' -e "PURGE BINARY LOGS BEFORE '2008-12-15 10:06:06';"

It is also possible to remove logs older than a specified interval using a relative expression:

mysql -u root -p -e "PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 7 DAY);"

Any purge action on a replicated environment should be timed so that replicas have already consumed the relevant logs. Purging logs that a replica still needs will break replication. A more drastic option, RESET MASTER (or RESET BINARY LOGS AND GTIDS in MySQL 8.4 and later), deletes all binary log files and resets the binary log index entirely, and should be approached with corresponding caution rather than used as a routine housekeeping measure.

Securing Database Credentials

One practical concern in the purge examples above is authentication. Placing a password directly on the command line is a security risk because command-line passwords can be exposed to other users through shell history or process inspection. A more secure alternative is to store connection details using the mysql_config_editor utility, which writes credentials to an obfuscated file named .mylogin.cnf.

On non-Windows systems, that file lives in the current user's home directory, and each group of settings within it is called a login path. A login path can store host, user, password, port and socket values. A default client login path is created with the following command, after which the password is entered at a prompt and is not echoed to the screen:

mysql_config_editor set --login-path=client --host=localhost --user=localuser --password

Additional login paths can be added for other servers. MySQL clients can then use those stored credentials like so:

mysql --login-path=client

A purge command written with a stored login path then becomes:

mysql --login-path=client -e "PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 7 DAY);"

That form is easier to automate cleanly and keeps credentials out of shell history. Client programmes still read .mylogin.cnf even when --no-defaults is used, unless --no-login-paths is specified, which gives administrators a safer way to avoid command-line passwords without giving up flexible client behaviour.

The Relationship Between mysqlcheck and Binary Logging

It is also worth understanding how table maintenance tools interact with binary logging because they can contribute to log growth in ways that are not immediately obvious. The mysqlcheck utility performs table maintenance operations, including checking, repairing, analysing and optimising tables, while the server is running. The --write-binlog option is enabled by default for ANALYZE TABLE, OPTIMIZE TABLE and REPAIR TABLE statements generated by mysqlcheck, meaning those statements are written to the binary log unless instructed otherwise.

Using --skip-write-binlog adds NO_WRITE_TO_BINLOG to those statements, preventing them from being recorded in the binary log. That can matter if maintenance operations should not be sent to replicas or replayed during recovery from backup. It is also important to note that InnoDB tables can be checked with CHECK TABLE but cannot be repaired with REPAIR TABLE, and that taking a backup before attempting any repair operation is advisable because some circumstances can result in data loss. Binary logging records change events at the server level rather than belonging to any single storage engine, so the presence of InnoDB does not in itself remove binlogs from the picture.

Managing MySQL Binary Log Files: A Practical Summary

For administrators trying to decide what to do when binlogs are consuming disk space, context is everything. A development server with no replication and no need for point-in-time recovery may be a sensible candidate for disabling binary logging. A production server participating in replication or relying on binlogs for recovery should retain them and focus instead on retention settings, controlled purging and an understanding of what workloads are causing rapid log rotation.

The practical path is to begin with the role and requirements of the server rather than with deletion. If the server does not need binary logs, use --skip-log-bin or --disable-log-bin at startup (or comment out log_bin on older versions) and then verify whether the change actually took effect for the installed version. If the server does need them, review retention settings such as expire_logs_days, use purge commands carefully when necessary and avoid exposing passwords on the command line by using mysql_config_editor and stored login paths. Where maintenance commands are involved, remember that mysqlcheck writes certain statements to the binary log by default unless --skip-write-binlog is specified. Binlog files are part of the server's change history and can be central to recovery and replication; when they begin to dominate a file system, the right response depends on what the server is expected to do and how carefully housekeeping is carried out.