InnoDB: table

[lihongjie0209]

表结构

MySQL stores data dictionary information for tables in .frm files in database directories. Unlike other MySQL storage engines, InnoDB also encodes information about the table in its own internal data dictionary inside the system tablespace. When MySQL drops a table or a database, it deletes one or more .frm files as well as the corresponding entries inside the InnoDB data dictionary. You cannot move InnoDB tables between databases simply by moving the .frm files.

frm 文件存储一份 系统表空间存储一份

[lihongjie0209]

表数据

An InnoDB table and its indexes can be created in the system tablespace, in a file-per-table tablespace, or in a general tablespace. 1. When innodb_file_per_table is enabled, which is the default, an InnoDB table is implicitly created in an individual file-per-table tablespace.

2. Conversely, when innodb_file_per_table is disabled, an InnoDB table is implicitly created in the InnoDB system tablespace.
3. To create a table in a general tablespace, use CREATE TABLE ... TABLESPACE syntax.

[lihongjie0209]

innodb_file_per_table 表导入和导出

This example demonstrates how to import a regular non-partitioned table that resides in a file-per-table tablespace.

1. On the destination instance, create a table with the same definition as the table you intend to import. (You can obtain the table definition using SHOW CREATE TABLE syntax.) If the table definition does not match, a schema mismatch error is reported when you attempt the import operation.

   mysql> USE test;
   mysql> CREATE TABLE t1 (c1 INT) ENGINE=INNODB;

2. On the destination instance, discard the tablespace of the table that you just created. (Before importing, you must discard the tablespace of the receiving table.)

   mysql> ALTER TABLE t1 DISCARD TABLESPACE;

3. On the source instance, run FLUSH TABLES ... FOR EXPORT to quiesce the table you intend to import. When a table is quiesced, only read-only transactions are permitted on the table.

   mysql> USE test;
   mysql> FLUSH TABLES t1 FOR EXPORT;

   FLUSH TABLES ... FOR EXPORT ensures that changes to the named table have been flushed to disk so that a binary table copy can be made while the server is running. When FLUSH TABLES ... FOR EXPORT is run, InnoDB generates a .cfg metadata file in the schema directory of the table. The .cfg file contains metadata that is used for schema verification during the import operation.

4. Copy the .ibd file and .cfg metadata file from the source instance to the destination instance. For example:

   shell> scp /path/to/datadir/test/t1.{ibd,cfg} destination-server:/path/to/datadir/test

   The .ibd file and .cfg file must be copied before releasing the shared locks, as described in the next step.

   Note

   If you are importing a table from an encrypted tablespace, InnoDB generates a .cfp file in addition to a .cfg metadata file. The .cfp file must be copied to the destination instance together with the .cfg file. The .cfp file contains a transfer key and an encrypted tablespace key. On import, InnoDB uses the transfer key to decrypt the tablespace key. For related information, see Section 14.14, “InnoDB Data-at-Rest Encryption”.

5. On the source instance, use UNLOCK TABLES to release the locks acquired by the FLUSH TABLES ... FOR EXPORT statement:

   mysql> USE test;
   mysql> UNLOCK TABLES;

6. On the destination instance, import the tablespace:

   mysql> USE test;
   mysql> ALTER TABLE t1 IMPORT TABLESPACE;

实现原理

Internals

The following information describes internals and messages written to the error log during a table import procedure.

When ALTER TABLE ... DISCARD TABLESPACE is run on the destination instance:

• The table is locked in X mode.
• The tablespace is detached from the table.

When FLUSH TABLES ... FOR EXPORT is run on the source instance:

• The table being flushed for export is locked in shared mode.
• The purge coordinator thread is stopped.
• Dirty pages are synchronized to disk.
• Table metadata is written to the binary .cfg file.

Expected error log messages for this operation:

[Note] InnoDB: Sync to disk of '"test"."t1"' started.
[Note] InnoDB: Stopping purge
[Note] InnoDB: Writing table metadata to './test/t1.cfg'
[Note] InnoDB: Table '"test"."t1"' flushed to disk

When UNLOCK TABLES is run on the source instance:

• The binary .cfg file is deleted.
• The shared lock on the table or tables being imported is released and the purge coordinator thread is restarted.

Expected error log messages for this operation:

[Note] InnoDB: Deleting the meta-data file './test/t1.cfg'
[Note] InnoDB: Resuming purge

When ALTER TABLE ... IMPORT TABLESPACE is run on the destination instance, the import algorithm performs the following operations for each tablespace being imported:

• Each tablespace page is checked for corruption.
• The space ID and log sequence numbers (LSNs) on each page are updated.
• Flags are validated and LSN updated for the header page.
• Btree pages are updated.
• The page state is set to dirty so that it is written to disk.

Expected error log messages for this operation:

[Note] InnoDB: Importing tablespace for table 'test/t1' that was exported
from host 'host_name'
[Note] InnoDB: Phase I - Update all pages
[Note] InnoDB: Sync to disk
[Note] InnoDB: Sync to disk - done!
[Note] InnoDB: Phase III - Flush changes to disk
[Note] InnoDB: Phase IV - Flush complete

[lihongjie0209]

冷备份恢复

Copying Data Files (Cold Backup Method)

You can move an InnoDB database simply by copying all the relevant files listed under "Cold Backups" in Section 14.19.1, “InnoDB Backup”.

InnoDB data and log files are binary-compatible on all platforms having the same floating-point number format. If the floating-point formats differ but you have not used FLOAT or DOUBLE data types in your tables, then the procedure is the same: simply copy the relevant files.

When you move or copy file-per-table .ibd files, the database directory name must be the same on the source and destination systems. The table definition stored in the InnoDB shared tablespace includes the database name. The transaction IDs and log sequence numbers stored in the tablespace files also differ between databases.

To move an .ibd file and the associated table from one database to another, use a RENAME TABLE statement:

RENAME TABLE db1.tbl_name TO db2.tbl_name;

If you have a “clean” backup of an .ibd file, you can restore it to the MySQL installation from which it originated as follows:

1. The table must not have been dropped or truncated since you copied the .ibd file, because doing so changes the table ID stored inside the tablespace.

2. Issue this ALTER TABLE statement to delete the current .ibd file:

   ALTER TABLE tbl_name DISCARD TABLESPACE;

3. Copy the backup .ibd file to the proper database directory.

4. Issue this ALTER TABLE statement to tell InnoDB to use the new .ibd file for the table:

   ALTER TABLE tbl_name IMPORT TABLESPACE;

   Note

   The ALTER TABLE ... IMPORT TABLESPACE feature does not enforce foreign key constraints on imported data.

In this context, a “clean” .ibd file backup is one for which the following requirements are satisfied:

• There are no uncommitted modifications by transactions in the .ibd file.
• There are no unmerged insert buffer entries in the .ibd file.
• Purge has removed all delete-marked index records from the .ibd file.
• mysqld has flushed all modified pages of the .ibd file from the buffer pool to the file.

You can make a clean backup .ibd file using the following method:

1. Stop all activity from the mysqld server and commit all transactions.
2. Wait until SHOW ENGINE INNODB STATUS shows that there are no active transactions in the database, and the main thread status of InnoDB is Waiting for server activity. Then you can make a copy of the .ibd file.

Another method for making a clean copy of an .ibd file is to use the MySQL Enterprise Backup product:

1. Use MySQL Enterprise Backup to back up the InnoDB installation.
2. Start a second mysqld server on the backup and let it clean up the .ibd files in the backup.