Re: Backups with MySQL/InnoDB
On Sunday, May 07, 2006 6:14 PM, Greg 'groggy' Lehey wrote: > On Sunday, 7 May 2006 at 9:27:31 -0700, Robert DiFalco wrote: >> What are people doing for backups on very large MySQL/InnoDB >> databases? Say for databases greater than 200 GB. Curious about >> the backup methods, procedures, and frequency. > > A second question, but not for the first time: how would you *like* to > do backups if you had the choice? We're currently in the final stages > of the design of an online backup solution, and in the near future I'll > publish the specs. I won't mention them now to avoid influencing you, > but now's the time to speak up if you want something specific. On Monday, 8 May 2006 at 8:15:17 -0700, paul rivers wrote: > > I would suggest looking at the functionality of Microsoft SQL Server > or Sybase backups. It's extremely nice from an admin point of view, > and certainly covers all of what Robert mentions. Yes, from an administrative perspective we're trying to make something that feels "intuitive", and particularly the Microsoft approach seems a good starting point for this aspect. If you have a pet feature not discussed below, let me know. On Sunday, May 07, 2006 9:30 PM, Robert DiFalco wrote: > > Fast, incremental, compressed, and no max-size limitations. Must be > transaction safe; able to run while transactions are going on > without including any started after the backup began; the usual > stuff. We're certainly planning incremental backups, but they probably won't be in the first release. We don't plan any size limitations (this is a streaming backup), and it will be transaction-safe (statement-safe for MyISAM) and online (i.e. concurrently with normal processing). Compression is a different issue. We haven't considered it so far, and though it's desirable, I don't see why we can't get an external program to do this (bzip2 or gzip, for example; the choice depends on your personal tradeoffs between time and space). On Monday, 8 May 2006 at 15:55:07 -0300, Daniel da Veiga wrote: > On 5/8/06, David Hillman wrote: >> On May 7, 2006, at 11:29 PM, Robert DiFalco wrote: >>> Fast, incremental, compressed, and no max-size limitations. Must be >>> transaction safe; able to run while transactions are going on without >>> including any started after the backup began; the usual stuff. >> >>Incremental, transaction safe, compressed, fast, no-max-size. >> ( In order ) >> > > Those are certainly the most important features (and I'll be glad to > beta-test it ;) I'll add: manage multiple servers, deal with > replication (using the replicated server as a backup would be cool), > manage binlogs (date and purge) and be compatible with version 4.1 and > above (I don't plan on using the 5 version any time soon). The component we're working on at the moment is the streaming online backup API. Basically you issue an SQL command BACKUP DATABASE, and it outputs a data stream that you can point at your tape drive, to a disk, or across the network to something like VERITAS. We're very conscious of the multiple server issue, but it's going to have to wait until we can back up one server properly. Dealing with replication is a special case of multiple servers, so that will wait too. We will backup the binlog, though, and our current thinking is to use it for incremental backups, though this may change. On Monday, 8 May 2006 at 16:09:23 -0500, Rich Duzenbury wrote: > > In addition, I'd like to see a configurable option for how often to > take a full and or incremental backups, a mechanism to age the > backups and drop them after a certain amount of time. For example, > I want a simple way to keep four weekly near line backups each > month, then age off and keep one backup for each of the previous 11 > months, and then just one backup per year. This would be about 1T > of data for us. This is also another aspect of the backup solution we're working on. I'll put it down on the wish list. On Tuesday, 9 May 2006 at 7:18:28 +1000, David Logan wrote: > Hi Greg, > > Maybe similar features to that of bacula (my current backup software of > choice for my wifes business servers). This is a very comprehensive open > source solution that has many of the features requested below. eg. > multiple servers, pooling, aging etc. It is a good example of what my > own requirements would be. I don't know Bacula, but I suppose I should investigate it. Do you know anybody in the project? > Is the intention to have a MySQL type plugin? eg. will it have an > api that will be open to other backup solutions being able to > utilise what will be written? Yes, this is very much the intention. It's the API that we're defining now. We've been talking to Zmanda (http://www.zmanda.com/), who are interested in extending amanda with MySQL plugins, and we'd be more than happy for others to join in. > It would be nice to be able to utilise a standard XBSA solution giving > access to the database from any one of the major enterp
RE: Backups with MySQL/InnoDB
Hi Greg, Maybe similar features to that of bacula (my current backup software of choice for my wifes business servers). This is a very comprehensive open source solution that has many of the features requested below. eg. multiple servers, pooling, aging etc. It is a good example of what my own requirements would be. Is the intention to have a MySQL type plugin? eg. will it have an api that will be open to other backup solutions being able to utilise what will be written? It would be nice to be able to utilise a standard XBSA solution giving access to the database from any one of the major enterprise backup solutions, eg. Legato Networker, Veritas Netbackup, HP Dataprotector etc. etc. This would allow an enormously simple and straightforward integration into many of the existing corporate solutions that exist around the world. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Duzenbury, Rich [mailto:[EMAIL PROTECTED] Sent: Tuesday, 9 May 2006 6:39 AM To: mysql@lists.mysql.com Subject: RE: Backups with MySQL/InnoDB > -Original Message- > From: Daniel da Veiga [mailto:[EMAIL PROTECTED] > Sent: Monday, May 08, 2006 1:55 PM > To: mysql@lists.mysql.com > Subject: Re: Backups with MySQL/InnoDB > > On 5/8/06, David Hillman <[EMAIL PROTECTED]> wrote: > > On May 7, 2006, at 11:29 PM, Robert DiFalco wrote: > > > Fast, incremental, compressed, and no max-size > limitations. Must be > > > transaction safe; able to run while transactions are going on > > > without including any started after the backup began; the > usual stuff. > > > > Incremental, transaction safe, compressed, fast, no-max-size. > > ( In order ) > > > > Those are certainly the most important features (and I'll be > glad to beta-test it ;) I'll add: manage multiple servers, > deal with replication (using the replicated server as a > backup would be cool), manage binlogs (date and purge) and be > compatible with version 4.1 and above (I don't plan on using > the 5 version any time soon). > > > -- > > David Hillman > > LiveText, Inc > > 1.866.LiveText x235 > > > > > > > In addition, I'd like to see a configurable option for how often to take a full and or incremental backups, a mechanism to age the backups and drop them after a certain amount of time. For example, I want a simple way to keep four weekly near line backups each month, then age off and keep one backup for each of the previous 11 months, and then just one backup per year. This would be about 1T of data for us. It would then be really sweet to be able to say 'restore a full backup of x database as of April 2, 2005 at 8:42 am' and have it create a new instance on a user defined port, then restore the closest previous full, then apply the binlogs up to the correct point in time. Thanks. Regards, Rich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Backups with MySQL/InnoDB
> -Original Message- > From: Daniel da Veiga [mailto:[EMAIL PROTECTED] > Sent: Monday, May 08, 2006 1:55 PM > To: mysql@lists.mysql.com > Subject: Re: Backups with MySQL/InnoDB > > On 5/8/06, David Hillman <[EMAIL PROTECTED]> wrote: > > On May 7, 2006, at 11:29 PM, Robert DiFalco wrote: > > > Fast, incremental, compressed, and no max-size > limitations. Must be > > > transaction safe; able to run while transactions are going on > > > without including any started after the backup began; the > usual stuff. > > > > Incremental, transaction safe, compressed, fast, no-max-size. > > ( In order ) > > > > Those are certainly the most important features (and I'll be > glad to beta-test it ;) I'll add: manage multiple servers, > deal with replication (using the replicated server as a > backup would be cool), manage binlogs (date and purge) and be > compatible with version 4.1 and above (I don't plan on using > the 5 version any time soon). > > > -- > > David Hillman > > LiveText, Inc > > 1.866.LiveText x235 > > > > > > > In addition, I'd like to see a configurable option for how often to take a full and or incremental backups, a mechanism to age the backups and drop them after a certain amount of time. For example, I want a simple way to keep four weekly near line backups each month, then age off and keep one backup for each of the previous 11 months, and then just one backup per year. This would be about 1T of data for us. It would then be really sweet to be able to say 'restore a full backup of x database as of April 2, 2005 at 8:42 am' and have it create a new instance on a user defined port, then restore the closest previous full, then apply the binlogs up to the correct point in time. Thanks. Regards, Rich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backups with MySQL/InnoDB
On 5/8/06, David Hillman <[EMAIL PROTECTED]> wrote: On May 7, 2006, at 11:29 PM, Robert DiFalco wrote: > Fast, incremental, compressed, and no max-size limitations. Must be > transaction safe; able to run while transactions are going on without > including any started after the backup began; the usual stuff. Incremental, transaction safe, compressed, fast, no-max-size. ( In order ) Those are certainly the most important features (and I'll be glad to beta-test it ;) I'll add: manage multiple servers, deal with replication (using the replicated server as a backup would be cool), manage binlogs (date and purge) and be compatible with version 4.1 and above (I don't plan on using the 5 version any time soon). -- David Hillman LiveText, Inc 1.866.LiveText x235 -- Daniel da Veiga Computer Operator - RS - Brazil -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V- PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++ --END GEEK CODE BLOCK-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backups with MySQL/InnoDB
On May 7, 2006, at 11:29 PM, Robert DiFalco wrote: Fast, incremental, compressed, and no max-size limitations. Must be transaction safe; able to run while transactions are going on without including any started after the backup began; the usual stuff. Incremental, transaction safe, compressed, fast, no-max-size. ( In order ) -- David Hillman LiveText, Inc 1.866.LiveText x235
RE: Backups with MySQL/InnoDB
Fast, incremental, compressed, and no max-size limitations. Must be transaction safe; able to run while transactions are going on without including any started after the backup began; the usual stuff. -Original Message- From: Greg 'groggy' Lehey [mailto:[EMAIL PROTECTED] Sent: Sunday, May 07, 2006 6:14 PM To: Robert DiFalco Cc: mysql@lists.mysql.com Subject: Re: Backups with MySQL/InnoDB On Sunday, 7 May 2006 at 9:27:31 -0700, Robert DiFalco wrote: > What are people doing for backups on very large MySQL/InnoDB databases? > Say for databases greater than 200 GB. Curious about the backup > methods, procedures, and frequency. A second question, but not for the first time: how would you *like* to do backups if you had the choice? We're currently in the final stages of the design of an online backup solution, and in the near future I'll publish the specs. I won't mention them now to avoid influencing you, but now's the time to speak up if you want something specific. Greg -- Greg Lehey, Senior Software Engineer MySQL AB, http://www.mysql.com/ Echunga, South Australia Phone: +61-8-8388-8286 Mobile: +61-418-838-708 Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backups with MySQL/InnoDB
On Sunday, 7 May 2006 at 9:27:31 -0700, Robert DiFalco wrote: > What are people doing for backups on very large MySQL/InnoDB databases? > Say for databases greater than 200 GB. Curious about the backup methods, > procedures, and frequency. A second question, but not for the first time: how would you *like* to do backups if you had the choice? We're currently in the final stages of the design of an online backup solution, and in the near future I'll publish the specs. I won't mention them now to avoid influencing you, but now's the time to speak up if you want something specific. Greg -- Greg Lehey, Senior Software Engineer MySQL AB, http://www.mysql.com/ Echunga, South Australia Phone: +61-8-8388-8286 Mobile: +61-418-838-708 Are you MySQL certified? http://www.mysql.com/certification/ pgpTiC6AScuNm.pgp Description: PGP signature
Backups with MySQL/InnoDB
What are people doing for backups on very large MySQL/InnoDB databases? Say for databases greater than 200 GB. Curious about the backup methods, procedures, and frequency.
