On 02/06/2012 02:45 PM, Phil Stracchino wrote: > 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. >
This was one of my ideas to try, though I'm still wondering -- If my slave does fall behind my production while dumping DB, because a long-running job is active during the dump, will that dump of the DB simply be missing information about that running job, or will anything else in the Catalog be affected? Because ultimately, if I need to restore my Catalog from backup, I want to be able to search and restore from all completed jobs (the acceptable omission being the job running during the dump, because it wasn't complete at the time!) as well as continue to run future backup jobs as normal with that restored Catalog. > 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...? > Thanks, I look into this. Without the locks, but dumping while a job is running, this still begs the question above -- Am I just putting the data associated with the running job (concurrent to the dump) at risk, or is there any risk that my Catalog will go "screwy" in a more broad fashion. For instance, a counter that's not incremented ...or some row that's written upon job completion that, since the dump was made before the 'long-running' job completed, causing more general mayhem than just missing records for the uncompleted job. I don't know the database layout, or logic of what's written to the database and when, to understand what risk I am at with Route 1 or Route 2. > 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.) Nope, not an option. > > thanks! Stephen -- 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 ------------------------------------------------------------------------------ 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@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
