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>
next in thread | previous in thread | raw e-mail | index | archive | help
This is an OpenPGP/MIME signed message (RFC 2440 and 3156)
--------------enig969BD8896FBD52C751B888BA
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: quoted-printable
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 po=
int
>>>> in time recovery and replication. I'll be adding a my.cnf file (usi=
ng
>>>> 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 t=
he
>>>> 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 delete=
d
>>> 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 ful=
l
>>> /var partition.
>> That can be alleviated by adding the logs to newsyslog.conf and gzippi=
ng
>> 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 cou=
rse,
>> use your own hostname and fully qualified hostname.
Ummm... actually this is not a particularly good idea. MySQL keeps an in=
ternal
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 v=
ery
little return, especially on a busy server. There's really no point in k=
eeping
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 t=
hen (as=20
> in, getting hostname from your dhcp server) ;)
> Actually, I think you can use a fixed name, but it's been a while since=
I=20
> looked at the bin-log related variables.
>=20
> However if you're using the bin-log, to recover accidental deletes or=20
> replications, then you need to use the mysql provided SQL commands for =
it.=20
> It's scriptable (periodic/crontab), but not for use in newsyslog.
>=20
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 labe=
led
by the hostname of the master server.
Cheers,
Matthew
--=20
Dr Matthew J Seaman MA, D.Phil. 7 Priory Courtyard
Flat 3
PGP: http://www.infracaninophile.co.uk/pgpkey Ramsgate
Kent, CT11 9PW
--------------enig969BD8896FBD52C751B888BA
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"
-----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-----
--------------enig969BD8896FBD52C751B888BA--
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?481AAFFC.80101>