Binary logs are filling up my Mac - Help!

A colleague of mine was running out of disc space on his development machine and on inspecting the disk space usage (use - www.derlien.com) found that MySQL was using up the bulk of the disk.

It reminded me that I solved this sometime ago and had an automated solution in place - so thought I should share - It is a real 'do your self a favour' tip! (I originally discovered this at http://www.cyberciti.biz/faq/what-is-mysql-binary-log/ - so much of this comes from that page)

Binary logs are used for data replication and recovery. They do build up so it is worth clearing them every now and then.
Basically out of the box MySQL is set up to log to a series of rolling binary log files (http://dev.mysql.com/doc/refman/5.1/en/binary-log.html) - These build up over time and eat your disk.

Update: I often get asked where these files actually are - Look in /usr/local/mysql/data (note that the mysql portion maybe a version number e.g. /usr/local/mysql-5.5.16-osx10.6-x86_64/data) - if in doubt look in your my.cnf for the path to the data files) - It can be usefull to see the actual files just to see the issue - but DON'T DELETE THEM!

In most cases on a dev box you can simply turn these off.

But here are some ways to clear the files that are there all ready - Don't simply delete them!!

Although the primary use of the binary logs is to replicate the database, in some cases they can be used for recovering the database.
So again - Don't delete them and backup your database.

Whilst you shouldn't delete them it is fine to purge them and to set up sensible expiry times and file sizes.
So in your my.cnf

If you don't want binary logging at all

If you don't need logging then you can turn it off (most don't unless you are replicating):
locate and edit your my.cnf (/etc/my.cnf - but may be somewhere else in WAMP/MAMP/XAMP etc)
locate and comment out the line:

You also need to remove or comment following lines:

Close and save the file. Finally, restart mysql server:

http://www.cyberciti.biz/faq/what-is-mysql-binary-log/

Purging your binary logs

The MySQL Console:

Some helpful scripts

At the command line

You can clear them up to a date or up to a specific log file:

# At the command line
# You can clear them up to a date or up to a specific log file:
#

$ mysql -u root -p 'rootpassword' -e "PURGE BINARY LOGS TO 'mysql-bin.00054';"
- or -
$ mysql -u root -p 'rootpassword' -e "PURGE BINARY LOGS BEFORE '2013-01-01 10:00:00';"

A Bash Script

#!/bin/bash
# Bash script to run every now and then
# source:http://knowledge-republic.com/CRM/2011/05/removing-of-binary-log-bash-script/
user=root
password=password
mysql -u$user -p$password -bse “show binary logs;” > binary.log
LastLine=`tail -n1 binary.log|awk ‘{print $1}’`
mysql -u$user -p$password -bse “purge binary logs to ‘”$LastLine”‘;”
rm binary.log

A Batch file for WAMP

rem Windows batch file for WAMP
@ECHO OFF
For /f "tokens=1-3 delims=/ " %%a in ('date /t') do (Set Datestr=%%c-%%b-%%a)
FOR /F "TOKENS=*" %%A IN ('TIME/T') DO SET TIME=%%A
echo Purging Logs on %Datestr% >> d:/wamp/logs/mysql-log-purge.log
@echo off
echo. | time /t
echo Purge Binary Logs .
D:\wamp\bin\mysql\mysql5.1.36\bin\mysql.exe -u root -p -e "PURGE BINARY LOGS BEFORE '%Datestr% 00:00:01';"
echo D:\wamp\bin\mysql\mysql5.1.36\bin\mysql.exe -u root -p -e "PURGE BINARY LOGS BEFORE '%Datestr% 00:00:01';"
echo Completed, Purged Binary Logs
echo. | time /t
pause
cls
if %errorlevel% == 0 goto okay
:error
echo Error Purging Binary Logs %Datestr% at %Time% >> d:/wamp/logs/mysql-log-purge.log
eventcreate /t error /id 100 /so MYSQLPurge /l application /d "MYSqlPurge - Error"
goto end
:okay
rem eventcreate /t information /id 101 /so MYSQLPurge /l application /d "MYSQLPurge - Success"
:end

• http://www.cyberciti.biz/faq/what-is-mysql-binary-log/
• http://www.rummandba.com/2011/02/script-to-purge-mysql-binary-logs.html
• http://knowledge-republic.com/CRM/2011/05/removing-of-binary-log-bash-sc...