-----Original message----- From: Phil Stracchino <ala...@metrocast.net> Sent: Tue 10-04-2012 20:30 Subject: Re: [Bacula-users] Bacula MySQL Catalog binlog restore To: bacula-users@lists.sourceforge.net; > On 04/10/2012 02:15 PM, Martin Simmons wrote: > > 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 > > what > > use is that if it doesn't work with temporary tables? > > > > For this to work, all transactions should either be entirely in the output > > of > > mysqldump or entirely in the later binlogs. This could probably work if > > temporary tables are used within a transaction, because hopefully mysqldump > > will wait for it to finish. It probably can't work if temporary tables are > > used without a transaction. > > 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 to be able > to restore any part of the transient state of a transaction that > happened to be running when the backup was made? Even if it could, > where would the results go? > > There's no way to resume an interrupted transaction from the middle, and > so there's no point in backing up any of its state except to roll back > anything it's already partly done. If you want to repeat the > transaction, you have to restart it from the beginning, which will > recreate any temporary tables it was using anyway. > > > -- > Phil Stracchino, CDK#2 DoD#299792458 ICBM: 43.5607, -71.355 > ala...@caerllewys.net ala...@metrocast.net p...@co.ordinate.org > Renaissance Man, Unix ronin, Perl hacker, SQL wrangler, Free Stater > It's not the years, it's the mileage. >
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 most likely to do with the fact that other jobs are running whilst the full DB backup is being run and I agree with this. Now, to me, the simple resolution to this is to assign a backup window for the catalog backup to run in, when no other backups are running. There are, however, two issues I can foresee: - The MySQL backup script is being called by a Bacula job as a run before script, so there will allways be a job 'running' whilst the catalog dump is being performed. - One backup job in particular takes approx 24-48 hrs to complete, so this will be constantly updating the catalog with file records, no matter when I schedule the full catalog backup for. I'm therefore back to square one. Whilst I understand the complications with temporary tables (and threads) that Phil has pointed out, I do agree with what Martin was suggesting that there may be a better way to use temporary tables. I don't, however, have enough MySQL knowledge to provide any further suggestions to improve this in later versions of Bacula. Stephen, yes, I am running the backup whilst other backups are running, for reasons stated above. You are right - if there were no jobs running, the temp tables wouldn't be referred to in the first place, as they would not exist. Your suggestion of keeping a 'buffer' of bin logs is interesting and this was the last thing I tried to resolve this problem with. I basically removed the '--delete-master-logs' option from the mysqldump line, so that old binary logs are not removed. (Side point: I'm not sure when the '--delete-master-logs' actually deletes the binary logs - the mysqldump docs state that it "[sends a] PURGE BINARY LOGS statement to the server __after__ performing the dump operation". This doesn't make sense to me, as anything that's created between the dump being initiated and the dump completing will be deleted from the logs and there's no guarantee it's included in the dump). So I removed the '--delete-master-logs' option, in a hope this would somehow allow me to see the CREATE TEMPORARY TABLE statements earlier in the binary logs, but it didn't. What I didn't do that you have suggested is to restore older logs first i.e. logs from before the dump, then let it work through them, skipping any duplicate key errors. My issue with this workaround though, is that I fear that it could corrupt the database in some way (unbeknown to me at the time of restoring) if I am just 'skipping errors'. To me, this all does beg the question: "Why use temporary tables in the first place?" Again, I may not have enough MySQL experience to realise this for myself yet, but it's my view at this stage, after battling this for a while now! Any further input anyone can give is greatly appreciated. Kind regards, Joe ------------------------------------------------------------------------------ Better than sec? Nothing is better than sec when it comes to monitoring Big Data applications. Try Boundary one-second resolution app monitoring today. Free. http://p.sf.net/sfu/Boundary-dev2dev _______________________________________________ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
