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

MySQL binary log files take up a lot of space

MySQL binary log files take up a lot of space
 
MySQL binary log files take up a lot of space
  • Currently 0 out of 5 Stars.
  • 1
  • 2
  • 3
  • 4
  • 5
Rating: 0/5 (0 votes cast)

Thank you for rating!

You have already rated this page, you can only rate it once!

Your rating has been changed, thanks for rating!

Log in or create a user account to rate this page.


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!


 

Read also

Install MySQL server on FreeBSD

Install MySQL server on FreeBSD

Install Apache in FreeBSD

Install Apache in FreeBSD

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
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!

Log in or create a user account to post a comment.

easyos.net
117217982212577402640

Quick navigation

General navigation