Re: innodb in 4.1.18
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]
Re: innodb in 4.1.18
Marten Lehmann wrote: I had a lot of trouble today because the InnoDB integration in MySQL is lousy. I read the manual and worked with innodb_per_file_table. So when I shutdown mysql I should be able to delete ib_logfile0, ib_logfile1 and ibdata1, because all table-data should be stored in the .idb and .frm files. But this obviously is not the case! The ib_logfile* stores, among other things, transaction history and whatnot. If you delete the log files or they otherwise become corrupted, your InnoDB tables spaces become unusable and you have to rebuild the table space. MySQL always reports errors that it can't find the table files, although they are in the directory of the database. And additionally: If I'm removing the files from a databases and try to create a table that existed before (but now doesn't exist because the files are removed), I can't create it any more. What is the function of ib_logfile and ibdata? I expected them to store transaction data only, but the seem to store more. But documentation is The ibdata file(s) contain the table data and indexes. You need both the ibdata file(s) and the iblog file(s). The table definitions are still stored in the .frm files. At this point it sounds like you will need to delete the .frm files for the tables and recreate the innodb files. Good luck. b. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb in 4.1.18
On Mar 21, 2006, at 1:08 PM, Marten Lehmann wrote: I had a lot of trouble today because the InnoDB integration in MySQL is lousy. I read the manual and worked with innodb_per_file_table. So when I shutdown mysql I should be able to delete ib_logfile0, ib_logfile1 and ibdata1, because all table-data should be stored in the .idb and .frm files. But this obviously is not the case! MySQL always reports errors that it can't find the table files, although they are in the directory of the database. And additionally: If I'm removing the files from a databases and try to create a table that existed before (but now doesn't exist because the files are removed), I can't create it any more. What is the function of ib_logfile and ibdata? I expected them to store transaction data only, but the seem to store more. But documentation is very sparse on that. This is spelled out pretty clearly in the manual: 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. right from the section on using innodb_file_per_table: http://dev.mysql.com/doc/refman/4.1/en/multiple-tablespaces.html And as others have said, you always need the logfiles. Good luck, Ware -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb in 4.1.18
Hello, This is spelled out pretty clearly in the manual: 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. 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. 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. Regards Marten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb in 4.1.18
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). 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). b. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
