>>>>> On Fri, 13 Apr 2012 21:47:04 +0100, Joe Nyland said:
>
> On 13 Apr 2012, at 18:14, Martin Simmons wrote:
>
> >>>>>> On Wed, 11 Apr 2012 11:09:12 +0100, =?utf-8?Q?Joe Nyland?= said:
> >>
> >> Ok, firstly: sorry for not following up sooner. There have been several
> >> replies for this thread since my last reply, so thanks everyone for your
> >> input.
> >>
> >> Phil, you point out that the problem here is most likely to do with the
> >> fact
> >> that other jobs are running whilst the full DB backup is being run and I
> >> agree with this.
> >>
> >> Now, to me, the simple resolution to this is to assign a backup window for
> >> the catalog backup to run in, when no other backups are running. There are,
> >> however, two issues I can foresee:
> >> - The MySQL backup script is being called by a Bacula job as a run before
> >> script, so there will allways be a job 'running' whilst the catalog dump is
> >> being performed.
> >
> > Should be OK because Bacula isn't in the middle of inserting File records
> > for
> > that job when the before script runs.
> >
> >
> >> - One backup job in particular takes approx 24-48 hrs to complete, so this
> >> will be constantly updating the catalog with file records, no matter when I
> >> schedule the full catalog backup for.
> >
> > Yes, that will be a problem.
> >
> >
> >> I'm therefore back to square one.
> >>
> >> Whilst I understand the complications with temporary tables (and threads)
> >> that Phil has pointed out, I do agree with what Martin was suggesting that
> >> there may be a better way to use temporary tables. I don't, however, have
> >> enough MySQL knowledge to provide any further suggestions to improve this
> >> in
> >> later versions of Bacula.
> >>
> >> Stephen, yes, I am running the backup whilst other backups are running, for
> >> reasons stated above. You are right - if there were no jobs running, the
> >> temp tables wouldn't be referred to in the first place, as they would not
> >> exist.
> >>
> >> Your suggestion of keeping a 'buffer' of bin logs is interesting and this
> >> was the last thing I tried to resolve this problem with. I basically
> >> removed
> >> the '--delete-master-logs' option from the mysqldump line, so that old
> >> binary logs are not removed. (Side point: I'm not sure when the
> >> '--delete-master-logs' actually deletes the binary logs - the mysqldump
> >> docs
> >> state that it "[sends a] PURGE BINARY LOGS statement to the server
> >> __after__
> >> performing the dump operation". This doesn't make sense to me, as anything
> >> that's created between the dump being initiated and the dump completing
> >> will
> >> be deleted from the logs and there's no guarantee it's included in the
> >> dump). So I removed the '--delete-master-logs' option, in a hope this would
> >> somehow allow me to see the CREATE TEMPORARY TABLE statements earlier in
> >> the
> >> binary logs, but it didn't.
> >>
> >> What I didn't do that you have suggested is to restore older logs first
> >> i.e. logs from before the dump, then let it work through them, skipping any
> >> duplicate key errors. My issue with this workaround though, is that I fear
> >> that it could corrupt the database in some way (unbeknown to me at the time
> >> of restoring) if I am just 'skipping errors'.
> >
> > Yes, it sounds like a risky approach. I think you would have filter the
> > older
> > logs to find the commands that are needed to make the newer logs work.
> >
> >
> >> To me, this all does beg the question: "Why use temporary tables in the
> >> first place?" Again, I may not have enough MySQL experience to realise this
> >> for myself yet, but it's my view at this stage, after battling this for a
> >> while now!
> >
> > The main reason was probably for simplicity with multiple connections to the
> > catalog (each connection automatically gets its own temporary tables). I
> > see
> > that some of the accurate-mode code is using a different approach though,
> > with
> > a table named after the jobid to make it unique.
> >
> > __Martin
> >
>
> Martin and Phil, thanks for your responses.
>
> I seem to have settled on the suggestion that maybe I should't worry about
> these temporary tables too much. As I think Phil is trying to explain; if
> there were a way in which these temporary tables could be backed up and
> restored somehow, then the actual data they would bring to the database
> would be useless anyway - the job wouldn't suddenly start running again from
> where it left off. So for all intents and purposes, the information about
> files being backed up in the job running at the moment when the database is
> being backed up may as well not even be in the database. After all, the
> temporary batch tables would only be deleted later on the the binary logs
> when the file records are actually moved to the file table, so to me, it
> seems pointless to worry about the batch table references. (If I've
> misunderstood something here, please accept my apologies)
>
> So... I've been doing daily test restores, but with the addition of the '-f'
> flag to mysql, so that it will continue to read from the binary logs, even
> if it come across an issue.
>
> Further to the above, all I've found it's complained about is the fact that
> the bacula.batch table does't exist. If we apply Phil's theory from above,
> then the messages about the batch table can be 'disregarded' and the rest of
> the database can be restored regardless. Obviously, using the '-f' flag
> means that any other 'serious' errors will be ignored, but they are still
> echoed to stout, so it's pretty easy to pick up on them, should they crop up
> when restoring. Like I say though, the only errors that I've seen are
> references to the batch table not existing. IF all else fails and I can't do
> a point in time restore, then it's not the end of the world - if I was doing
> a restore of my Bacula catalog, then I think I would have a fair amount of
> other stuff on my mind at that point, which I would be more worried about
> than getting a bang up to date catalog. Getting incremental catalog backups
> was more of a convenience/learning exercise, rather than a strict
> requirement.
I would expect some of the errors to be statements that use values from the
missing batch table like this:
INSERT INTO File (FileIndex, JobId, PathId, FilenameId, LStat, MD5, DeltaSeq)
SELECT batch.FileIndex, batch.JobId, Path.PathId,
Filename.FilenameId,batch.LStat, batch.MD5, batch.DeltaSeq
FROM batch
JOIN Path ON (batch.Path = Path.Path)
JOIN Filename ON (batch.Name = Filename.Name)
and similarly for the Path and Filename tables.
Therefore it seems to me that the records in the File, Path and Filename
tables will be missing after restoring from the incremental backup of the
binary logs.
Have you checked that the File data for those backups is in the catalog after
restoring the binary logs?
__Martin
------------------------------------------------------------------------------
For Developers, A Lot Can Happen In A Second.
Boundary is the first to Know...and Tell You.
Monitor Your Applications in Ultra-Fine Resolution. Try it FREE!
http://p.sf.net/sfu/Boundary-d2dvs2
_______________________________________________
Bacula-users mailing list
Bacula-users AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users
|
