handling mysql binlog data

[Matthew Seaman]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160

Zbigniew Szalbot wrote:
> Hello,
> 
> Following a recent thread I would like to ask if it is safe to remove
> old binlog data. From the below I understand that the current binlog is
> mysql-bin.000007 and the previous ones (from 000001-000006) are not
> written to any more. I just need confirmation before I take any action.
> By asking whether it is safe, I only mean if deleting them will not
> affect mysql operation. I do backups of mysql data anyway.

The reason for keeping binlog files is so that you can replay any SQL
queries that modify the database contents.  You may want this for auditing
or for performance analysis, but the most usual reason is as a way 
of recoverng the database contents right up to the very latest point
possible.

In that case, if you're making regular database dumps, you only need the
binlog data written after the database dump.  (Depending on how paranoid
you are, you might have several database dumps, and keep binlogs between
the oldest dump you have and the present, but that's probably more than
is strictly necessary)

> -rw-rw----  1 mysql  mysql    61956466 Apr 16 08:44 mysql-bin.000001
> -rw-rw----  1 mysql  mysql   697756219 Apr 17 14:26 mysql-bin.000002
> -rw-rw----  1 mysql  mysql        1056 Apr 17 14:29 mysql-bin.000003
> -rw-rw----  1 mysql  mysql  1073745860 Apr 24 05:49 mysql-bin.000004
> -rw-rw----  1 mysql  mysql   620489997 Apr 27 10:48 mysql-bin.000005
> -rw-rw----  1 mysql  mysql   229357379 Apr 28 15:55 mysql-bin.000006
> -rw-rw----  1 mysql  mysql   869964294 May  2 14:15 mysql-bin.000007
> 
> Do I understand correctly that removing mysql-bin.000007 would result in
> mysql not writing any data to mysql-bin file until the server gets
> restarted?

MySQL maintains an open file descriptor on the current binlog file -- if
you delete that file while MySQL is running, you'll clear the directory
entry for the file, but the file data will still exist and be writable
for MySQL.  Of course, as soon as MySQL closes the descriptor, the last
link to the data will be lost, and the OS will feel free to recycle that
chunk of disk space.

	Cheers,

	Matthew

- -- 
Dr Matthew J Seaman MA, D.Phil.                       Flat 3
                                                      7 Priory Courtyard
PGP: http://www.infracaninophile.co.uk/pgpkey         Ramsgate
                                                      Kent, CT11 9PW, UK
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.9 (FreeBSD)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEAREDAAYFAkgbLBwACgkQ3jDkPpsZ+VZJRACgqRwhCfQcV/HV7XUe89w1UGDs
3lMAoIHm4elc4s/ACuhWQRt8hKnvvI+i
=10Os
-----END PGP SIGNATURE-----