…and other MySQL Oddities.
If your MySQL binary log files are a “Growing” problem… there are a few simple steps you can use to curtail their growth and save your disk space from these huge files.
They probably look like:
And around 1 gig in size each.
These files are generated when you have log-bin=mysql-bin or other log-bin= line in your /etc/my.cnf configuration file.
There are two ways to fix this problem:
The first is to stop the MySQL server from creating these binary files. The only reason you would need the binary log files is if you are doing mysql replication from a master to a slave. In this case, these files are necessary, at least until the slave(s) has caught up with the master. To stop this from logging, comment out (using #) the line containing ‘log-bin’.
The second is to continue logging – this is necessary as stated above if you are performing SQL replication.
You can add a line to your /etc/my.cnf file which will only keep these files around as long as necessary. The line is:
expire_logs_days = X
Where X is the number of days you’d like to keep them around. I would recommend 10, but this depends on how busy your MySQL server is and how fast these log files grow. Just make sure it is longer than the slowest slave takes to replicate the data from your master.
Just a side note: You know that you should do this anyway, but make sure you back up your mysql database. The binary log can be used to recover the database in certain situations; so having a backup ensures that if your database server does crash, you will be able to recover the data.
Originally posted 20070814 and last touched 20070814