[mythtv-users] Full or Partial Database restore?
Michael T. Dean
mtdean at thirdcontact.com
Tue Sep 14 18:05:36 UTC 2010
On 09/14/2010 01:41 PM, John Haywood wrote:
> On 15/09/2010, at 3:18 AM, Michael T. Dean wrote:
>> On 09/14/2010 09:04 AM, John Haywood wrote:
>>> After much instability going from Mythbuntu 9.10 to 10.04, even
>>> trying a database backup, clean install and restore the inevitable
>>> finally happened:
>>> 0.23.1 fixes, PPA repo
>>>
>>> I was experiencing several freezes and lockups, usually one or two
>>> per day.
>>>
>>> Finally the whole box just started up to a setup screen - and
>>> refused to update the schema from<blank> to 1025 (i.e. no value to
>>> current)
>>> I ran the db optimise script to check, then the mysqlcheck, as per
>>> the wiki
>>> mysqlcheck found problems which it repaired
>>>
>>> I then thought I'd check the xfs filesystem on which the recordings
>>> are stored and found quite a few orphaned files, which it placed in
>>> Lost + Found
>>>
>>> I'm now faced with reinstalling the OS, and restoring the db from a
>>> backup. I'd like to keep my database of recordings, videos and
>>> schedules if possible, but if I have to lose the rest, I can live
>>> with setting the combined fe/be up again
>>>
>>> Should I try a full restore from a couple of days before the final
>>> crash first - or am I just prolonging the agony given the previous
>>> issues?
>>>
>>> Or should I just do a partial restore from the last backup
>>
>> There's really no benefit to doing a partial restore if you have a
>> good backup of a non-corrupt schema.
>
> This I understand, and thanks for the advice. What i'm concerned about
> is the quality of the backup, and hence the restore I guess ....
Yeah, that question is a bit more difficult to answer.
Based on the symptoms you've described, the most-likely problem you were
encountering was that your settings table (where the current DB schema
version is stored) was corrupt. In this case, optimize_mythdb.pl will
be unable to run, and mythtv-setup and mythbackend would both start up
and think that no database exists and try to update from <blank> to
current DB schema version.
If that's the case, the most-likely worst-case result is that you lost a
single setting value from the DB when settings was repaired. That
setting value will be replaced with its default value the next time you
run the program that uses the setting. Therefore, if the above is true
(and is the /only/ problem), a backup from the repaired DB is usable
(and, IMHO, you should use the full restore).
However, it's also possible that you had other crashed tables, too. If
that's the case, you may have lost more data. Even so, the worst-case
scenario is likely that you lost a row from each of those tables. And,
if any of those tables is in the list of tables restored with a partial
restore, you lost the row whether you do a full or partial restore.
And, if the tables aren't in the list of tables restored by a partial
restore, then you still have to re-create the lost data--as well as all
the data you're throwing away with a partial restore--so, the partial
restore really doesn't do anything other than force you to re-create
data now (rather than when you find it's missing--i.e. doing
http://www.gossamer-threads.com/lists/mythtv/users/264034#264034 when
you find out one or more capture card isn't working; or going into
mythtv-setup and fixing Storage Groups when you find out one or more
directories is missing; or running mythcommflag --rebuild on a recording
when you find it isn't seeking properly; or ...).
Note, also, that you can do a full restore, now, run for a couple weeks,
and then change your mind (and do a partial restore from the new
system's backup). If you do a partial restore, now, you can't change
your mind--you'll lose all the recording information from the point of
the backup until you change your mind.
It's completely up to you, but if it were me, I'd do a full restore, and
if things don't work, then I'd likely fix the issue(s), as above. If a
bunch of things don't work--and I fix a few of them over a period of a
few weeks--and I stop trusting my data, then I'd consider a partial
restore. That said, I doubt that you'll have any problems with a full
restore (it will likely "just work").
FWIW, I used to do partial restores all the time to "clean up" my DB.
Then I started doing a lot of work with the DB-related code in MythTV
and with the database itself, and I found that partial restores are
almost always a lot of work with no benefit.
Mike
More information about the mythtv-users
mailing list