Disable MySQL Binary Log to Save Space Oct 19th 2021 Words: 311

Problem

One of the server running MySQL instance is running out of space. The monitor graph suggests that approximately 1.9GB extra space is occupied every day. By inspecting with du -a ./ | sort -n -r | head -n 10 I locked the culprit to some files under /var/lib/mysql. The files are named like mysql-bin.000001. I googled and found they are the MySQL binary log.

Binary Log Explaination

Definition:

The binary log is a set of log files that contain information about data modifications made to a MySQL server instance.

Purpose:

The binary log has two important purposes:

For replication, the binary log is used on master replication servers as a record of the statements to be sent to slave servers. Many details of binary log format and handling are specific to this purpose. The master server sends the events contained in its binary log to its slaves, which execute those events to make the same data changes that were made on the master. A slave stores events received from the master in its relay log until they can be executed. The relay log has the same format as the binary log.

Certain data recovery operations require use of the binary log. After a backup file has been restored, the events in the binary log that were recorded after the backup was made are re-executed. These events bring databases up to date from the point of the backup.

Solution

Clear Log

1
2
3
4
5
PURGE BINARY LOGS;

PURGE BINARY LOGS TO 'mysql-bin.010';

PURGE BINARY LOGS BEFORE '2019-04-02 22:46:26';

Disable Binlog

Add mysql command line parameter --skip-log-bin

OR in cnf file:

1
2
[mysqld]
disable_log_bin

Set Log Limit

1
SET GLOBAL expire_logs_days = number_of_days;

OR in cnf file:

1
2
3
[mysqld]
expire-logs-days = 10
max_binlog_size = 100M