Tuesday, December 4, 2012

MySQL DB binary file cleanup - disabling binary logs

I am a newbie.  Even when I think I am not, I am.  I learned a lot today by posting a question to stackoverflow the other day.  The question was moved to stackoverflow DB administration site.

The short of it was disk space was filling up and I was trying to find out where.  My analysis lead me into this folder wamp\bin\mysql\mysql5.5.24\data, which contained alot of large binary files.

Turns out these files are generated by MySQL whenever a reset was done to the mysql service, which I did reset a few times over the past couple months and whenever the file reached a certain size, 1GB.  Mysql writes all db transactions to a binary file as a default. Once a certain size limit is reached, this also is defined in the my.ini file, mysqld starts a new file.

This is quote on my question on stackoverflow:

"Those files are called binary logs. They contain a list of all completed SQL statements you have executed. Your mysqld process created them and autorotates at 1GB. Any restart of mysqld or issuing FLUSH LOGS; will close one binary log and open another"

I was able to safely remove them, since I do not use replication, or understand much about why I would use it.  I was able to recover about 20 GB of drive space!

I am also disabling the feature - I do not know why I would need every executed SQL command written to a binary file. If I were a more seasoned DB admin, I may feel differently, but for now, my bigger concern is eating up that drive space.

I have disabled this data being written to the binary log by commenting out this line in the my.ini file located at
 wamp\ bin\mysql\mysql5.5.24

I commented out the last line of this block, restarted mysql service and checked the folder to ensure that nothing was written.

# Replication Master Server (default)
# binary logging is required for replication
# log-bin=mysql-bin

After researching this a bit more, there are lot of postings about disabling binary logging in mysql, here is one example.

Moral of the story?  I do not know very much about being a DB admin.  Also, to be aware of what is going on on your server and be willing to do a little RD to figure out things like this.

1 comment:

  1. I really Enjoy the reading on this post, please continue it.


    SEO Sydney | SEO Melbourne | SEO

    ReplyDelete