I've been using mysql for development. I don't use replication but I guess I had binary-logging enabled for some time on one of my previous computers and I have unknowingly accumulated hundreds of binary log files.

I'm using Linux now and I actually need to start using binary logging. When I try to enable binary log in my.cnf - mysql server doesn't start and this is what I find in error log:

  1 110314 14:39:40 [Note] Plugin 'FEDERATED' is disabled.                                                                                                                                                                                                                        
  2 110314 14:39:40  InnoDB: Started; log sequence number 0 36669358
  3 ^G/usr/sbin/mysqld: File '.\mysql-bin.000179' not found (Errcode: 2)
  4 110314 14:39:40 [ERROR] Failed to open log (file '.\mysql-bin.000179', errno 2)
  5 110314 14:39:40 [ERROR] Could not open log file
  6 110314 14:39:40 [ERROR] Can't init tc log
  7 110314 14:39:40 [ERROR] Aborting
  9 110314 14:39:40  InnoDB: Starting shutdown.

so - I assume - I just have to delete old binary logs - right? Can I just delete all mysql-bin. files? Would I have to clear mysql-bin.index file as well?



Edit: Whoa, I totally misread the question. Yeah make sure bin_log is commented out. Restart. remove any bin logs and the bin log index. Then add bin_log back to your my.cnf and restart. I'll leave my original answer cause it contains some decent stuff especially under #1.

Original: You've got a few options here. However it's never a good idea to delete bin logs via the filesystem unless the database had been configured not to generate them any longer.

  1. Keep bin logs, but expire them occasionally. Add expire_logs_days = 3 to your my.cnf and all Mysql servers should have this config if they generate bin logs. I use 10 days on my servers. You may want to add max_binlog_size = 100M or some other size smaller than the default of 1G on a dev server. Mysql only removes bin_logs once it's reached the max days and max size.

  2. remove bin logs altogether. Comment out log_bin in your my.cnf. Restart Mysql because log_bin is not dynamic. You can now go in and delete the bin logs via the filesystem.

  3. Purge bin_logs up to a certain point. Do the following from the mysql commandline. You can change the day to whatever works for you.


In most cases I'd recommend #1 because sometimes it is useful to have a couple of days of bin logs.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.