Wednesday, October 21, 2009

Filled your hard disk with Mysql binary logs

When I'm running a program, I saw an error message " No space left on device". I wonder how I don't have enough hard disk space, because at the same day I cleaned 2GB. Then I just think about what are the things I have done during this period to kill my hard disk. I only ran sample SQL scripts to check the MySQL Horizontal partitioning feature as mentioned in my previous post. It inserts 8,000,000 X 2 records to database.

Once I checked the size of the MySQL directory I figure out it has 6.9GB and mysql/data directory occupies 6.5GB out of that. There are lots of "mysql-bin.*" files available in the data directory. Ohhh I have enabled the binary logs. So every update your doing, it will be logged in the binary log. This will help you to recover in case of a crash. Do I need to recover my TEST databases used in the projects? Since this is a development environment, then the answer is "NO". This is how you disable it and make your hard dick clean with out the unwanted binary logs.

1. Stop the mysql server : /etc/init.d/mysql stop
2. Comment out "#log-bin=mysql-bin" in my.cnf
If you can't find out where is your my.cnf is then use the following command to find out the file.
find / -type f -name "my.cnf" -print
3. Then delete your "mysql-bin" from data directory
Note: It would be safe if you can keep last 10 bin logs files and delete the rest.
4. Start the mysql server: /etc/init.d/mysql start

Even though the disabling of binary log is straight forward for most of the developers, most of them do not disable in there MySQL at development environment. Unintentionally our hard disk will fill with out notice. That's why I thought of highlighted this.

I got 5GB cleared from my hard disk and SQL queries get faster... Now I can store 7 movies downloaded from Torrent :))

No comments: