How to Disable MySQL Binary Logging in LNMP to Free Up Disk Space

Problem Background

In a default LNMP (Linux, Nginx, MySQL, PHP) installation, MySQL typically has binary logging enabled. This feature is primarily used for database replication and point-in-time recovery. However, for single-server applications with frequent database operations that don't require these advanced features, binary logs can continuously accumulate and consume significant disk space. If your VPS or server has limited storage, these log files can quickly fill the disk, causing database errors when it cannot write new data.

Solution: Disable MySQL Binary Logging

The core steps involve modifying the MySQL configuration file, commenting out the relevant logging directives, and restarting the service.

Step 1: Edit the MySQL Configuration File

Open the main MySQL configuration file using a text editor like vim or nano. In LNMP environments, the path is usually /etc/my.cnf.

vim /etc/my.cnf

Step 2: Locate and Comment Out Logging Directives

Find the lines related to binary logging. They typically look like this:

log-bin=mysql-bin
binlog_format=mixed
# You might also see: expire_logs_days=10

Add a # character at the beginning of each line to comment them out. After modification, they should appear as:

#log-bin=mysql-bin
#binlog_format=mixed
#expire_logs_days=10

Explanation: log-bin defines the base name for binary log files; binlog_format specifies the log format; expire_logs_days sets the number of days before logs are automatically purged (if present). Commenting these lines will completely stop the generation of binary logs.

Step 3: Restart the MySQL Service

After saving and closing the configuration file, restart MySQL to apply the changes. Execute one of the following commands:

/etc/init.d/mysql restart

Alternatively, depending on your system, you can use:

service mysql restart
# or
systemctl restart mysqld

Clean Up Existing Log Files

After disabling logging, old binary log files (e.g., mysql-bin.000001, mysql-bin.000002) may still exist on disk. You can safely delete them to reclaim space.

Method 1: Clean Up via MySQL Command (Recommended)

This method safely deletes all binary log files and resets the log index.

1. Log into the MySQL command line:

   /usr/local/mysql/bin/mysql -u root -p

2. Enter your MySQL root password.
3. At the MySQL prompt, execute:

   RESET MASTER;

4. Type exit; to quit MySQL.

Warning: Executing RESET MASTER; will permanently delete all binary log files and start a new log sequence. Ensure you no longer need these logs for replication or recovery.

Method 2: Manual File Deletion

You can also delete the log files directly. They are typically located in the MySQL data directory (e.g., /usr/local/mysql/data/ or /var/lib/mysql/) with filename patterns mysql-bin.* and mysql-bin.index.

# First confirm the path and ensure MySQL is stopped or logging is disabled
rm /usr/local/mysql/data/mysql-bin.*
rm /usr/local/mysql/data/mysql-bin.index

Important Considerations and Alternatives

• Applicability: This guide is primarily for LNMP one-click installation environments. For other setups (Docker, official packages), the configuration file path may differ (e.g., /etc/mysql/my.cnf or /etc/mysql/mysql.conf.d/mysqld.cnf).
• Feature Impact: Disabling binary logging means you cannot use point-in-time recovery or master-slave replication. Evaluate your business needs carefully.
• Alternative (Keep Logs but Limit Size): If you need logs but want to control their size, keep log-bin enabled and set expire_logs_days to a small value (e.g., 3-7 days). MySQL will automatically purge old logs.

  expire_logs_days = 7

• Troubleshooting: If MySQL fails to start after configuration changes, check the file syntax or review the error log (usually at /usr/local/mysql/data/hostname.err).

Following these steps will effectively prevent MySQL log files from filling your disk, ensuring stable database operation.