Bill Adams wrote:
InnoDB always needs the shared tablespace because it puts its
internal data dictionary and undo logs there. The .ibd files are
not sufficient for InnoDB to operate.
well, thats what I found before. But it doesn't explain why InnoDB
does need a logfile even when all transactions are committed or
rolled back and mysql is shut down.
More is stored in the innodb log files besides a strict log of events.
It does explain, that I can use innodb_file_per_table which gives the
impression that with that option a shared ibdata isn't required. Or
maybe it is required during runtime, but not for a backup.
You might be able to set e.g. "innodb_data_file_path = ibdata:0M" but
in any event the innodb log files track the existence of the different
ibdata files (size, if they have been formatted, etc).
You always have to have the "central" tablespace, whether you are using
file per table or not. First and foremost, for your backup purposes, *it
holds data dictionary information* (as well as within the frm files
within each database), plus undo/rollback segment space and the adaptive
hash index - even when using file per table.
So with InnoDB it is not possible to shutdown the database server,
backup the files and maybe use them on another server if the other
server already has an ibdata and ib_logfile? Do I always have to do a
mysqldump then? That takes much longer.
You can. But direct file system operations on MySQL (really any
database) are dangerous. Same thing with why IMHO you should use
"PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 31 DAY);" rather
than just deleting your binary logs directly (if you are using
replication and all that). If you are going to copy the InnoDB files
you probably need to have an exact setup. And as I am sure you have
figured out you have to back up the .idb files and the innodb log
files at the same time.
AFAIK, You cannot take the .idb file from one server and copy it to
another server and have it just work (like you can with the MyISAM
files).
Correct, to move any InnoDB tables between machines you need:
o All ib data files
o All ib logfiles
o All related database directories and frm files
o Your my.cnf/ini
http://dev.mysql.com/doc/refman/5.0/en/innodb-backup.html
Regards
Mark
--
Mark Leith, Support Engineer
MySQL AB, Worcester, England, www.mysql.com
Are you MySQL certified? www.mysql.com/certification
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]