Monthly Archives: October 2008

managing mysql binary logs

Binary logs is how mysql keeps track of what changed in the databases. While it is recommended (in case you want to recover the database ) and sometimes even required ( if you want to replicate the db ) to keep such logs if you have a server where the database changes frequently those logs will occupy a lot of your disk space.

If you are tempted to just delete ( rm ) some of the old logs files DON'T do it. Or if you do it remember to also update the index file and remove the lines containing the log files you have deleted from it otherwise you will get in trouble, depending on your version mysqld server  might not start next time.

A better way then deleting them directly from the file system is to use the "purge logs" statement to delete all logs prior to a certain log file or prior to a certain date.The only problem with this is that you still have to remember to do this from time to time or set up a cron job to do it or else you will have to do it when mysql dies because it ran out of disk space. Luckily there is an even better solution.

There is a configuration option for mysql server that allows you to specify the number of days you want to keep logs for. Everything older then that number of days will be automatically deleted my the mysql server. The configuration variable is named: expire_logs_days. Something like expire_logs_days=30 will delete all log files older then 30 days

Warning! purge logs and expire_logs_days might not work if you deleted the bin logs files directly from the file system. To make them work  you will have check each line in the .index file. Each line in the .index file contains a bin log file name. If any file mentioned in the .index file doesn't exist on disk you will have to delete that line. Then just restart the mysql server.

One other tip to make the logs use less disk space is to tell the server not to record logs for databases where you don't care about loging ( like databases you only use for development or testing that might still get a lot of updates but you don't want to replicate them or you don't need to recover them if anything breaks ) .Here you can either tell mysql to only keep bin logs about some databases or to ignore others. The binlog-do-db and binlog-ignore-db configuration options will help you with this.