Date: Fri, 02 May 2008 07:09:00 +0100 From: Matthew Seaman <m.seaman@infracaninophile.co.uk> To: Mel <fbsd.questions@rachie.is-a-geek.net> Cc: John <vaughajj@jmu.edu>, Paul Schmehl <pauls@utdallas.edu>, freebsd-questions@freebsd.org Subject: Re: Recovering mysql data - mysqlbinlog Message-ID: <481AAFFC.80101@infracaninophile.co.uk> In-Reply-To: <200805012255.45600.fbsd.questions@rachie.is-a-geek.net> References: <200805011913.DSZ93243@jmu.edu> <200805012152.06354.fbsd.questions@rachie.is-a-geek.net> <B7797CB8B91FC30D58FEBAC9@utd65257.utdallas.edu> <200805012255.45600.fbsd.questions@rachie.is-a-geek.net>
index | next in thread | previous in thread | raw e-mail
[-- Attachment #1 --]
Mel wrote:
> On Thursday 01 May 2008 22:24:33 Paul Schmehl wrote:
>> --On Thursday, May 01, 2008 21:52:05 +0200 Mel
>>
>> <fbsd.questions@rachie.is-a-geek.net> wrote:
>>> On Thursday 01 May 2008 21:13:41 John wrote:
>>>> Thank you Mel and Paul for the suggestions. From what I understand the
>>>> general query log is more for debugging and the binary log is for point
>>>> in time recovery and replication. I'll be adding a my.cnf file (using
>>>> the my-large.cnf as a skeleton) soon. I'm glad the issue was caught
>>>> earlier on and now I'm the wiser thanks to you guys. I wonder why the
>>>> default is no. I can't think of anyone who wouldn't find the binary
>>>> logging beneficial.
>>> I can think of a reason for FreeBSD. The binary logs are never deleted
>>> and upon every server restart a new one is created. If you're like me,
>>> developing on a laptop with a webenvironment including 'Mysql server',
>>> shutting down your laptop daily, you quickly find yourself having full
>>> /var partition.
>> That can be alleviated by adding the logs to newsyslog.conf and gzipping
>> and rotating them regularly.
>> If you don't restart mysql much, something like this would work:
>>
>> /var/db/mysql/[hostname]-bin.* mysql:mysql 660 7 * $W6D0 JBG
>> /var/db/mysql/[FQHN].pid
>>
>> If you're restarting it daily, something like this should work:
>>
>> /var/db/mysql/[hostname]-bin.* mysql:mysql 660 25 * $D0 JBG
>> /var/db/mysql/[FQHN].pid
>>
>> Adjust the counts and the rotation schedule to your liking and, of course,
>> use your own hostname and fully qualified hostname.
Ummm... actually this is not a particularly good idea. MySQL keeps an internal
list of all the binlogs it has available, and doing this will break that. The
best method is to cron a script that will run eg.
PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 7 DAY);
for whatever interval suits you. binlogs can absorb a lot of space for very
little return, especially on a busy server. There's really no point in keeping
binlogs from before the earliest full database dump in your backup cycle,
and even keeping that many is probably a little OTT for most purposes.
You are regularly dumping the database aren't you?
> Yes, in this particular case it can, not changing hostnames is a plus then (as
> in, getting hostname from your dhcp server) ;)
> Actually, I think you can use a fixed name, but it's been a while since I
> looked at the bin-log related variables.
>
> However if you're using the bin-log, to recover accidental deletes or
> replications, then you need to use the mysql provided SQL commands for it.
> It's scriptable (periodic/crontab), but not for use in newsyslog.
>
MySQL recommends that you use a fixed name for binary logs nowadays.
mysql-bin.NNNNNNN typically. The reasoning seems to be that it makes it
easier to deal with replication -- although relay logs are all still labeled
by the hostname of the master server.
Cheers,
Matthew
--
Dr Matthew J Seaman MA, D.Phil. 7 Priory Courtyard
Flat 3
PGP: http://www.infracaninophile.co.uk/pgpkey Ramsgate
Kent, CT11 9PW
[-- Attachment #2 --]
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.9 (FreeBSD)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iEYEAREIAAYFAkgasAIACgkQ8Mjk52CukIwnRQCfbQ0gmWPD8zmF+viqhfDa5kJ7
gPUAnjFSZgJfgnumZFy7Ye7tvxjvkOFD
=h5R+
-----END PGP SIGNATURE-----
help
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?481AAFFC.80101>