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