Hi, I've been using Bacula for a while now and I have a backup procedure in place for my MySQL databases, where I perform a full (dump) backup nightly, then incremental (bin log) backups every hour t
Author: Stephen Thompson <stephen AT seismo.berkeley DOT edu>
Date: Thu, 05 Apr 2012 14:37:27 -0700
I am not running a catalog backup in that way, but have thought about it. You're correct that the batch tables are temporary tables created so that jobs can do batch inserts of the file attributes. I
Hi Stephen, Thank you very much for your reply. I agree that it seems the creation of the batch table is not being captured, for some reason. As I think it may be useful, here's the line taken from m
Author: Stephen Thompson <stephen AT seismo.berkeley DOT edu>
Date: Thu, 05 Apr 2012 15:46:41 -0700
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...
Author: Phil Stracchino <alaric AT metrocast DOT net>
Date: Thu, 05 Apr 2012 19:08:34 -0400
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 --ski
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
Author: Konstantin Khomoutov <flatworm AT users.sourceforge DOT net>
Date: Sat, 7 Apr 2012 02:52:02 +0400
[...] [...] I don't have an answer to the problem being discussed but nonetheless I want to offer one improvement. Having password be passed to a program this way is lame since when it appears on the
Konstantin, Thank you very much for this tidbit of information - I really appreciate it. I'll be sure to build this into my backup routine, once I sort out by binary logging issues :-). 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 men
Author: Phil Stracchino <alaric AT metrocast DOT net>
Date: Tue, 10 Apr 2012 11:15:29 -0400
No, because a dump will not contain temporary tables. So if you restore a dump and a set of binlogs that contain transactions referring to temporary tables extant when you created the dump, yes, thos
Author: Martin Simmons <martin AT lispworks DOT com>
Date: Tue, 10 Apr 2012 17:27:51 +0100
Is mysql's backup procedure really that broken? Or maybe the problem is caused by Bacula misusing temporary tables (e.g. without a transaction)? __Martin -- Better than sec? Nothing is better than s
Author: Phil Stracchino <alaric AT metrocast DOT net>
Date: Tue, 10 Apr 2012 12:44:25 -0400
Why do you consider that "broken"? A temporary table has meaning and visibility only to a single database connection. In order for there to be any use or purpose to backing up temporary tables, the b
Author: Martin Simmons <martin AT lispworks DOT com>
Date: Tue, 10 Apr 2012 19:15:38 +0100
Sorry, I didn't mean mysqldump on its own. The MySQL documentation suggests that you can make a backup from the output of mysqldump plus later binlogs and then use them to restore a database, but wh
Author: Phil Stracchino <alaric AT metrocast DOT net>
Date: Tue, 10 Apr 2012 15:27:22 -0400
You shouldn't think of a temporary table as persistent DB data. Think of them instead as part of the transient state of a single ongoing transaction. Is it a reasonable expectation for a DB restore t
Author: Stephen Thompson <stephen AT seismo.berkeley DOT edu>
Date: Tue, 10 Apr 2012 13:40:46 -0700
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
Ok, firstly: sorry for not following up sooner. There have been several replies for this thread since my last reply, so thanks everyone for your input. Phil, you point out that the problem here is mo
Author: Martin Simmons <martin AT lispworks DOT com>
Date: Fri, 13 Apr 2012 18:02:51 +0100
OK, so it looks like Bacula's use of temporary tables outside a transaction is at best incompatible with "live" backups of MySQL and at worst incorrect. __Martin -- For Developers, A Lot Can Happen
Author: Martin Simmons <martin AT lispworks DOT com>
Date: Fri, 13 Apr 2012 18:14:23 +0100
Should be OK because Bacula isn't in the middle of inserting File records for that job when the before script runs. Yes, that will be a problem. Yes, it sounds like a risky approach. I think you wou
Author: Phil Stracchino <alaric AT metrocast DOT net>
Date: Fri, 13 Apr 2012 14:13:41 -0400
I'm not understanding what you think is "incorrect" here. Let's try it this way: How are you thinking that temporary tables should work, and what do you think "should happen" if you try to restore yo
Martin and Phil, thanks for your responses. I seem to have settled on the suggestion that maybe I should't worry about these temporary tables too much. As I think Phil is trying to explain; if there