Re: [Bacula-users] save DB's online
For Postgres the Bacula wiki has a nice description that uses the archive log mode & bpipe. I think that gets along online well. For MySQL I've also read that some people use dedicated slaves for the backups. Imho that's a nice method if you have "more than a little" of data to safe and no downtime. Greets, Florian 2013/1/18 K. M. Peterson > What do you mean by "online"? > > In some environments, this is a synonym for "hot", so I interpret your > question as whether Bacula has any particular specialized interface into > any of these db engines that allows it to copy data in a consistent state. > > The answer is "no". What I think most of are doing is using the db copy > or dump functionality or LVM (and similar technology) snapshots. > > _KMP > > > On Thursday, January 17, 2013, Sven Gehr wrote: > >> Hi@all, >> >> is it possible to backu databases e.g. mysql, pgsql (on other hosts) >> with bacula online? >> >> -- >> Viele Grüsse >> >> Sven Gehr >> >> -- >> Viele Grüsse >> >> Sven Gehr >> >> >> -- >> 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 >> > > > -- > Master HTML5, CSS3, ASP.NET, MVC, AJAX, Knockout.js, Web API and > much more. Get web development skills now with LearnDevNow - > 350+ hours of step-by-step video tutorials by Microsoft MVPs and experts. > SALE $99.99 this month only -- learn more at: > http://p.sf.net/sfu/learnmore_122812 > ___ > Bacula-users mailing list > Bacula-users@lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/bacula-users > > -- the purpose of libvirt is to provide an abstraction layer hiding all xen features added since 2006 until they were finally understood and copied by the kvm devs. -- 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. SALE $99.99 this month only -- learn more at: http://p.sf.net/sfu/learnmore_122912___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] save DB's online
What do you mean by "online"? In some environments, this is a synonym for "hot", so I interpret your question as whether Bacula has any particular specialized interface into any of these db engines that allows it to copy data in a consistent state. The answer is "no". What I think most of are doing is using the db copy or dump functionality or LVM (and similar technology) snapshots. _KMP On Thursday, January 17, 2013, Sven Gehr wrote: > Hi@all, > > is it possible to backu databases e.g. mysql, pgsql (on other hosts) > with bacula online? > > -- > Viele Grüsse > > Sven Gehr > > -- > Viele Grüsse > > Sven Gehr > > > -- > 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 > -- Master HTML5, CSS3, ASP.NET, MVC, AJAX, Knockout.js, Web API and much more. Get web development skills now with LearnDevNow - 350+ hours of step-by-step video tutorials by Microsoft MVPs and experts. SALE $99.99 this month only -- learn more at: http://p.sf.net/sfu/learnmore_122812___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] save DB's online
On Thu, Jan 17, 2013 at 08:52:32AM -0500, Phil Stracchino wrote: > On 01/17/13 08:33, Uwe Schuerkamp wrote: > > Could we expect to see better db performance by moving to innodb or > > one of MariaDB's fancy new backends? I'm especially interested in > > improving volume recycle times which can be quite long with our setup > > (200GB file table). the DELETE from File where JobId in (.) can > > take a few hours sometimes. > > InnoDB in general performs considerably better than MyISAM, especially > on current MySQL branches. Percona claims that their XtraDB engine has > a slight performance edge over InnoDB. I don't have any direct > experience with MariaDB yet. > > MyISAM can perform reasonably in an almost-all-read situation, but on > any modern-scale DB, as soon as you start throwing any significant > percentage of writes into the query mix MyISAM performance completely > tanks. I've seen all-MyISAM customer DBs at a complete standstill from > write-contention bottlenecks. > Well, I guess I'll have to stop backups for a week then and migrate the db over to innodb... or find some clever way involving a myisam master / innodb slave setup. I'll do some reading on the web, thanks! Uwe -- NIONEX --- Ein Unternehmen der Bertelsmann SE & Co. KGaA -- 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
Re: [Bacula-users] save DB's online
On 01/17/13 08:33, Uwe Schuerkamp wrote: > Could we expect to see better db performance by moving to innodb or > one of MariaDB's fancy new backends? I'm especially interested in > improving volume recycle times which can be quite long with our setup > (200GB file table). the DELETE from File where JobId in (.) can > take a few hours sometimes. InnoDB in general performs considerably better than MyISAM, especially on current MySQL branches. Percona claims that their XtraDB engine has a slight performance edge over InnoDB. I don't have any direct experience with MariaDB yet. MyISAM can perform reasonably in an almost-all-read situation, but on any modern-scale DB, as soon as you start throwing any significant percentage of writes into the query mix MyISAM performance completely tanks. I've seen all-MyISAM customer DBs at a complete standstill from write-contention bottlenecks. -- 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
Re: [Bacula-users] save DB's online
On Thu, Jan 17, 2013 at 08:20:48AM -0500, Phil Stracchino wrote: > 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. > Agreed, I forgot that important step in my previous email. Our method right now involves flushing the tables, setting the read lock, rsyncing the data files to a separate directory, then enabling write access to the db while tarring / lzop'ing the new directory in the background. Naturally we only do this when there are no backup jobs running which is easy to determine using a "stat dir" echo'ed to bconsole before the database backup starts. Could we expect to see better db performance by moving to innodb or one of MariaDB's fancy new backends? I'm especially interested in improving volume recycle times which can be quite long with our setup (200GB file table). the DELETE from File where JobId in (.) can take a few hours sometimes. Cheers, Uwe -- NIONEX --- Ein Unternehmen der Bertelsmann SE & Co. KGaA -- 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
Re: [Bacula-users] save DB's online
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
Re: [Bacula-users] save DB's online
Zitat von Sven Gehr : > Hi@all, > > is it possible to backu databases e.g. mysql, pgsql (on other hosts) > with bacula online? There are many different possibilities: - Use the dump utility of the DB to get a consistent dump to backup maybe with a ClientRunBeforeJob - Use the DB provided possibillities to set the DB in "Backup-Mode" and let Bacula simply save the files - Use the Enterprise Plugins (http://www.baculasystems.com/products/bacula-enterprise-plugins/postgresql-plugin) which provide one or both of the above in an easy way Regards Andreas -- 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
Re: [Bacula-users] save DB's online
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? > > -- > Viele Grüsse > > Sven Gehr > 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. Cheers, Uwe -- NIONEX --- Ein Unternehmen der Bertelsmann SE & Co. KGaA -- 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
