On 02/06/2012 05:02 PM, Stephen Thompson wrote:
> So, my question is whether anyone had any ideas about the feasibility of 
> getting a backup of the Catalog while a single "long-running" job is 
> active?  This could be in-band (database dump) or out-of-band (copy of 
> database directory on filesystem or slave database server taken 
> offline).  We are using MySQL, but would not be opposed to switching to 
> PostGRES if it buys us anything in this regard.
> 
> What I wonder specifically (in creating my own solution) is:
> 1) If I backup the MySQL database directory, or sync to a slave server 
> and create a dump from that, am I simply putting the active 
> "long-running" job records at risk of being incoherent, or am I risking 
> the integrity of the whole Catalog in doing so?
> 2) If I attempt a dump of the MySQL catalog and lock the tables while 
> doing so, what will the results be to the active "long-running" job? 
> Will it crap out or simply pause and wait for database access when it 
> needs to read/write to the database?  And if so, how long will it wait?

Stephen,
Three suggestions here.

Route 1:
Set up a replication slave and perform your backups from the slave.  If
the slave falls behind the master while you're dumping the DB, you don't
really care all that much.  It doesn't impact your production DB.

Route 2:
If you're not using InnoDB in MySQL, you should be by now.  So look into
the --skip-opt and --single-transaction options to mysqldump to dump all
of the transactional tables consistently without locking them.  Your
grant tables will still need a read lock, but hey, you weren't planning
on rewriting your grant tables every day, were you...?

Route 3:
Look into an alternate DB backup solution like mydumper or Percona
XtraBackup.

Route 4:
Do you have the option of taking a snapshot of your MySQL datadir and
backing up the snapshot?  This can be viable if you have a small DB and
fast copy-on-write snapshots.  (It's the technique I'm using at the
moment, though I'm considering a switch to mydumper.)


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

------------------------------------------------------------------------------
Try before you buy = See our experts in action!
The most comprehensive online learning library for Microsoft developers
is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3,
Metro Style Apps, more. Free future releases when you subscribe now!
http://p.sf.net/sfu/learndevnow-dev2
_______________________________________________
Bacula-users mailing list
Bacula-users AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users