On 04/10/2012 07:51 AM, Joe Nyland wrote:
> -----Original message-----
> From: Joe Nyland<j...@joenyland.co.uk>
> Sent: Fri 06-04-2012 22:15
> Subject: Re: [Bacula-users] Bacula MySQL Catalog binlog restore
> To: Bacula Users<bacula-users@lists.sourceforge.net>;
>> On 6 Apr 2012, at 00:08, Phil Stracchino wrote:
>>
>>> On 04/05/2012 06:46 PM, Stephen Thompson wrote:
>>>> On 04/05/2012 03:19 PM, Joe Nyland wrote:
>>>>> As I think it may be useful, here's the line taken from my MySQL
>>>>> 'RunBeforeJob' script when the full backup is taken:
>>>>>
>>>>> mysqldump --all-databases --single-transaction --delete-master-logs
>>>>> --flush-logs --master-data --opt -u ${DBUSER} -p${DBPASS}>
>>>>> ${DST}/${HOST}_${DATE}_${TIME}.sql.dmp
>>>>>
>>>>> Can you spot anything there which could cause the creation of
>>>>> this/these temporary tables to not be included in the bin log? I've
>>>>> spent a while getting this list of options right and I'm not 100%
>>>>> sure I've got the correct combination, but it's possible I've
>>>>> missed something here.
>>>>>
>>>>
>>>> Sorry, I don't think I can be much help here. I'm wrangling with
>>>> mysqldump myself at the moment since I moved from MyISAM tables to
>>>> InnoDB and the documentation is very poor.
>>>>
>>>> Are you using InnoDB... If not, I'm not sure why
>>>> --single-transaction is there, and if so, I wonder if it shouldn't
>>>> come after --opt. The options order matter and since --opt is the
>>>> default, having it at the end of your line is only resetting anything
>>>> you change earlier in the line back to the --opt defaults.
>>>
>>> Since --opt is the default, there's no reason to ever explicitly specify
>>> it at all in the first place.
>>>
>>> And as we just discussed the other day, --single-transaction is
>>> ineffective without either --skip-lock-tables, or --skip-opt and adding
>>> back in the stuff from --opt that you want.
>>>
>>>
>>> --
>>> Phil Stracchino, CDK#2 DoD#299792458 ICBM: 43.5607, -71.355
>>> ala...@caerllewys.net ala...@metrocast.net p...@co.ordinate.org
>>> Renaissance Man, Unix ronin, Perl hacker, SQL wrangler, Free Stater
>>> It's not the years, it's the mileage.
>>
>> Thank you all for you input.
>>
>> Following your advice, I've now changed my mysqldump line in my script to:
>>
>> mysqldump --all-databases -u ${DBUSER} -p${DBPASS} --flush-logs
>> --master-data=1 --delete-master-logs --opt>
>> ${DST}/${HOST}_${DATE}_${TIME}.sql.dmp
>>
>> Re-reading the mysqldump reference manual (yet again!) I'm starting to wonder
>> whether the '--delete-master-logs' option is causing some important
>> transactions to be lost from the binary logs, which is the reason why the
>> temporary table creation statements mentioned above are missing from my log
>> file. My theory is that during the dump of the database, the temporary tables
>> are created, then the dump finishes and deletes the binary logs, therefore
>> removing any log of the temporary tables being created in the first place.
>> Does
>> that sound feasible?
>>
>> Thanks,
>>
>> Joe
>
> I'm a bit ashamed to admit I'm still battling this! I've removed
> '--delete-master-logs' from my mysqldump line, but it hasn't helped.
>
> For some reason, it seems as if the dump does not contain any mention of the
> temporary tables being created, neither do the binary logs, however there are
> statements which refer to bacula.batch, as if it should be there.
>
> Could it be that these statements refer to a bacula.batch table which was
> created by another thread prior to the mysql dump being created? ...and
> that's why the "CREATE TEMPORARY TABLE bacula.batch" statement is not in the
> binary logs after the full backup. Surely, if this were the case, the
> bacula.batch table sowuld be included in the dump would they not?
>
> My fear is that because I am restoring binary logs, the binary log restores
> will be running under their own threads (after the main dump file had been
> restored) and thus will be unable to access temporary tables created by any
> other previous threads - making what I am trying to achieve impossible.
>
> I know this is becoming a little OT as it's largely to do with mysqldump and
> binary logging, but I hope someone can help.
>
> Any ideas how to overcome this?
>
I wonder if you're running the backup while other jobs are running?
If nothing else is running, then the dump shouldn't miss any of the temp
tables, because there will be none during the dump.
If you run it concurrently, consider this:
Rather than blasting away your binlogs, keep them around for longer than
the interval between your backups (i.e. keep them for at least 2 days if
you dump every day). Then backup ALL binlogs when you do the
incremental. Then if you need to restore, you should be able to
intentionally go back farther in time in the binlogs, before the dump,
and start syncing from there WITH errors temporarily disabled (or at
least duplicate entry errors). This might/should let the import skip
over stuff that the dump has already restored, but catch the stuff that
it missed, like temp tables.
Problem is you're likely to not know WHEN to start in the logs, though
you could arbitrarily go back 24 hours. Then of course, the problem is
that you still might catch the binlogs in a spot after a temp table
creation, but not before it's done being used, so you may still run into
'no such table' errors, which you could skip over. Not the best
solution in the world, and there might be ways to fix up these two
issues, but it sounds better than nothing to me.
Stephen
> Thank you.
>
> 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@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/bacula-users
--
Stephen Thompson Berkeley Seismological Laboratory
step...@seismo.berkeley.edu 215 McCone Hall # 4760
404.538.7077 (phone) University of California, Berkeley
510.643.5811 (fax) Berkeley, CA 94720-4760
------------------------------------------------------------------------------
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@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users
