MySQL Forums
Forum List  »  InnoDB

Re: Importing Data with Single .ibd file
Posted by: Marko Mäkelä
Date: September 10, 2014 07:53AM

The main differences between MyISAM and InnoDB are the redo log (for crash-safety) and the undo log (for transactions).

InnoDB tablespaces will contain references to both the redo log and the undo log. These references have to be reset when importing a tablespace, so that a tablespace will not be pointing to what the importing instance would be considering ‘the future’. This is even the case when you got a ‘clean export’.

If you just copy an *.ibd file while the server is running, then you should also copy and apply the InnoDB redo log (ib_logfile*). That is what MySQL Enterprise Backup does. This would result in a table where each index tree is consistent. The table as a whole could still be inconsistent. This is because row operations are performed in each index separately. If the backup finished while some transaction had updated a few indexes of the table, but not all indexes yet, the indexes would be inconsistent with each other. That is where the undo log comes to play.

As far as I know, the only currently available safe way to import an InnoDB table is to use MySQL 5.6 and lock the table during the export. This will ensure that there are no concurrent transactions that are modifying the table. It will also flush any dirty pages from the buffer pool to the table, and merge the change buffer to the secondary indexes. After such an export, an import will be fine. (If you are using MEB, it should be fine to restore a backup to an intermediate MySQL instance and then do the export from there. In that way, you will not break any references to the undo logs or the redo log.)

The main question seems to be: Why does MySQL 5.6 use a *.cfg file in addition to the *.frm file when importing an InnoDB table? The answer is that there are two data dictionaries, and the *.frm file does not contain everything that InnoDB needs. We are working on simplifying this and making this more reliable.

Marko Mäkelä

Options: ReplyQuote


Subject
Views
Written By
Posted
2666
September 06, 2014 04:12AM
1392
September 07, 2014 12:06PM
Re: Importing Data with Single .ibd file
5347
September 10, 2014 07:53AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.