On 04/16/2012 10:38 AM, Martin Simmons wrote:
> I think it should be impossible to make a database backup that represents a
> point in the middle of anything.  By "should be impossible" I mean that either
> MySQL should always prevent it or database clients should use MySQL in such a
> way that allows MySQL to prevent it.

It's not MySQL's business to do any such thing.  In fact, the preference
from the DB point of view is that you should be able to start a DB
backup in the middle of ANYTHING and still obtain a consistent backup.
And indeed you can, if you're not backing up binlogs.  If you back up
binlogs and attempt to replay them, the onus is somewhat upon you to
ensure that the transactions you're replaying make sense in the context
of the state of the DB.

> However, the second option looks possible if the client uses temporary tables
> within a single transaction.  I.e. it starts a transaction, creates its
> temporary tables, fills/uses them to update the permanent tables, drops the
> temporary tables and then commits the transaction.
> 
> Can that work with mysqldump --single-transaction?  Note that it allows MySQL
> to forcibly abort the client's transaction if necessary, so it can be
> rerecorded in a binlog that comes after the backup.
> 
> Bacula's use of temporary tables doesn't follow this approach because it
> doesn't start a transaction before creating the table.

Bacula cannot follow this approach because, unfortunately, even as long
in the tooth and obsolete as the MyISAM storage engine is, Bacula cannot
safely assume that it is working with a transactional storage engine.


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

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