Cleaning Up MySQL Binary Logs

| Comments

While Jimmy has previously covered disabling MySQL’s binary logging for those who don’t need it and don’t want to worry about the unexpected disk space usage, others prefer to merely purge older binary logs to reclaim disk space. MySQL’s binary logs live in /var/mysql and appear as mysql-bin.000001. Some of my servers merely hosting a few weblogs have bin logs taking up 4K-1MB, but others hosting large web applications have bin logs in the 1GB range. The last thing you want is for the drive hosting your MySQL databases to fill up unexpectedly.

Here’s a one-liner for removing all MySQL bin logs older than 30 days:

sudo find /var/mysql -name "mysql-bin.0*" -mtime +30 -exec rm {} +

Obviously, any command like this that automates deletion of potentially needed data could be disastrous, so make sure you have a good backup of your data before you try it. The benefit of the above command is that you can remove -exec rm {} + from the end of it to do a dry-run without actually removing any files and it’ll merely list the file names. Also, if you want preserve all bin logs newer than 60 days, simply change to read -mtime +60, or whatever best fits your needs.

Depending on your usage & backup setup, you could certainly automate this using cron or launchd.