(mysql.info.gz) Multiple tablespaces

Info Catalog

(mysql.info.gz) InnoDB and MySQL Replication

(mysql.info.gz) Using InnoDB tables

 
 15.7.6 Using Per-Table Tablespaces
 ----------------------------------
 
 * CRITICAL BUG in 4.1 if you specify `innodb_file_per_table' in
 `my.cnf'! If you shut down `mysqld', then records may disappear from
 the secondary indexes of a table. See (Bug #7496) for more information
 and workarounds. This is fixed in 4.1.9, but another bug (Bug #8021)
 bit the Windows version in 4.1.9, and in the Windows version of 4.1.9
 you must put the line `innodb_flush_method=unbuffered' to your `my.cnf'
 or `my.ini' to get `mysqld' to work.
 
 Starting from MySQL 4.1.1, you can store each `InnoDB' table and its
 indexes into 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.
 
 If you need to downgrade to 4.0, you have to take table dumps and
 re-create the whole `InnoDB' tablespace. If you have not created new
 `InnoDB' tables under MySQL 4.1.1 or later, and need to downgrade
 quickly, you can also do a direct downgrade to the MySQL 4.0.18 or
 later in the 4.0 series.  Before doing the direct downgrade to 4.0.x,
 you have to end all client connections to the `mysqld' server that is
 to be downgraded, and let it run the purge and insert buffer merge
 operations to completion, so that `SHOW INNODB STATUS' shows the main
 thread in the state `waiting for server activity'. Then you can shut
 down `mysqld' and start 4.0.18 or later in the 4.0 series.
 
 You can enable multiple tablespaces by adding a line to the `[mysqld]'
 section of `my.cnf':
 
      [mysqld]
      innodb_file_per_table
 
 After restarting the server, `InnoDB' will store 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. 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, 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. The `.ibd' files are not
 sufficient for `InnoDB' to operate. The shared tablespace consists of
 the familiar `ibdata' files where `InnoDB' puts its internal data
 dictionary and undo logs.
 
 *You cannot freely move `.ibd' files around* between database
 directories the way you can with `MyISAM' table files. This is because
 the table definition is stored in the `InnoDB' shared tablespace, and
 also because `InnoDB' must preserve the consistency of transaction IDs
 and log sequence numbers.
 
 Within a given MySQL installation, you can move an `.ibd' file and the
 associated table from one database to another with the familiar `RENAME
 TABLE' statement:
 
      RENAME TABLE OLD_DB_NAME.TBL_NAME TO NEW_DB_NAME.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 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 such a clean backup `.ibd' file with the following method:
 
   1. Stop all activity from the `mysqld' server and commit all
      transactions.
 
   2. Wait until `SHOW 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.
 
 It is in the TODO to also allow moving clean `.ibd' files to another
 MySQL installation. This requires resetting of transaction IDs and log
 sequence numbers in the `.ibd' file.
 

Info Catalog

(mysql.info.gz) InnoDB and MySQL Replication

(mysql.info.gz) Using InnoDB tables