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
