Home / Articles / BSD / FreeBSD / MySQL binary log files take up a lot of space

MySQL binary log files take up a lot of space

Authors: Administrator User

Currently 0 out of 5 Stars.
1
2
3
4
5

Rating: 0/5 (0 votes cast)

Nov 10th 2013 01:41 pm 9211877 32382

One day you may learn an amazing fact that a lot of space on your computer is taken up by MySQL with not much data, actually being present in the database.

If you look inside MySQL database folder, you might see that there are many MySQL binary log files and they take up a lot of space. They might look like these: mysql-bin.000001, mysql-bin.000002, mysql-bin.000003 and so on.

These files are used for so-called binary log which in turn is used for replication and data recovery. If you don't need the binary log then you can simply turn it off, then all these files may be deleted.

To turn it off, just follow instruction below.

Find and comment out the line in file my.cnf by adding hash mark:

#log-bin=mysql-bin

Also all related options with the binary log must be commented out too, for example like this one:

#binlog_format=mixed

If some of them are not commented out then error messages are displayed in log file after restarting MySQL, for example like this one:

131022 11:14:24 [ERROR] You need to use --log-bin to make --binlog-format work.
131022 11:14:24 [ERROR] Aborting

To check your log file for error messages, the following commands can be used:

cat /var/db/mysql/*.err | grep ERROR | tail

Usually the my.cnf file has a path: /usr/local/etc/my.cnf in FreeBSD.

Now all binary log files can be removed from the folder /var/db/mysql:

cd /var/db/mysql
rm mysql-bin.*

And the last thing - restart MySQL.

To check if it's really turned off, just try again to search for the same kind of files in the folder /var/db/mysql after MySQL is restarted.

If no such files were found this means that you've done a pretty good job! Nice work!

Tags: Binary log Operation systems BSD FreeBSD MySQL

Discussion (total 3 comments)

Václav Zeman

November 11, 2013 at 03:27 am

I think this article give very wrong advice. The right thing to do is to find out how far back should you logs go and then set up the appropriate value of

expire_logs_days

(http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_expire_logs_days). Or you should just set up a cron job to purge the logs using command:

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

daniele

November 11, 2013 at 08:41 am

SQL> PURGE BINARY LOGS TO mysql-bin.00000[xxx]

this purge old log from start to mysql-bin.00000[xxx]

Administrator User

November 11, 2013 at 09:09 am

Hello! Thank you for quick feedback! I just wanted to show how you can simply turn the binary log off if you don't need it. So, I didn't advise to do it in all cases, only if you need it to be turned off. If I wrote something wrong, please correct me, then I will correct the article!