Re: Recovering mysql data - mysqlbinlog
Mel wrote: On Thursday 01 May 2008 22:24:33 Paul Schmehl wrote: --On Thursday, May 01, 2008 21:52:05 +0200 Mel <[EMAIL PROTECTED]> 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.NNN 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 signature.asc Description: OpenPGP digital signature
Re: Recovering mysql data - mysqlbinlog
On Thursday 01 May 2008 22:24:33 Paul Schmehl wrote: > --On Thursday, May 01, 2008 21:52:05 +0200 Mel > > <[EMAIL PROTECTED]> 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. 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. -- Mel Problem with today's modular software: they start with the modules and never get to the software part. ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to "[EMAIL PROTECTED]"
Re: Recovering mysql data - mysqlbinlog
--On Thursday, May 01, 2008 21:52:05 +0200 Mel <[EMAIL PROTECTED]> 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. -- Paul Schmehl ([EMAIL PROTECTED]) Senior Information Security Analyst The University of Texas at Dallas http://www.utdallas.edu/ir/security/ ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to "[EMAIL PROTECTED]"
Re: Recovering mysql data - mysqlbinlog
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. People running dedicated or semi-dedicated MySQL installations, are encouraged to tweak their installation anyway. The most common ones being: - put the binary logs on a large enough disk, if possible a seperate disk all together for performance. - by default, /tmp is used for temporary sorting tables, when JOINs demand it. If that's a small partition or worse a 64MB memory disk, expect to get in trouble, system wide. Use the tmpdir configuration variable. - thread_concurrency as indicated in the templates - skip-networking for security if all traffic is local - sort and key buffer + max_connections to limit/max out memory usage. -- Mel Problem with today's modular software: they start with the modules and never get to the software part. ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to "[EMAIL PROTECTED]"
RE: Recovering mysql data - mysqlbinlog
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 didn't run myisamchk since I'm trying to restore data that was mistakenly deleted and not recovering from a corruption. -John -Original Message- From: Mel [mailto:[EMAIL PROTECTED] Sent: Thursday, May 01, 2008 2:23 PM To: freebsd-questions@freebsd.org Cc: John; [EMAIL PROTECTED] Subject: Re: Recovering mysql data - mysqlbinlog On Thursday 01 May 2008 19:45:32 John wrote: > Can someone direct me to where the MySQL transaction logs are stored? I am > running FreeBSD 6.1 with MySql 5.0.41 which I installed from ports, no > extra config. From what I've read online, unless you disable them (which I > couldn't find in the makefile) the logs should be being created. Doing a > find / --name "bin." Didn't turn up anything. By default they end up in /var/db/mysql and are named $hostname-bin.### Also, the information you read online is incorrect. By default, binary logging (which are the transaction logs) is disabled. I don't recall whether this is a FreeBSD port specific modification. You can turn it on by putting a my.cnf in /var/db/mysql. Templates named my-$size.cnf are in /usr/local/share/mysql and are well-commented. > I'll also note that it is not in var/db/mysql with the rest of the mysql > data files and the ib_logfiles which would have been useful, but the data I > am trying to recover is from a MyISAM. I take it you tried myisamchk? -- Mel Problem with today's modular software: they start with the modules and never get to the software part. ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to "[EMAIL PROTECTED]"
Re: Recovering mysql data - mysqlbinlog
On Thursday 01 May 2008 19:45:32 John wrote: > Can someone direct me to where the MySQL transaction logs are stored? I am > running FreeBSD 6.1 with MySql 5.0.41 which I installed from ports, no > extra config. From what I've read online, unless you disable them (which I > couldn't find in the makefile) the logs should be being created. Doing a > find / --name "bin." Didn't turn up anything. By default they end up in /var/db/mysql and are named $hostname-bin.### Also, the information you read online is incorrect. By default, binary logging (which are the transaction logs) is disabled. I don't recall whether this is a FreeBSD port specific modification. You can turn it on by putting a my.cnf in /var/db/mysql. Templates named my-$size.cnf are in /usr/local/share/mysql and are well-commented. > I'll also note that it is not in var/db/mysql with the rest of the mysql > data files and the ib_logfiles which would have been useful, but the data I > am trying to recover is from a MyISAM. I take it you tried myisamchk? -- Mel Problem with today's modular software: they start with the modules and never get to the software part. ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to "[EMAIL PROTECTED]"
Re: Recovering mysql data - mysqlbinlog
--On Thursday, May 01, 2008 13:45:32 -0400 John <[EMAIL PROTECTED]> wrote: Can someone direct me to where the MySQL transaction logs are stored? I am running FreeBSD 6.1 with MySql 5.0.41 which I installed from ports, no extra config. From what I've read online, unless you disable them (which I couldn't find in the makefile) the logs should be being created. Doing a find / --name "bin." Didn't turn up anything. /var/db/mysql/hostname-bin.number /var/db/mysql/hostname-bin.index /var/db/mysql/hostname.err And if you've enabled query logging (stores passwords in plain text!) /var/db/mysql/query.log -- Paul Schmehl ([EMAIL PROTECTED]) Senior Information Security Analyst The University of Texas at Dallas http://www.utdallas.edu/ir/security/ ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to "[EMAIL PROTECTED]"
