WL#5522: InnoDB: Transportable Single-Table Tablespaces

Status: Complete 

Bug#11746326 25491: DETACH AND ATTACH INNODB TABLESPACE FILES.

Allow users to 'cold' export a table from a running MySQL server instance to
another running instance. It can also be used to do a full table restore on the
same instance. For this we will need to extend the SQL server syntax and 
introduce:

   FLUSH TABLE[S] t1,...,tn FOR EXPORT;


The above SQL clause will lock table t[1-n] in SHARED mode and block
transactions that attempt to update the table. It will however allow read-only
operations to continue. When control returns from the above statement to the
user, the table's dirty pages should all be synced to disk with any change
buffer entries merged prior to sync. The objective is to make the tablespace
transactionally consistent on disk. The quiesce (export) process will also
create a binary file that contains the meta-data required for importing the 
table. The naming convention of the meta-data file is illustrated by the 
following the use case:

   use test;
   CREATE TABLE t(c1 INT) engin=InnoDB;
   FLUSH TABLE t FOR EXPORT; -- quiesce the table and create the meta data file
   UNLOCK TABLES;

   $innodb_data_home_dir/test/t.cfg

This will be created in the same directory that the target tablespace (.ibd)
resides in. The contents of any previous .cfg file will be overwritten. The
format of this file is described in the HLS.

The user must copy the .ibd files and the .cfg file(s) to the destination
directory before releasing the SHARED locks.

After copying the .ibd file(s) the user must release the SHARED locks on the
table(s) using the following syntax:

  UNLOCK TABLES;

When there are foreign key constraints between tables, users should export all
tables at the same (logical) point of time. Stop updating any of the tables,
commit all transactions related to them, acquire S locks on the tables and
export the tables. In that way, the constraints will hold also in the receiving
InnoDB instance.

To IMPORT the tablespace, the user must create the table on the receiving
instance if it doesn't already exist and it is currently the users
responsibility to ensure that the schema matches that of the importing
tablespace. Before a tablespace can be imported InnoDB has to DISCARD the
tablespace that is attached to the table. The syntax for this operation is:

   ALTER TABLE t DISCARD TABLESPACE;

The user must stop all updates on the tables, prior to the IMPORT. For tables
that are in a foreign key relationship, the updates to all the tables in the
group must be stopped.

After detaching/discarding the tablesapce the user must initate the IMPORT using
the following syntax:

  ALTER TABLE t IMPORT TABLESPACE;

After the above operation completes, the table(s) should be ready for use as 
normal.


Copyright (c) 2000, 2025, Oracle Corporation and/or its affiliates. All rights reserved.