Thanks for your response Daniel, 1. I appreciate that InnoDB is more robust than ISAM- passes the "ACID" test.
2. > Just convert InnoDB tables to MyISAM and backup the MyISAM version. I did this on a large table- it took a while and generated lots of I/O. Multiply this by 200 databases and 1000 tables on a single server- it's a production support issue. We still need a hot backup solution that's more capable than "all or nothing." Using hot-swappable RAID with journaling file systems doesn't obviate the need for online database backups. Another option is full database replication with duplicate servers and disks. $igh... 3. > you can have many tablespaces... I don't think so. According to the docs it's one tablespace with many files and the data is comingled. On a 100GB database with 50 2GB files, what happens when one file is lost? Restore the entire system while all the databases are down? InnoDB is great but I'd like to be able to recover a single database from backups while the other databases are up and running. Enhancement Request: 1. The ability to associate a database with named tablespaces/files. 2. Given 1, the ability to backup and recover a single database, tablespace, or file set without impacting on the online availability of other databases, tablespaces, and/or files. Without this capability, the loss of one data file effectively equates to the loss of the entire database server and the mean time to recovery (MTTR) is unecessarily long. IMHO :-) -----Original Message----- From: Kiss Dániel [mailto:[EMAIL PROTECTED]] Sent: Friday, June 07, 2002 12:04 AM To: Orr, Steve; [EMAIL PROTECTED] Subject: Re: InnoDB Hot Backups... ALL OR NOTHING ??? First of all, there are many aspects of your problem. 1. The InnoDB uses transaction safe table types, and uses the log files to restore if anything goes wrong during the tsanasction. So it is almost impossible to have a permanent database error, that cannot be repaired by InnoDB itself. If MySQL crashes, you just have to restart it, and InnoDB repaires automatically all the tables containing errors. 2. In spite of the first section, its a good idea to create backups of your InnoDB tablespace, because it can happen that the hard disk you have your tablespace files fails and in a case like this you don't have anything else, just your backup files. It's a little bit difficult to save all the InnoDB tablespaces onto another backup disk, because they can be very big, although they are compressable very well, because the empty spaces inside the tablespace contain zeros. A simple solution is not to backup directly the InnoDB tablespaces. Just convert the InnoDB tables into MyISAM and backup the MyISAM version of them. 3. The third aspect is that you can have many tablespaces, not only one big. For example instead of an InnoDB initialization command in my.cnf like this innodb_data_file_path = ibdata1:2000M you can use this innodb_data_file_path = ibdata1:200M;ibdata2:200M;ibdata3:200M;..ibdata10:200M Good luck, Daniel At 15:21 2002.06.06. -0600, you wrote: >Can you backup/restore just one MySQL database with InnoDB hot backup? From >what I gather it's an all or nothing proposition. As I understand it, >there's only one "tablespace" (with any number of data files) and all >database tables of type 'InnoDB' are comingled in the one tablespace. >Therefore, if a single datafile becomes corrupt, all the databases with >InnoDB type tables are down and you have to restore everything. Is that >right? If so are there any plans to have multiple named tablespaces? > >We have a single server with 150+ databases (one for each hosted customer). >If one customer database goes down then we can restore the MYISAM type >tables without affecting the 24X7 availability for the other 149 customers. >However, if we convert tables to type InnoDB and a data file is corrupted or >lost, then all databases are down and we have to restore EVERYTHING. Is this >correct? > > >Sincere thanks in advance... > >--------------------------------------------------------------------- >Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > >To request this thread, e-mail <[EMAIL PROTECTED]> >To unsubscribe, e-mail <[EMAIL PROTECTED]> >Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
