15.1 The InnoDB Tablespace and Logs

InnoDB operates using two primary disk-based resources: A tablespace for storing table contents, and a set of log files for recording transaction activity.

Each InnoDB table has a format (.frm) file in the database directory of the database to which the table belongs. This is the same as tables managed by any other MySQL storage engine, such as MyISAM. However, InnoDB manages table contents (data rows and indexes) on disk differently than does the MyISAM engine. InnoDB uses a tablespace—one or more files that form a single logical storage area. All InnoDB tables are stored together within the tablespace. There are no table-specific datafiles or index files for InnoDB the way there are for MyISAM tables. The tablespace also contains a rollback segment. As transactions modify rows, undo log information is stored in the rollback segment. This information is used to roll back failed transactions.

Although logically InnoDB treats the tablespace as a single storage area, it can consist of one file or multiple files. Each file can be a regular file or a raw partition. The final file in the tablespace can be configured to be auto-extending, in which case InnoDB expands it automatically if the tablespace fills up. Because the tablespace is shared among all databases (and thus is not database-specific), tablespace files are stored by default in the server's data directory, not within a database directory.

In MySQL 4.1, multiple tablespaces can be configured. That capability is not covered here or on the certification exams at this time. A single tablespace is assumed throughout.

In addition to the tablespace, the InnoDB storage engine manages a set of InnoDB-specific log files that contain information about ongoing transactions. As a client performs a transaction, the changes that it makes are held in the InnoDB log. The more recent log contents are cached in memory. Normally, the cached log information is written and flushed to log files on disk at transaction commit time, though that may also occur earlier.

If a crash occurs while the tables are being modified, the log files are used for auto-recovery: When the MySQL server restarts, it reapplies the changes recorded in the logs, to ensure that the tables reflect all committed transactions.

For information about configuring the tablespace and log files, see section 15.7, "InnoDB Maintenance."

15.1.1 InnoDB Tablespace Portability

Binary portability for InnoDB means that you can directly copy the tablespace files from a MySQL server on one machine to another server on a different machine and the second server will be able to access the tablespace with no problems. Because all InnoDB tables managed by a server are stored together in the tablespace, portability of the tablespace is a function of whether all individual InnoDB tables are portable. If even one table is not, neither is the tablespace.

Two conditions that determine whether the InnoDB tablespace is binary portable from one host to another are similar to the conditions for MyISAM portability:

• Both machines must use two's-complement integer arithmetic.

• Both machines must use IEEE floating-point format, or else none of the InnoDB tables in the tablespace must contain any floating-point columns (FLOAT or DOUBLE).

In practice, these conditions pose little restriction. Two's-complement integer arithmetic and IEEE floating-point format are the norm on modern hardware.

A third condition is that you should use lowercase names for databases and tables. This is because InnoDB stores these names internally in lowercase on Windows. Using lowercase names allows binary portability between Windows and Unix.

The procedure for making a binary tablespace backup can be found in section 15.5, "InnoDB Backup and Recovery."