On 01/17/13 04:55, Uwe Schuerkamp wrote:
> On Thu, Jan 17, 2013 at 10:40:26AM +0100, Sven Gehr wrote:
>> Hi@all,
>>
>> is it possible to backu databases e.g. mysql, pgsql (on other hosts)
>> with bacula online?
>
>
> Yes and no. If there are no jobs running you can set the db to read
> only, but bacula will barf the next time it tries to insert something
> into the tables.
>
> A frequently quoted method is creating an lvm snapshot and using a
> tool like "mydumper" to create the backup. I don't know about postgres
> as we're using mariadb exclusively with bacula ATM.
This seems a little of a confused mixture.
First, yes, you totally can back up MySQL DBs online, provided you do it
correctly. "Correctly" means, in general, one of two things: a
transactional backup or a snapshot.
A transactional backup can be done with any of several tools --
mysqldump, mydumper, Percona XtraBackup, MySQL Enterprise Backup -- *as
long as you are using InnoDB tables*. (And at this point in time,
unless you're using one of the small handful of MyISAM table features
not yet supported by InnoDB, you have no excuse for NOT using all
InnoDB.) If you're using mysqldump, which is old and at this point
pretty much the village idiot of MySQL backup tools, you'll need to use
--single-transaction --skip-lock-tables when running it. The other
tools mentioned will automatically just Do The Right Thing.
For a snapshot backup, you can issue a FLUSH TABLES WITH READ LOCK to
quiesce all of the MyISAM tables, wait for it to return, snapshot the
data directory, release the lock, and then mount the snapshot and back
it up. We have found at my company that LVM snapshots actually do not
work very well for this purpose, because they are too slow and require
too much disk space. On the other hand, ZFS snapshots work extremely
well, as they are virtually instant and require no reserved disk space.
If you have to restore, it will be fast compared to reloading a dump,
but you will have to do an InnoDB recovery, so make sure you back up
both binary logs (if any) and InnoDB write-ahead logs.
Either way, you do not back up the live data. Trying to do that is a
waste of time, because your backup will be inconsistent, because the
database will be changing as you back it up. There's no point in
backing up the live data files. Don't bother to do it. It's a waste of
time and space. Perform a consistent transactional dump and back up the
dump, or perform a snapshot and back up the snapshot.
PostgreSQL has a tool called pg_dumpall that is conceptually similar to
mysqldump and mydumper.
One last footnote: *SOLELY* setting MySQL read-only does NOT guarantee
a consistent backup. You must FLUSH TABLES, and even then you're still
not 100% safe on InnoDB.
--
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.
------------------------------------------------------------------------------
Master Visual Studio, SharePoint, SQL, ASP.NET, C# 2012, HTML5, CSS,
MVC, Windows 8 Apps, JavaScript and much more. Keep your skills current
with LearnDevNow - 3,200 step-by-step video tutorials by Microsoft
MVPs and experts. ON SALE this month only -- learn more at:
http://p.sf.net/sfu/learnmore_122712
_______________________________________________
Bacula-users mailing list
Bacula-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users
