Date: Fri, 02 May 2008 15:58:36 +0100 From: Matthew Seaman <m.seaman@infracaninophile.co.uk> To: z.szalbot@lc-words.com Cc: freebsd-questions@freebsd.org Subject: Re: handling mysql binlog data Message-ID: <481B2C1C.2050206@infracaninophile.co.uk> In-Reply-To: <481B08B2.7070702@lc-words.com> References: <481B08B2.7070702@lc-words.com>
next in thread | previous in thread | raw e-mail | index | archive | help
-----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-----
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?481B2C1C.2050206>