(mysql.info) multiple-tablespaces

 
 14.2.3.1 Using Per-Table Tablespaces
 ....................................
 
 You can store each `InnoDB' table and its indexes in its own file. This
 feature is called `multiple tablespaces' because in effect each table
 has its own tablespace.
 
 Using multiple tablespaces can be beneficial to users who want to move
 specific tables to separate physical disks or who wish to restore
 backups of single tables quickly without interrupting the use of the
 remaining `InnoDB' tables.
 
 You can enable multiple tablespaces by adding this line to the
 `[mysqld]' section of `my.cnf':
 
      [mysqld]
      innodb_file_per_table
 
 After restarting the server, `InnoDB' stores each newly created table
 into its own file `TBL_NAME.ibd' in the database directory where the
 table belongs. This is similar to what the `MyISAM' storage engine
 does, but `MyISAM' divides the table into a data file `TBL_NAME.MYD' and
 the index file `TBL_NAME.MYI'.  For `InnoDB', the data and the indexes
 are stored together in the `.ibd' file. The `TBL_NAME.frm' file is
 still created as usual.
 
 If you remove the `innodb_file_per_table' line from `my.cnf' and
 restart the server, `InnoDB' creates tables inside the shared
 tablespace files again.
 
 `innodb_file_per_table' affects only table creation, not access to
 existing tables. If you start the server with this option, new tables
 are created using `.ibd' files, but you can still access tables that
 exist in the shared tablespace. If you remove the option and restart
 the server, new tables are created in the shared tablespace, but you
 can still access any tables that were created using multiple
 tablespaces.
 
 `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.
 
 * You cannot freely move `.ibd' files between database
 directories as you can with `MyISAM' table files. This is because the
 table definition that is stored in the `InnoDB' shared tablespace
 includes the database name, and because `InnoDB' must preserve the
 consistency of transaction IDs and log sequence numbers.
 
 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. Issue this `ALTER TABLE' statement:
 
           ALTER TABLE TBL_NAME DISCARD TABLESPACE;
 
      *Caution*: This statement deletes the current `.ibd' file.
 
   2. Put the backup `.ibd' file back in the proper database directory.
 
   3. Issue this `ALTER TABLE' statement:
 
           ALTER TABLE TBL_NAME IMPORT TABLESPACE;
 
 In this context, a `clean' `.ibd' file backup means:
 
    * 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
 commercial `InnoDB Hot Backup' tool:
 
   1. Use `InnoDB Hot 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.
Info Catalog
(mysql.info) innodb-configuration
(mysql.info) innodb-raw-devices
automatically generated byinfo2html