>>>>> 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
------------------------------------------------------------------------------
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
|
