Hi! A hot backup (= online backup) means backing up your database without locking anything for more than a few milliseconds.
If you back up a massive, fast changing, database with mysqldump --single-transaction, then -- at least in some theoretical settings -- InnoDB can run out of space in the tablespace, because it cannot purge history in the tablespace before that big mysqldump ends. If you use InnoDB Hot Backup http://www.innodb.com/hotbackup.html, the only limit is the size of the disk(s) where you take the backup. The size of InnoDB's transaction logs, ib_logfiles, does NOT limit the size of transactions you can run during the backup procedure. InnoDB Hot Backup (ibbackup) works like similar tools for Oracle. It copies the tablespace, page by page, and at the same time archives the log that InnoDB writes during the backup process to its log files. The archived log becomes the file ibbackup_logfile in your backup. ibbackup does not communicate with mysqld in any way during the backup process. It does not set any locks. It just reads the data and log files. When we want to take the backup into use, ibbackup applies the archived log to the data files in the backup and in that way rolls the backup forward to a consistent single point in time. When Nils talks about setting table locks, he probably means a new Perl script innobackup http://www.innodb.com/hotbackup.html#innobackup_perl_script. innobackup calls ibbackup and takes a backup of both InnoDB and MyISAM type tables, as well as the .frm files. To back up MyISAM tables it has to call FLUSH TABLES WITH READ LOCK at the end of the backup run, because there is no hot backup procedure available for MyISAM tables. If the MyISAM tables are small, like the 'user' and 'host' system tables in the 'mysql' database, then the locked phase only lasts a couple of seconds, and we can call also innobackup essentially a 'hot backup' tool. Note that mysqldump takes a 'logical' backup of your database, because it dumps the rows into a file in a human-readable form. ibbackup, on the other hand, takes a binary backup of your database. Even if you use ibbackup, you should sometimes make logical dumps because you can easier check that data in a human-readable file is not corrupt. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for MySQL ----- Original Message ----- From: "Nils Valentin" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]>; "P Srinivasulu" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Wednesday, August 27, 2003 7:47 PM Subject: Re: Mysql Online Backup. 2003年 8月 28日 木曜日 01:43、Nils Valentin さんは書きました: > Hi Chris, > > 2003年 8月 28日 木曜日 00:54、Chris Nolan さんは書きました: > > A very good question! One which I will attempt to provide a half-decent > > answer for. > > > > Trying to execute a transaction that involves grabbing massive amounts of > > data from various tables while still allowing updates to be committed can > > be tricky. At best, this means performance problems. Please don't get me > > wrong - MySQL is the fastest database on the planet (and it looks to only > > get faster, with additional query caches, better designed caches and a > > helper thread architecture in the works) but there are certain things > > that are just hard to do quickly, even if you are the demigods that work > > at Innobase Oy and MySQL AB. > > > > The advantage of other methods is that you avoid all sorts of nasty > > locking on various parts of your database. The advantage of the Hot Copy > > product that Innobase Oy sell is that it doesn't place any locks on your > > InnoDB table space when it runs. > > You made an interesting point there with the locking mechanism. I double > checked the innodb homepage. > > http://www.innodb.com/hotbackup.html > > While it says at the top as you stated no locks are written it also says > under 3) how it locks and unlocks the tables. > > I guess thats best explained by Heikki ;-) - I copied her. > > Best regards > > Nils Valentin > Tokyo/Japan > > > In summary, it's not a question as to whether the current methods work, > > it's a question as to how well suited they are to your needs. For mine > > they work well, but if I was processing 600 queries / second, I think I'd > > want ibcopy to help me out. > > > > Regards, > > > > Chris > > > > <quote who="P Srinivasulu"> > > > > > If the backup solutions are there already, why is that a future > > > requirement for Mysql? > > > > > > Thanks, > > > Srinivasulu. > > > > > >>>> Chris Nolan <[EMAIL PROTECTED]> 8/27/2003 6:41:27 PM >>> > > > > > > Hi! > > > > > > You can currently do this with InnoDB with mysqldump (look at the > > > --single-transaction switch). I use it for backing up a 12 GB mail > > > database without problems. > > > > > > Regards, > > > > > > Chris > > > > > > P.S. Keep up the great work over there at Novell! Tell us where to send > > > > > > the expensive bottles of alcohol for when you help put SCO out of their > > > > > > misery! > > > > > > P Srinivasulu wrote: > > >>Hi All, > > >> > > >>The link below says that online backup functionality will be provided > > >>in Mysql-5.1. I think its already there in the form of mysqlhotcopy. > > > > > > Is > > > > > >>Mysql going to extend the functionality of mysqlhotcopy so that it > > > > > > works > > > > > >>for Innodb tables also? or they are going to provide independent > > > > > > online > > > > > >>backup functionality. Please clarify me. > > >> > > >>http://www.mysql.com/doc/en/TODO_MySQL_5.1.html > > >> > > >>Thanks, > > >>Srinivasulu. -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
