My C:\ drive partion space was almost full, so I tried to analyse junk data on my machine to free some space. I noticed that MySql ‘data’ folder was using around 16GB. Further looking into the data folder I noticed that many mysql binary log files had been created and they were occupying GBs of the space.

After cleaning the log file the ‘data’ folder size reduced to ~6GB.

What is the purpose of binary log files, why not delete them?

The binary log contains information about SQL statements that modify database contents. This information is stored in the form of “events” that describe the modifications.

The binary log has two important purposes:

1. Replication:

The binary log is used on master replication servers as a record of the statements to be sent to slave servers. 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.

2. Data Recovery:

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.

How to turn off binary logging?

If in our local development environment we are not using a master-slave-configuration and have no critical data that would need to be recovered in case of a rare DB inconsistency issue, we can free up GBs of disk space by turning off “binary logging” in MySQL.

To turn off binary logging follow the below steps:

1. Open MySql configuration file (my.ini / my.cnf) in your favourite editor.

2. Locate the following line and comment it.

#log-bin=mysql-bin

3. Check if the following line are also present and comment them.

#expire_logs_days=10

#max_binlog_size=100M

4. Save the file and restart the MySql server.

To delete the binary log files manually after you turn off binary logging, follow the following steps:

1. Stop the Mysql server.

2. Verify that no Mysql process/service is running.

3. Navigate to the Mysql data directory and delete the binary log files and the index file (You may take a backup of MySql data folder to restore it back if something goes wrong)

4. Restart the MySql server.

How to disable logging for current session?

At the mysql prompt run the following statement:

mysql> SET sql_log_bin=0;

The session user must have the “SUPER” privilege to set this variable.

How to view the list of the binary log files on the server?

To list the binary log files on the server, login to mysql and run the following command:

mysql> SHOW BINARY LOGS;

Alternatively you can run

mysql> SHOW MASTER LOGS;

How to Purge Master Logs?

The log consists of a set of binary log files, plus an index file. By running ‘PURGE BINARY LOGS’ or ‘PURGE MASTER LOGS’ statement at the mysql prompt you can purge binary log on the basis of date or filename. BINARY and MASTER are synonyms.

To purge binary log files on the basis of file, run the following statement at the mysql prompt:

mysql> PURGE BINARY LOGS TO 'mysql-bin.000127';

This will delete all the binary log files listed in the log index file before ‘mysql-bin.000127’ and also update the binary log index file.

To purge binary log files on the basis of date, run the following statement at the mysql prompt:

mysql> PURGE BINARY LOGS BEFORE '2014-02-28 10:12:23';

This will delete all the binary log files listed in the log index file before ‘2014-02-28 10:12:23’ and also update the binary log index file.

The date is given in the format ‘YYYY-MM-DD hh:mm:ss’.

References:

http://dev.mysql.com/doc/refman/5.6/en/binary-log.html
http://dev.mysql.com/doc/refman/5.1/en/stored-programs-logging.html
http://dev.mysql.com/doc/refman/5.1/en/replication-options-binary-log.html
http://dev.mysql.com/doc/refman/5.0/en/show-binary-logs.html
http://dev.mysql.com/doc/refman/5.0/en/reset-master.html
http://dev.mysql.com/doc/refman/5.0/en/purge-binary-logs.html
http://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html