-----Original message-----
From: Phil Stracchino <alaric AT metrocast DOT net>
Sent: Tue 10-04-2012 20:30
Subject: Re: [Bacula-users] Bacula MySQL Catalog binlog restore
To: bacula-users AT lists.sourceforge DOT net;
> On 04/10/2012 02:15 PM, Martin Simmons wrote:
> > Sorry, I didn't mean mysqldump on its own.
> >
> > The MySQL documentation suggests that you can make a backup from the output
> > of
> > mysqldump plus later binlogs and then use them to restore a database, but
> > what
> > use is that if it doesn't work with temporary tables?
> >
> > For this to work, all transactions should either be entirely in the output
> > of
> > mysqldump or entirely in the later binlogs. This could probably work if
> > temporary tables are used within a transaction, because hopefully mysqldump
> > will wait for it to finish. It probably can't work if temporary tables are
> > used without a transaction.
>
> You shouldn't think of a temporary table as persistent DB data. Think
> of them instead as part of the transient state of a single ongoing
> transaction. Is it a reasonable expectation for a DB restore to be able
> to restore any part of the transient state of a transaction that
> happened to be running when the backup was made? Even if it could,
> where would the results go?
>
> There's no way to resume an interrupted transaction from the middle, and
> so there's no point in backing up any of its state except to roll back
> anything it's already partly done. If you want to repeat the
> transaction, you have to restart it from the beginning, which will
> recreate any temporary tables it was using anyway.
>
>
> --
> Phil Stracchino, CDK#2 DoD#299792458 ICBM: 43.5607, -71.355
> alaric AT caerllewys DOT net alaric AT metrocast DOT net phil AT
> co.ordinate DOT org
> Renaissance Man, Unix ronin, Perl hacker, SQL wrangler, Free Stater
> It's not the years, it's the mileage.
>
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.
- 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.
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'.
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!
Any further input anyone can give is greatly appreciated.
Kind regards,
Joe
------------------------------------------------------------------------------
Better than sec? Nothing is better than sec when it comes to
monitoring Big Data applications. Try Boundary one-second
resolution app monitoring today. Free.
http://p.sf.net/sfu/Boundary-dev2dev
_______________________________________________
Bacula-users mailing list
Bacula-users AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users
|
