Chapter 1. Introduction to the InnoDB Plugin

The unique architecture of MySQL permits multiple storage engines with different capabilities to be accessed through the same SQL language and APIs. Starting with version 5.1, MySQL AB has promoted the idea of a “pluggable” storage engine architecture, which permits multiple storage engines to be added to MySQL. Currently, however, most users have accessed only those storage engines that are distributed by MySQL AB, and are linked into the binary (executable) releases.

Since 2001, MySQL AB has distributed the InnoDB transactional storage engine with its releases (both source and binary). Beginning with MySQL version 5.1, it is possible for users to swap out one version of InnoDB and use another. The pluggable storage engine architecture also permits Innobase Oy to release new versions of InnoDB containing bug fixes and new features independently of the release cycle for MySQL. Users can thus take advantage of these new versions of InnoDB in the context of their deployed MySQL installations.

The InnoDB Plugin for MySQL contains several important new features:

Note that the ability to use data compression and the new row format require the use of a new InnoDB file format called “Barracuda”. The previous file format, used by the built-in InnoDB in MySQL versions 5.0 and 5.1 is now called “Antelope” and does not support these features, but does support the other features introduced with the InnoDB Plugin.

The InnoDB Plugin is upward compatible from standard InnoDB as built in to, and distributed with, MySQL. Existing databases can be used with the InnoDB Plugin for MySQL. As described in Section 9.5, “Configuring the InnoDB Plugin”, the new parameter innodb_file_format can help protect upward and downward compatibility between InnoDB versions and database files, allowing users to enable or disable use of new features that can only be used with certain versions of InnoDB.

The built-in InnoDB in MySQL since version 5.0.21 has a safety feature that prevents it from opening tables that are in an unknown format. However, as noted in Section 11.2, “The Built-in InnoDB, the Plugin and File Formats”, the system tablespace may contain references to new-format tables that will confuse the built-in InnoDB in MySQL. These references will be cleared in a “slow” shutdown of the InnoDB Plugin.

With previous versions of InnoDB, no error would be returned until you try to access a table that is in a format “too new” for the software. Beginning with version 1.0.1 of the InnoDB Plugin, however, to provide early feedback, InnoDB will check the system tablespace to ensure that the file format used in the database is enabled for use before it will start. See Section 4.4.1, “Startup File Format Compatibility Checking” for the details.

From the MySQL download site, you can download the MySQL Server 5.1.38 and up, containing an additional, upgraded version of InnoDB with additional features beyond the built-in InnoDB provided by MySQL. You can configure your server to use the InnnoDB Plugin rather than the built-in InnoDB, to take advantage of the extra features and performance enhancements in the Plugin. The InnoDB Plugin for MySQL is licensed under the same license that MySQL uses (GPLv2). It is available at no charge for use and can be freely redistributed, subject to the same requirements of the GPL as is MySQL itself (see the GNU General Public License, version 2). Limited support is available for the InnoDB Plugin for MySQL at http://forums.innodb.com. You may also report bugs in the InnoDB Plugin using the MySQL bug database.

In many environments, the InnoDB plugin can dynamically be added to a MySQL instance without relinking the MySQL server. The plugin version of InnoDB then “takes over” from the statically linked InnoDB that is part of the mysqld binary. In other environments, it is currently necessary to build the entire MySQL server, including the InnoDB plugin, from source code.

On Linux, Unix and Windows, it is a simple matter of installing the InnoDB Plugin for MySQL using the MySQL command INSTALL PLUGIN. When the InnoDB Plugin for MySQL is installed, it replaces the statically-linked version of InnoDB that is incorporated in the MySQL binary as distributed by MySQL AB.

On platforms where the dynamic plugin is not available, users must download the source code for the InnoDB Plugin for MySQL and build MySQL from source. Building from source also facilitates the distribution of a MySQL server where the InnoDB Plugin is already “installed”, so all users of an organization can use the new capabilities without the INSTALL step. The procedure for building from source code is documented in Section 9.4, “Building the InnoDB Plugin from Source Code”.

Full instructions are provided in Chapter 9, Installing the InnoDB Plugin.

InnoDB Plugin releases are numbered with version numbers independent of MySQL release numbers. The initial release of the InnoDB Plugin is version 1.0, and it is designed to work with MySQL 5.1.

Once you have installed the InnoDB Plugin, you can check its version number in three ways:

The InnoDB Plugin writes its version number to the error log, which can be helpful in diagnosis of errors:

091105 12:28:06 InnoDB Plugin 1.0.5 started; log sequence number 46509

Note that the PLUGIN_VERSION column in the table INFORMATION_SCHEMA.PLUGINS does not display the third component of the version number, only the first and second components, as in 1.0.

Because the InnoDB Plugin introduces a new file format, with new on-disk data structures within both the database and log files, there are important restrictions on the use of the plugin in typical user environments. Specifically, you should pay special attention to the information presented here about file format compatibility with respect to the following scenarios:

WARNING: Once you use the InnoDB Plugin on a set of database files, take care to avoid crashes and corruptions when using those files with an earlier version of InnoDB, as might happen by opening the database with MySQL when the plugin is not installed. It is strongly recommended that you use a “slow shutdown” (SET GLOBAL innodb_fast_shutdown=0) when stopping the MySQL server when the InnoDB Plugin is enabled. This will ensure log files and other system information written by the plugin will not cause problems when using a prior version of InnoDB. See Section 11.3, “How to Downgrade”.

Because of these considerations, and although it may be useful in certain circumstances to use the plugin in a temporary way as just described, many users will find it preferable to test their application with the plugin and use it on an on-going basis, without reverting back to the standard, built-in InnoDB.

WARNING: If you dump a database containing compressed tables with mysqldump, the dump file may contain CREATE TABLE commands that attempt to create compressed tables, or those using ROW_FORMAT=DYNAMIC in the new database. Therefore, you should be sure the new database is running the InnoDB Plugin, with the proper settings for innodb_file_format and innodb_file_per_table, if you want to have the tables re-created as they exist in the original database. Typically, however, when the mysqldump file is loaded, MySQL and InnoDB will ignore CREATE TABLE options they do not recognize, and the table(s) will be created in a format used by the running server.

WARNING: If you use MySQL replication, you should be careful to ensure all slaves are configured with the InnoDB Plugin, with the same settings for innodb_file_format and innodb_file_per_table. If you do not do so, and you create tables that require the new “Barracuda” file format, replication errors may occur. If a slave MySQL server is running the built-in InnoDB, it will ignore the CREATE TABLE options to create a compressed table or one with ROW_FORMAT=DYNAMIC, and create the table uncompressed, with ROW_FORMAT=COMPACT.

WARNING: The current version of InnoDB Hot Backup does not support the new “Barracuda” file format. Using InnoDB Hot Backup Version 3 to backup databases in this format will cause unpredictable behavior. A future version of InnoDB Hot Backup will support databases used with the InnoDB Plugin. As an alternative, you may back up such databases with mysqldump.