[mythtv-users] upgrading MySQL

[Greg Woods]

On Fri, 2009-04-03 at 10:15 -0400, Michael T. Dean wrote:
> On 04/03/2009 02:09 AM, David Brodbeck wrote:

> > If it's a backup made with mysqldump, yes, you should be able to 
> > restore it on a different version of MySQL.  A mysqldump backup is 
> > just a series of SQL statements that will recreate the database; it 
> > doesn't care about versions or word size or endianness.
> >
> > It's only the binary files you have to be careful about moving.
> 
> Right, and the easiest way to get a good backup is 
> http://www.mythtv.org/wiki/Database_Backup_and_Restore 

It turns out the answer with regard to moving binary files is: "it
depends". I finally got a good mythconverg database from my old dead
system to a new system, but I got it because I found some
mysqldump-style backups buried in the recordings directory. Apparently
mythbackend (or mythfilldatabase?) is making periodic backups. From
this, I was able to restore a database that is only 4 days out of date,
so only a few lost recording entries that I may be able to recreate
manually. When I started from a database restored from one of these
backups, mythbackend was then able to upgrade the schema without any
problems and all is well in the database department (now on to VDPAU and
HD-PVR :-)

I have some other single-table databases on the same server, where the
binary files read in just fine despite being 64-bits vs. 32-bits and
different MySQL versions.

At work, we use DRBD to replicate the entire disk partition of a large
multi-table MySQL database to a backup server. This works just fine.
Obviously that is copying the binaries, although it's different than
using scp or ssh/tar to copy them because the entire file system
structure is identical, not just the binary files themselves. Also the
entire /var/lib/mysql tree is copied, not just the mythconverg database.
And of course the two machines have identical hardware and software. 

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.

--Greg