Re: MySQL backups
On Oct 15, 2007, at 18:25, Thomas Charron wrote: I know what the options are pretty much. But they're convinced that this is the right one. There's little substitute for science. Come up with your worst-case scenario and test their method. I bet it won't work, but we might be surprised, and learn something about their new kind of unix software. If it demonstrably doesn't work then can they still insist on it? If so, you're sunk and the best you can hope for is for their backup agent not to hose your system. And that's no way to run a ship. -Bill - Bill McGonigle, Owner Work: 603.448.4440 BFC Computing, LLC Home: 603.448.1668 [EMAIL PROTECTED] Cell: 603.252.2606 http://www.bfccomputing.com/Page: 603.442.1833 Blog: http://blog.bfccomputing.com/ VCard: http://bfccomputing.com/vcard/bill.vcf ___ gnhlug-discuss mailing list gnhlug-discuss@mail.gnhlug.org http://mail.gnhlug.org/mailman/listinfo/gnhlug-discuss/
Re: MySQL backups
On 10/15/07, Thomas Charron [EMAIL PROTECTED] wrote: Has anyone had experience with using CA backup solutions backing up CVS and MySQL data repositories? CA as in ARCServe? Ewww. Horrible software. Run, do not walk, away from that crap. It really concerns me that our Sysadmins are planning to do this, as they don't want to take down the MySQL or CVS servers while they do a backup. Just about anything that keeps its files open all the time is *NOT* going to work well for this. Upon a restore, best case, it will seem to the software as if the machine lost power at the time of the backup. The best case, because a backup isn't instantaneous, and so different parts of different files will get backed up at different times. Such a situation may not be recoverable. The saying about how Nobody really cares about backups, it's the *restores* that matter comes to mind here. With database servers, there are typically two approaches to solving this problem. One is to have the database server do a dump/export/whatever to disk file(s) before the backup runs, and then just backup those files. Essentially a two-stage backup. Not sophisticated, but often reliable. The other approach is to have special backup software that can speak to the running database server and suck the data out in a backup-ish way. In the payware world, such software is often called an agent (as in, Oracle agent, Sybase agent, etc.). Such don't need the disk space for the extra files, and may have other benefits. -- Ben ___ gnhlug-discuss mailing list gnhlug-discuss@mail.gnhlug.org http://mail.gnhlug.org/mailman/listinfo/gnhlug-discuss/
Re: MySQL backups
On Mon, Oct 15, 2007 at 05:09:02PM -0400, Ben Scott wrote: One is to have the database server do a dump/export/whatever to disk file(s) before the backup runs, and then just backup those files. Essentially a two-stage backup. Not sophisticated, but often reliable. This is what I do. It's slow and painful, but almost 100% reliable. My backup files are compressed raw SQL, so they can be greped, seded, perled, and restored anywhere. There's another option, if you have the right filesystem. You lock the database, take a filesystem level snapshot, unlock the DB, then back up the snapshot. That's supposed to work for Linux LVM2 and several of the commercial NAS/SAN offerings. On some systems you only need to lock the DB for a few seconds. -- Matt Brodeur RHCE [EMAIL PROTECTED] http://www.nexttime.com PGP ID: 2CFE18A3 / 9EBA 7F1E 42D1 7A43 5884 560C 73CF D615 2CFE 18A3 One difference between a man and a machine is that a machine is quiet when well oiled. pgpWf6R0xdEmK.pgp Description: PGP signature ___ gnhlug-discuss mailing list gnhlug-discuss@mail.gnhlug.org http://mail.gnhlug.org/mailman/listinfo/gnhlug-discuss/
Re: MySQL backups
On 10/15/07, Ben Scott [EMAIL PROTECTED] wrote: On 10/15/07, Thomas Charron [EMAIL PROTECTED] wrote: Has anyone had experience with using CA backup solutions backing up CVS and MySQL data repositories? CA as in ARCServe? Ewww. Horrible software. Run, do not walk, away from that crap. I truely, true wish I had some say in it, but yes, it is, and unfortunately, I'm on a boat (our server) in their IT ocean. It really concerns me that our Sysadmins are planning to do this, as they don't want to take down the MySQL or CVS servers while they do a backup. Just about anything that keeps its files open all the time is *NOT* going to work well for this. Upon a restore, best case, it will seem to the software as if the machine lost power at the time of the backup. The best case, because a backup isn't instantaneous, and so different parts of different files will get backed up at different times. Such a situation may not be recoverable. They intend to use some kinda of 'CA Agent for Open Files', which makes me cringe even more. With database servers, there are typically two approaches to solving this problem. One is to have the database server do a dump/export/whatever to disk file(s) before the backup runs, and then just backup those files. Essentially a two-stage backup. Not sophisticated, but often reliable. The other approach is to have special backup software that can speak to the running database server and suck the data out in a backup-ish way. In the payware world, such software is often called an agent (as in, Oracle agent, Sybase agent, etc.). Such don't need the disk space for the extra files, and may have other benefits. That was our suggestion. But they insist it HAS to be done without taking any services available on the system down. Specifically, for CVS, this CANNOT be done. -- -- Thomas ___ gnhlug-discuss mailing list gnhlug-discuss@mail.gnhlug.org http://mail.gnhlug.org/mailman/listinfo/gnhlug-discuss/
Re: MySQL backups
On 10/15/07, Thomas Charron [EMAIL PROTECTED] wrote: They intend to use some kinda of 'CA Agent for Open Files', which makes me cringe even more. Open file agents (OFA) are often useful on MS Windows, where files are frequently under mandatory locks. There's no advisory file locking under Windows (that I know of), so all locks are mandatory, and further, file locking is a lot more common. For example, Excel locks spreadsheet files when you open them (to prevent access collisions). But then people leave their Excel files open overnight and you can't back them up. An OFA will solve that problem. This is generally safe, because Excel only writes to the file when you hit Save (so the file is consistent on disk, despite being open and locked). Most OFAs are nothing special; they're just a way for the backup software company to extract more money from the customer. On *nix, file locks are almost always advisory, and are much less common in the first place. So an open file agent is practically a NOOP; you can almost always just back-up open files. But I'm sure CA will be happy to sell you a license for it anyway. Open file agents do *nothing* to address problems due to files on disk being in an inconsistent state when the backup runs. With MS Excel or whatever, this isn't an issue. Not so with a database. But they insist it HAS to be done without taking any services available on the system down. Specifically, for CVS, this CANNOT be done. Well, for MySQL, just do database-to-disk dumps and back those files up, like I said. I don't really know much about CVS from an admin POV, so I can't comment there. Is there a way to make a transactional copy of an entire CVS repository? -- Ben ___ gnhlug-discuss mailing list gnhlug-discuss@mail.gnhlug.org http://mail.gnhlug.org/mailman/listinfo/gnhlug-discuss/
Re: MySQL backups
On Mon, 2007-10-15 at 16:44 -0400, Thomas Charron wrote: Has anyone had experience with using CA backup solutions backing up CVS and MySQL data repositories? NO (but why should that stop me). It really concerns me that our Sysadmins are planning to do this, as they don't want to take down the MySQL or CVS servers while they do a backup. MySQL file locking, and the integrity of the CVS backup are two issues that I just don't see how a strait file based backup can work on this server. (addressing MySQL) You're right. Feed the output from sqldump into your backup. mysqldump will flush and lock each database as the database is backed up, but does not, by default, protect consistency between databases. If that's a concern, an LVM snapshot (or equivalent) might be better. The output from sqldump is a file of SQL commands to recreate the database(s). That's sometimes more useful than simply backing up the server files directly. It also compresses well and works OK when fed into rdiff. -- Lloyd Kvam Venix Corp DLSLUG/GNHLUG library http://www.librarything.com/catalog.php?view=dlslug ___ gnhlug-discuss mailing list gnhlug-discuss@mail.gnhlug.org http://mail.gnhlug.org/mailman/listinfo/gnhlug-discuss/
Re: MySQL backups
A few thoughts: - have a MySQL slave system that you can shut down for backup purposes. Doesn't have to be beefy, just enough disk space to store the DB. - mysqldump is your friend. - I think that you can sort-of hot backup MyISAM tables, and there's a hot backup utility for InnoDB. -there is a process for using innodb file per table and then locking per table and backing up the underlying tablespace files. We use mysqldump with a role account that has backup privileges on the databases that we can't easily recreate. Works pretty well though I'll change it to back up one database per file. -Mark ___ gnhlug-discuss mailing list gnhlug-discuss@mail.gnhlug.org http://mail.gnhlug.org/mailman/listinfo/gnhlug-discuss/
Re: MySQL backups
Thomas Charron wrote: Has anyone had experience with using CA backup solutions backing up CVS and MySQL data repositories? It really concerns me that our Sysadmins are planning to do this, as they don't want to take down the MySQL or CVS servers while they do a backup. MySQL file locking, and the integrity of the CVS backup are two issues that I just don't see how a strait file based backup can work on this server. There are ways (different ones, of course) to do hot backups of MySQL while it is online, but they vary with the data storage engine used. As several have said, the right technique is to backup the dumps, not the actual database files. That's like backing up the swap partition and hoping restoring it will bring the machine back to life. -- Ted Roche Ted Roche Associates, LLC http://www.tedroche.com ___ gnhlug-discuss mailing list gnhlug-discuss@mail.gnhlug.org http://mail.gnhlug.org/mailman/listinfo/gnhlug-discuss/
Re: MySQL backups
On 10/15/07, Ted Roche [EMAIL PROTECTED] wrote: Thomas Charron wrote: Has anyone had experience with using CA backup solutions backing up CVS and MySQL data repositories? There are ways (different ones, of course) to do hot backups of MySQL while it is online, but they vary with the data storage engine used. As several have said, the right technique is to backup the dumps, not the actual database files. That's like backing up the swap partition and hoping restoring it will bring the machine back to life. I know what the options are pretty much. But they're convinced that this is the right one. Sometimes, I hate IT geeks.. :-D -- -- Thomas ___ gnhlug-discuss mailing list gnhlug-discuss@mail.gnhlug.org http://mail.gnhlug.org/mailman/listinfo/gnhlug-discuss/
Re: MySQL backups
On 10/15/07, Ben Scott [EMAIL PROTECTED] wrote: On 10/15/07, Thomas Charron [EMAIL PROTECTED] wrote: Open file agents do *nothing* to address problems due to files on disk being in an inconsistent state when the backup runs. With MS Excel or whatever, this isn't an issue. Not so with a database. I didn't clarify. These backups are taking place on a Windows 2003 Server running under VMWare. My appologies for not clarifying. I was more curiouse as to if binary backup of the file done while it was locked would be bad, which I believed it was, do to past experience. But they insist it HAS to be done without taking any services available on the system down. Specifically, for CVS, this CANNOT be done. Well, for MySQL, just do database-to-disk dumps and back those files up, like I said. Yes, but if they hose my server while doing their backups I'm going to be pissed. I don't really know much about CVS from an admin POV, so I can't comment there. Is there a way to make a transactional copy of an entire CVS repository? CVS locks per file, which means that the file changes are atomic per file. Checkin in the middle of the backup, and the restore has half your checked in files, with no record of the rest. Aka, fudged up. I can copy the CVS repositories as well, but my concern is them affecting the operation of my machine. -- -- Thomas ___ gnhlug-discuss mailing list gnhlug-discuss@mail.gnhlug.org http://mail.gnhlug.org/mailman/listinfo/gnhlug-discuss/
Re: MySQL backups
On 10/15/07, Thomas Charron [EMAIL PROTECTED] wrote: These backups are taking place on a Windows 2003 Server running under VMWare. Ah. Then the OFA will likely be useful for the scenario I described. I was more curiouse as to if binary backup of the file done while it was locked would be bad, which I believed it was, do to past experience. The fact the something is locked does not inherently mean a backup of it at that time will be invalid. If all files are consistent on disk, then the backup is okay. Like my Excel example. The thing is, a hot database is not likely to be consistent on disk. There's another, similar problem in that if the backup software locks the file during backup (and file locks are the rule, rather than the exception, on Windows), and then the application needs the file during the backup, you have an upset application. The OFA should handle that, too. Yes, but if they hose my server while doing their backups I'm going to be pissed. ... my concern is them affecting the operation of my machine. Well, I don't have any recent experience with ARCServe (my past experience was enough for several lifetimes), but assuming their OFA works properly (this is a big assumption), it should not cause your system to fail during the backup. The backup it creates might not be useful, but it shouldn't hurt the running system. On 10/15/07, Thomas Charron [EMAIL PROTECTED] wrote: Sometimes, I hate IT geeks.. :-D Incompetent ones do suck a lot. And, unfortunately, Sturgeon's Law applies. -- Ben ___ gnhlug-discuss mailing list gnhlug-discuss@mail.gnhlug.org http://mail.gnhlug.org/mailman/listinfo/gnhlug-discuss/
