Using Per-Table Tablespaces

• 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.

• One 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.

• To enable multiple tablespaces, add 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.
    • MyISAM divides the table into a data file tbl_name.MYD and the index file tbl_name.MYI.
  • For InnoDB, data and the indexes are stored together in the .ibd file.
    • The tbl_name.frm file is still created as usual.

Can I use raw devices for shared tablespace?

---

---

© 2007 John Michael Pierobon

Notes