Why does InnoDB store all databases in one file?

The architecture of InnoDB demands the use of four basic types of info pages

• Table Data Pages
• Table Index Pages
• Table MetaData
• MVCC Data (to support Transaction Isolation and ACID Compliance)
  • Rollback Segments
  • Undo Space
  • Double Write Buffer (background writing to prevent reliance on OS caching)
  • Insert Buffer (managing changes to non-unique secondary indexes)

See the Pictorial Representation of ibdata1

By default, innodb_file_per_table is disabled. This causes all four info page types to land a single file called ibdata1. Many people try to spread out the data by making multiple ibdata files. This could lead to fragmentation of data and index pages.

This is why I often recommend cleaning up the InnoDB infrastructure, using the default ibdata1 file and nothing more.

Copying is very dangerous because of the infrastructure under which InnoDB works. There are two basic infrastructures

• innodb_file_per_table disabled
• innodb_file_per_table enabled

InnoDB (innodb_file_per_table disabled)

With innodb_file_per_table disabled, all these types of InnoDB info live within ibdata1. The only manifestation of any InnoDB table outside of ibdata1 is the .frm file of the InnoDB table. Copying all InnoDB data at once requires copying all of /var/lib/mysql.

Copying an individual InnoDB table is totally impossible. You must MySQL dump to extract a dump of the table as a logical representation of the data and its corresponding index definitions. You would then load that dump to another database on the same server or another server.

InnoDB (innodb_file_per_table enabled)

With innodb_file_per_table enabled, table data and its indexes live in the database folder next to the .frm file. For example, for the table db1.mytable, the manifestation of that InnoDB table outside of ibdata1 would be:

• /var/lib/mysql/db1/mytable.frm
• /var/lib/mysql/db1/mytable.ibd

System Tablespace ibdata1

All the metadata for db1.mytable still resides in ibdata1 and there is absolutely no way around that. Redo logs and MVCC data also still live with ibdata1.

When it comes to table fragmentation, here is what happens to ibdata1:

• innodb_file_per_table enabled: you can shrink db1.mytables with ALTER TABLE db1.mytable ENGINE=InnoDB; or OPTIMIZE TABLE db1.mytable;. This results in /var/lib/mysql/db1/mytable.ibd being physically smaller with no fragmentation.
• innodb_file_per_table disabled: you cannot shrink db1.mytables with ALTER TABLE db1.mytable ENGINE=InnoDB; or OPTIMIZE TABLE db1.mytable; because it resides with ibdata1. Running either command actually, make the table contiguous and faster to read and write to. Unfortunately, that occurs at the end of ibdata1. This makes ibdata1 grow rapidly. This is fully addressed in my InnoDB Cleanup Post.

WARNING (or DANGER as the Robot would say in Lost in Space)

If you are thinking of just copying the .frm and .ibd file, you are in line for the world of hurting. Copying the .frm and .ibd file of an InnoDB table is only good if and only if you can guarantee that the tablespace id of the .ibd file matches exactly with the tablespace id entry in the metadata of the ibdata1 file.

I wrote two posts in DBA StackExchange about this tablespace id concept

• Table compression in InnoDB? (under the heading 'Restoring Databases')
• How to Recover an InnoDB table whose files were moved around

Here is an excellent link on how to reattach any .ibd file to ibdata1 in the event of mismatched tablespace ids : http://www.chriscalender.com/?tag=innodb-error-tablespace-id-in-file. After reading this, you should come to the immediate realization that copying .ibd files is just plain crazy.

For InnoDB, you only need to something this to move

CREATE TABLE db2.mytable LIKE db1.mytable;
INSERT INTO db2.mytable SELECT * FROM db1.mytable;

to make a copy of an InnoDB table.

If you are migrating it to another DB server, use mysqldump.

With regard to mixing all InnoDB tables from all databases, I can actually see the wisdom in doing so. At my employer's DB/Web hosting company, I have one MySQL Client that has a table in one database whose constraints are mapped to another table in another database within the same MySQL instance. With one common metadata repository, it makes transactional support and MVCC operability possible across multiple databases.

You can toggle InnoDB to store tables per file by adding innodb-file-per-table to your cnf.

Innodb really just cares about pages of data at a basic level. In fact, you can set up InnoDB to use just a raw block device with no filesystem what so ever! http://dev.mysql.com/doc/refman/5.5/en/innodb-raw-devices.html

There are conveniences to storing tables for file such as being able to more easily regain used space via optimize.

Even with files per table, you can't just copy the the ibd files so easily since InnoDB is transactional and stores information about it's state in the globally shared ibdata/log files.

That is not to say it cannot be done. If the table is offline you can discard/import the tablespaces and copy the .idbs around http://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-tablespaces.html

This is the default behaviour but not mandatory. From MySQL docs, Using Per-Table Tablespaces:

By default, all InnoDB tables and indexes are stored in the system tablespace. As an alternative, you can store each InnoDB table and its indexes in its own file. This feature is called “multiple tablespaces” because each table that is created when this setting is in effect has its own tablespace.

As to why, the reason is probably the different architectures of the two engines (MyISAM and InnoDB). For example, in InnoDB, you can't just copy the .ibd file to another database or installation. Explanation (from the same page):

Portability Considerations for .ibd Files

You cannot freely move .ibd files between database directories as you can with MyISAM table files. 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.