[mythtv-users] upgrading MySQL

[David Brodbeck]

On Fri, April 3, 2009 8:26 am, Michael T. Dean wrote:
>> So the bottom line on copying binary databases is: Maybe. But the
>> standard advice of making backups with mysqldump definitely holds. You
>> certainly cannot count on being able to read binary database files from
>> a system backup, as I have learned the hard way at the cost of several
>> evenings' worth of effort.
>
> And, IMHO, why waste the time trying (and not being /absolutely/ certain
> all your data is good) when all it takes for peace of mind is a simple
> backup and restore?  (Where the backup and restore are both easy with
> the backup/restore scripts. :)

The main reason you see people doing binary backups is speed.  On a large
database running mysqldump can take tens of minutes, and during that time
all the tables are write-locked, effectively making the database unusable
until the backup is complete.

With binary backups the things to watch out for are:
1. Make sure the backup is consistent.  The tables must be write-locked
before copying the files or the backup will probably be worthless.
2. Make sure you'll be able to re-create the same system for reading the
binary files -- same MySQL version, same compile options, same
architecture.

Condition (1) is often hard to achieve in a normal system backup.  What
I've done in the past is to use mysqlhotcopy (which does table locking) to
make a consistent backup copy of the database files, then let the normal
system backup routine archive it.  mysqlhotcopy is a lot faster than
mysqldump so the database is offline for a shorter period of time.  It
ONLY works on MYISAM tables, though.  You can't back up InnoDB this way.

On filesystems that support snapshots an even faster method of backing up
MYISAM tables would be to lock the tables, snapshot the filesystem, then
unlock the tables and back up the snapshot.

If you can afford the downtime, though, mysqldump is definitely the most
robust way to go.  Among other things, the output file is text, so if it
gets corrupted you can dive in and fix it. ;)  Also, with mysqldump and
query logging, you can actually recreate any database state *since* the
last backup, too -- but the query log has a serious performance penalty,
so I wouldn't recommend it for production use.