[mythtv] mysql 5 bug affecting mythtv

[Colin Guthrie]

Dan Wilga wrote:
> At 2:09 PM -0800 1/30/08, Rob Smith wrote:
>> As for the mysql binary logs, that's done because mysql recommends and
>> defaults to having binlogs enabled. I personally think not having
>> binlogs is encouraging a foot shooting.
> 
> IMHO, the only reason you would want to turn binary logs on is if you 
> are intending to use the replication or cluster features of MySQL. 
> Otherwise, all you're doing is eating up lots of disk space and a 
> non-trivial amount of CPU cycles.
> 
> There is little benefit in keeping binlogs as a form of backup, 
> because on a busy system it would be very difficult to figure out 
> exactly where to start playing them back in order to recreate a 
> working system. They would have to be exactly synchronized with the 
> most recent file-based (mysqlhotcopy or equivalent) backup.

I find binlogs pretty useful in terms of a backup TBH.

Every night I flush the logs, close all the db tables and take an LVM
snapshot of the database partition and back it up to a separate disk.
This is very quick to do, doesn't interrupt the service (much) and is
very quick to restore from too. Every hour I flush the binary logs and
copy them to a separate disk. That way even with a hideous server crash
the worst case scenario is 1 hour of data loss. The good thing with
snapshots is that it prevents the whole problem of backing up
non-transactional tables such as MyISAM.

A replicated slave server would be better but if you can't justify the
expense of that, this is a pretty good system.

Col


-- 

+------------------------+
|     Colin Guthrie      |
+------------------------+
| myth(at)colin.guthr.ie |
| http://colin.guthr.ie/ |
+------------------------+