9

They are taking up too much space:

/var/lib/mysql/jiaoyou-slow.log: 53M
/var/lib/mysql/mysql-bin.000005: 68M
/var/lib/mysql/mysql-bin.000003: 1.1G
/var/lib/mysql/mysql-bin.000007: 34M
/var/lib/mysql/mysql-bin.000004: 225M
Jesse Nickles
  • 250
  • 1
  • 12

4 Answers4

15

No, you should not delete them by hand.

If you delete them at the disk level, mysql will crash.

The command to remove them is:

PURGE BINARY LOGS TO 'mysql-bin.010';

Replace mysql-bin.010 with the last mysql-bin file that you wish to keep - typically the last one.

See the MySQL docs for more information.

Joshua Pinter
  • 155
  • 4
RageZ
  • 316
  • 1
  • 4
8

There are a few reasons to use binary logging. In order of importance:

  1. Replication -- Slaves suck content from the master's binary log.
  2. Greater backup granularity -- Replay logs over a prior backup point.
  3. Query logging -- More efficient than the general query log for those occasional "what write query ran?" moments.

So there are three things to check before you delete any binary logs:

  1. Do you have any replication slaves and are they up to date?
    • If you delete a binary log before the slave has had a chance to pull it into it's relay log then it will be unable to proceed with replication. Also there can be freak instances where you may need to delete the relay log and spool again from the master.
  2. Are your backups in check?
  3. Will you need to review any queries that have been executed over that logging period?

If you are happy with the answers to all of those then go ahead and delete them with the PURGE command as noted by RageZ. Absolutely don't delete them by hand because MySQL likes to keep track of them. You can either use the syntax TO to specify a filename or BEFORE to specify a date. You can see which file MySQL currently has open with SHOW MASTER STATUS.

A much better approach, as kedar notes, is to use expire_logs_days. This will automatically perform the action of purging any binary logs that are older than N days.

Dan Carley
  • 25,189
  • 5
  • 52
  • 70
3

you may also use expire_logs_days http://dev.mysql.com/doc/refman/5.0/en/purge-binary-logs.html

-2

In Ubuntu 12.04

PURGE BINARY LOGS

Add this line to your /etc/mysql/my.cnf and it'll need to be in the [mysqld] section.

expire_logs_days = 7

After:

/etc/init.d/mysql restart

Or:

Then log into Mysql and run this command to set the variable without having to restart Mysql.

SET GLOBAL expire_logs_days=7;

user147593
  • 1
  • 2
    The question is about MySQL. The underlying distribution has absolutely nothing with it. PURGE BINARY LOGS expects a date or a file. It doesn't make sense to set expire_log_days using SET GLOBAL, since it will be lost on mysqld restart -it's the kind of mistake that backfires in production environments. – dkaragasidis Nov 29 '12 at 14:36