How to get rid of mysql binlogs?

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
  8 
  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?

Thanks!

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.

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.
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.
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.

mysql> PURGE BINARY LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 7 DAY);

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

How to get rid of mysql binlogs?

Related

Recent Posts